We will set the DEFAULT to "REBUILD" = "N" and REORGANISE = "Y". See explanation below. Users can still choose to REBUILD by changing the settings.
When Upgrading, we will set REBUILD = "N" if Reorganise = "Y" *** Literature and Explanation ***
The difference in results is likely due to the distinct ways in which the REBUILD and REORGANIZE commands work for SQL Server indexes.
REBUILD:
This command drops and recreates the index from scratch. This can lead to better performance improvements because it fully reorganizes the index structure. However, it can be resource-intensive and cause significant blocking while the operation is performed, especially if ONLINE = OFF is used. The rebuild operation updates index statistics and removes fragmentation but can be more disruptive to the system.
REORGANIZE:
This command reorganizes the leaf level of the index by physically reordering the pages to match the logical order of the index, and also performs page compaction. It is a less resource-intensive operation and does not cause the same level of blocking as REBUILD. REORGANIZE gradually reduces fragmentation without dropping and recreating the index, making it less disruptive. In your scenario, using Rebuild = 1 and Reorganise = 1 can sometimes be redundant because the REBUILD command already performs a full reorganization and compaction of the index. Thus, REORGANIZE afterward might not provide any additional benefits and can lead to inefficient use of resources. However, choosing Rebuild = 0 and Reorganise = 1 might result in better performance because REORGANIZE is a more lightweight operation that doesn't cause significant blocking or resource consumption, making it more suitable for scenarios where minimal disruption is desired. Ultimately, the choice between REBUILD and REORGANIZE depends on the specific requirements of your environment, such as the level of fragmentation, system load, and acceptable downtime. |