Adaptive Index Defrag

Latest update: 1/20/2017 (Check change log)

Download on GitHub

NOTE: if using SQL Server 2012 or SQL Server 2014, mind there is a bug where the online index rebuild can cause index corruption or data loss when it is used together with concurrent queries that modify many rows. If using @onlineRebuild option, to work around this, use one of the following:

• Apply a CU that includes hotfix https://support.microsoft.com/kb/2969896. For SQL Server 2012 SP1, apply at least CU11. For SQL Server 2012 SP2, apply at least CU1. For SQL Server 2014, apply at least CU2.
• Another alternative, but one that impacts performance would be to set the @maxdopRestriction setting to 1.

What’s the purpose of AdaptiveIndexDefrag?

The purpose for this procedure to to perform an intelligent defrag on one or more indexes for one or more databases. The 1st release (back in March 2010) was inspired by an earlier release of Michelle Ufford’s code in SQLFOOL.com site, and has since evolved to suit different and added needs, as you can verify below in the change log.
In a nutshell, this procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, amongst other parameters, like if page locks are allowed or the existence of LOBs.
All within a specified time frame you choose, defaulting to 8 hours. The defrag priority can also be set, either on size, fragmentation level or index usage (based on range scan count), which is the default.
It also handles partitioned indexes, optional statistics update (table-wide or only those related to indexes), rebuilding with the original fill factor or index padding and online operations, to name a few options.

On what version of SQL can I use it?

This procedure can be used from SQL Server 2005 SP2 onwards, because of the DMVs and DMFs involved.

How to deploy it?

Starting with v1.3.7, on any database context you choose to create the usp_AdaptiveIndexDefrag and its supporting objects, open the attached script, and either keep the @deploymode variable at the top to upgrade mode (preserving all historic data), or change for new deployments or overwrite old versions and objects (disregarding historic data).

How to use it?

After executing the attached script in a user database of your choice, either run the procedure usp_AdaptiveIndexDefrag with no parameters, since all are optional (If not specified, the defaults for each parameter are used), or customize its use with the following parameters, grouped by type:

@Exec_Print defaults to 1 (that is to execute the SQL code generated by this SP) or optionally 0 (just print the commands).
@printCmds defaults to 0 (do not print all commands to screen) or optionally 0 (print all commands to screen). Useful if you just want to see what commands would be executed.
@outputResults defaults to 0 (does not output fragmentation information) or optionally 0 (output fragmentation information after run completes).
@debugMode defaults to 0 (do not display debug comments) or optionally 0 (display debug comments).
@timeLimit limits how much time can be spent performing index defrags and is expressed in minutes. Note that the time limit is checked BEFORE an index defrag begins, thus a long index defrag can exceed the time limit. Defaults to 480m (8h).
@dbScope specifies a database name to defrag. If not specified, all non-system databases plus msdb and model will be defragmented.
@tblName specifies if you only want to defrag indexes for a specific table. The input format is schema.table_name. If not specified, all tables will be defragmented.
@defragOrderColumn defines how to prioritize the order of defrags and is used only if @Exec_Print is set to 1. The default is to order by range_scan_count (count of range and table scans on the index because these can benefit the most from defragmentation), other option are fragmentation (amount of fragmentation in the index) or page_count (number of pages in the index).
@defragSortOrder defines the sort order of the ORDER BY clause on the above query on how to prioritize the order of defrags. Options are ASC (ascending) or DESC (descending), which is the default.
@forceRescan defaults to 0, where a rescan will not occur until all indexes have been defragmented (this allows for a defrag run to span multiple executions over several periods of time). Other option is 1 to force a rescan.
@defragDelay specifies the time to wait between defrag commands and defaults to 5s. Refer to Reorganizing and Rebuilding Indexes for documentation on the following parameters. @ixtypeOption defaults to NULL (all indexes will be defragmented). Other options are 1 (only Clustered indexes) or 0 (only Non-Clustered indexes, including XML and Spatial Indexes).
@minFragmentation defaults to 5%, will not defrag if fragmentation is less.
@rebuildThreshold defaults to 30%. Higher than 30% will result in a rebuild operations instead of reorganize.
@rebuildThreshold_cs defaults to 10%. Greater than 10% will result in columnstore rebuild.
@minPageCount specifies how many pages must exist in an index in order to be considered for a defrag. Defaults to one extent (8 pages).
@maxPageCount specifies the maximum number of pages that can exist in an index and still be considered for a defrag run. Useful for scheduling small indexes during business hours and large indexes for non-business hours.
@fillfactor defaults to 1 (the original FF from when the index was created or last defragmented) or optional 0 (uses the default FF of 0).
@scanMode specifies which scan mode to use to determine fragmentation levels. LIMITED mode is the default. Scans the smallest number of pages. For an index, only the parent-level pages of the B-tree (that is, the pages above the leaf level) are scanned. For a heap, only the associated PFS and IAM pages are examined. The data pages of the heap are not scanned. Other options include SAMPLED (returns statistics based on a 1 percent sample of all the pages in the index or heap) or DETAILED (scans all pages and returns all statistics. Can cause performance issues). If the index or heap has fewer than 10,000 pages, DETAILED mode is automatically used instead of SAMPLED.
@onlineRebuild defaults to 0 (offline rebuild) or optionally 1 (online rebuild if possible)
@sortInTempDB defaults to 0 (perform sort operation in the index's database) or optionally 1 (perform sort operation in TempDB). If a sort operation is not required, or if the sort can be performed in memory, SORT_IN_TEMPDB is ignored. Setting this option to 1 can result in faster defrags and prevent database file size inflation. The caveat is you have to monitor TempDB closely.
@maxDopRestriction specifies a processor limit for index rebuilds. If not specified, defrag operations will use the system MaxDOP setting, up to a limit of 8.
@updateStats defaults to 1 (updates stats when reorganizing) or optionally 0 (does not update stats when reorganizing)
@updateStatsWhere defaults to 0 (updates all stats in entire table) or optionally 1 (updates only index related stats). Even if you choose to update stats, and if the @scanMode option was NOT set in LIMITED mode, only those within certain thresholds will be updated. Refer to Statistical maintenance functionality (autostats) in SQL Server to check the defaults for auto-update statistics.
@statsSample defaults to NULL (performs a sample scan on the target table or indexed view where the database engine automatically computes the required sample size), or optionally FULLSCAN (all rows in table or view should be read to gather the statistics) or RESAMPLE (statistics will be gathered using an inherited sampling ratio for all existing statistics including indexes).
@ix_statsnorecomp defaults to 0 (run with STATISTICS_NORECOMPUTE OFF). Refer to ALTER INDEX (Transact-SQL) for information on the option STATISTICS_NORECOMPUTE. Optionally use 1 (run with STATISTICS_NORECOMPUTE ON will disable the auto update statistics on index related statistics). If you are dealing with stats update with a custom job (or even with this code by updating statistics), you may use this option.
@statsIncremental defaults to NULL. When Incremental is ON, the statistics created are per partition statistics. When OFF, the statistics tree is dropped and SQL Server re-computes the statistics. This setting overrides the database level INCREMENTAL property.
@dealMaxPartition specifies whether to exclude the right-most populated partition (if an index is partitioned), or act only on that same partition, excluding all others. Typically, this is the partition that is currently being written to in a sliding-window scenario. Enabling this feature may reduce contention. This may not be applicable in other types of partitioning scenarios. Non-partitioned indexes are unaffected by this option. This parameter defaults to 0 (only right-most populated partition is defragmented). If the partition is smaller than @minPageCount, it won't be considered. Other options are 1 (to exclude the right-most populated partition) or NULL (all partitions are defragmented).
@dealLOB specifies if all pages that contain large object (LOB) data are compacted or not. The LOB data types are image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml. Compacting this data can improve disk space use. Reorganizing a specified clustered index compacts all LOB columns that are contained in the clustered index. Reorganizing a non-clustered index compacts all LOB columns that are non-key (included) columns in the index. Default is 0 (compact LOBs when reorganizing) and optional 1 (does not compact LOBs when reorganizing).
@ignoreDropObj specifies if a table or index is dropped after the defrag cycle has begun, you can choose to ignore those errors in the overall outcome, thus not showing a job as failed if the only errors present refer to dropped database objects. Default is 0 (includes errors about objects that have been dropped since the defrag cycle began) and optional 1 (for error reporting purposes, ignores the fact that objects have been dropped since the defrag cycle began).
@disableNCIX specifies if non-clustered indexes are to be disabled before a rebuild. If disk space is limited, it may be helpful to disable the non-clustered index before rebuilding it. When a non-clustered index is not disabled, the rebuild operation requires enough temporary disk space to store both the old and new index. However, by disabling and rebuilding a non-clustered index in separate transactions, the disk space made available by disabling the index can be reused by the subsequent rebuild or any other operation. Hence, no additional space is required except for temporary disk space for sorting (this is typically 20 percent of the index size according to BOL). Note that it does not disable indexes on partitioned tables when defragging a subset of existing partitions. Also, the procedure keeps track of whatever indexes were disabled by the defrag cycle. In case the defrag is canceled, it will account for these disabled indexes in the next run. Default is 0 (does NOT disable non-clustered indexes prior to a rebuild)  and optional 1 (disables non-clustered indexes prior to a rebuild).
@offlinelocktimeout specifies a lock timeout period in seconds when doing offline index rebuilds.
@onlinelocktimeout specifies a a time-out period for locks to wait at low priority, expressed in minutes; this is valid from SQL Server 2014 onwards.
@abortAfterwait sets the action of @onlinelocktimeout. This parameter defaults to NULL (After lock timeout occurs, continue waiting for the lock with normal (regular) priority) and optionals are 0 (Kill all user transactions that block the online index rebuild DDL operation so that the operation can continue) and 1 (Exit the online index rebuild DDL operation currently being executed without taking any action).
@dealROWG sets the Columnstore reorg option to compress all rowgroups, and not just closed ones.

What objects are created when running the attached script?

1. tbl_AdaptiveIndexDefrag_Working, used to keep track of which objects to act on, and crucial information that influence how those objects are handled. It also keeps track of which indexes were already defragged in a previous run, if your defrag cycle must span several days due to time constraints.
2. tbl_AdaptiveIndexDefrag_Stats_Working, the statistics counterpart of the above table.
3. tbl_AdaptiveIndexDefrag_log, an index operations logging table, where all the index operations are logged.
4. tbl_AdaptiveIndexDefrag_Stats_log, a statistics operations logging table, where all the statistics operations are logged.You might want to cleanup this and the above table after awhile using the procedure usp_AdaptiveIndexDefrag_PurgeLogs.
5. tbl_AdaptiveIndexDefrag_Exceptions, an exceptions table where you can set the restrictions on which days certain objects are handled (mask just like in sysschedules system table). You can also set exceptions for specific indexes, tables or entire databases.
   Say you have a specific table that you only want to defrag on weekends, you can set it in the exceptions table so that all indexes on that table will only be defragged on Saturdays and Sundays. Or you want to exclude one database or table from ever being defragged. These are just examples of how to manage specific needs.
6. tbl_AdaptiveIndexDefrag_IxDisableStatus, where indexes that were disabled are logged, so that an interruption in the defrag cycle can account for these indexes has being disabled by the defrag cycle itself and not the user.
7. usp_AdaptiveIndexDefrag_PurgeLogs, which will purge the log tables of data older than 90 days, to avoid indefinite growth.
   The 90 days is just the default, change @daystokeep input parameter to a value you deem fit. I recommend executing this in a job.
8. usp_AdaptiveIndexDefrag_Exclusions, which is will help in setting on which days (if any) you allow for a specific index, or even all indexes on a given table, to be defragmented. In the previous post here there was an example query of how you could set the exclusions embedded in the script, but due to some feedback, I’ve turned it into an SP.
   This sproc takes 4 input parameters:
   • @exclusionMask_DB, enter only one database name at a time.
   • @exclusionMask_days, enter weekdays in short form, between commas. Keep only relevant weekdays on which you DO NOT want to allow defragmentation to occur. Order is not mandatory, but weekday short names are important AS IS ('Sun,Mon,Tue,Wed,Thu,Fri,Sat').
     Conversely, leave the default value of NULL to exclude the object(s) from ever being defragged, equivalent to choosing every day of the week.
   • @exclusionMask_tables (optional) enter table names separated by commas ('table_name_1, table_name_2, table_name_3').
   • @exclusionMask_indexes (optional) enter index names separated by commas ('index_name_1, index_name_2, index_name_3'). If you want to exclude all indexes in a given table, enter its name but don't add index names.
9. usp_AdaptiveIndexDefrag_CurrentExecStats, which can be used to keep track of which indexes were already defragged thus far in the current execution.
10. usp_AdaptiveIndexDefrag, the main procedure that handles index defragmentation and statistics updates. Takes the input parameters shown before.
11. And several views for miscellaneous purposes:
    vw_ErrLst30Days, to check all known execution errors in the last 30 days.
    vw_ErrLst24Hrs, to check all known execution errors in the last 24 hours.
    vw_AvgTimeLst30Days, to check the average execution time for each index in the last 30 days.
    vw_AvgFragLst30Days, to check the average fragmentation found for each index in the last 30 days.
    vw_AvgLargestLst30Days, to check the average size for each index in the last 30 days.
    vw_AvgMostUsedLst30Days, to check the average usage of each index in the last 30 days.
    vw_LastRun_Log, to check in the log tables how the last execution did.

A few common usage scenarios for this script:

1. EXEC dbo.usp_AdaptiveIndexDefrag The defaults are to defragment indexes with fragmentation greater than 5%; rebuild indexes with fragmentation greater than 30%; defragment ALL indexes; commands WILL be executed automatically; defragment indexes in DESC order of the RANGE_SCAN_COUNT value; time limit was specified and is 480 minutes (8 hours); ALL databases will be defragmented; ALL tables will be defragmented; WILL be rescanning indexes; the scan will be performed in LIMITED mode; LOBs will be compacted; limit defrags to indexes with more than 8 pages; indexes will be defragmented OFFLINE; indexes will be sorted in the DATABASE; indexes will have its ORIGINAL Fill Factor; only the right-most populated partitions will be considered if greater than 8 page(s); statistics WILL be updated on reorganized indexes; defragmentation will use system defaults for processors; does NOT print the t-sql commands; does NOT output fragmentation levels; waits 5s between index operations;
2. EXEC dbo.usp_AdaptiveIndexDefrag @dbScope = 'AdventureWorks2014' Same as above, except its scope is only the 'AdventureWorks2008R2' database.
3. EXEC dbo.usp_AdaptiveIndexDefrag @dbScope = 'AdventureWorks2014', @tblName = 'Production.BillOfMaterials' Same as above but only acting on the BillOfMaterials table.
4. EXEC dbo.usp_AdaptiveIndexDefrag @Exec_Print = 0, @printCmds = 1
   Using the operating defaults in 1, this will not execute any commands. Instead, just prints them to the screen. Useful if you want to check what it will be doing behind the scenes.
5. EXEC dbo.usp_AdaptiveIndexDefrag @Exec_Print = 0, @printCmds = 1, @scanMode = 'DETAILED', @updateStatsWhere = 1 Same as above, but adding the DETAILED scanMode to allow for finer thresholds in stats update, and forcing the update statistics to run on index related stats, instead of all the table statistics.
6. EXEC dbo.usp_AdaptiveIndexDefrag @scanMode = 'DETAILED', @updateStatsWhere = 1 , @disableNCIX = 1 Differs from the above just because it will execute the comands instead of printing them and will disable non-clustered indexes prior to a rebuild operation.
7. EXEC dbo.usp_AdaptiveIndexDefrag @minFragmentation = 3, @rebuildThreshold = 20 Using the operating defaults in 1, this will lower the minimum fragmentation that allows the defrag to include a given index to 3%, and the rebuild vs. reorganize threshold to just 20%.
8. EXEC dbo.usp_AdaptiveIndexDefrag @onlineRebuild = 1 Using the operating defaults in 1, this will try to do online rebuild operations whenever possible.
9. EXEC dbo.usp_AdaptiveIndexDefrag @onlineRebuild = 1, @updateStatsWhere = 0, @statsSample = 'FULLSCAN'
   Similar to the above, this will also force update statistics to run on all stats found on table with FULLSCAN.
10. EXEC dbo.usp_AdaptiveIndexDefrag @onlineRebuild = 1, @updateStatsWhere = 0, @dbScope = 'AdventureWorks2014', @defragOrderColumn = 'fragmentation', @timeLimit = 240, @scanMode = 'DETAILED' Similar to the above, this will also restrict all defrag operations to the 'AdventureWorks2008R2' database, giving priority to the most fragmented indexes (instead of the most used, which is the default), limiting the time window for defrag operations to just 4 hours, and using the DETAILED scanMode to allow for finer thresholds in stats update.
11. EXEC dbo.usp_AdaptiveIndexDefrag @timeLimit = 360
    Using the operating defaults in 1, will set the running window to just 6 hours.
12. EXEC dbo.usp_AdaptiveIndexDefrag @offlinelocktimeout = 300, @onlinelocktimeout = 5
    Using the operating defaults in 1, will set the lock timeout value for 300s when doing offline rebuilds, and 5 minutes when doing online rebuilds (valid from SQL Server 2014 onward).
13. EXEC dbo.usp_AdaptiveIndexDefrag @rebuildThreshold = 99, @dealMaxPartition = 1, @onlineRebuild = 1 Using the operating defaults in 1, this will try to do online rebuild operations whenever possible and exclude from the defrag run the right-most partition will while setting a rebuild threshold of 99%, essentially forcing a reorganize instead of a rebuild.
    Useful if you consider the scenario where you had all your indexes with a low fill factor for some purpose in a partitioned table, but then had to rebuild them using a higher fill factor and reclaim the space using DBCC SHRINKFILE (yes, not advisable but can happen on occasion). Forcing a reorganize on all but the right-most partition (active) is the most efficient way of defragmenting your indexes again with minimum impact on the server availability.

Change log:

*  As you may know, the default thresholds for auto update statistics differ. If the cardinality for a table is greater than 6, but less than or equal to 500, update statistics every 500 modifications. If the cardinality for a table is greater than 500, update statistics when (500 + 20 percent of the table) changes have occurred. On the other hand, the sp_updatestats method is only sensitive to the row modification counter calculated in the sys.sysindexes catalog view, which counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table. This can be can be over simplistic in itself.
So, when executing this script (thru a daily job, for example), and you choose a scan mode option other than ‘Limited’ (pertaining to sys.dm_db_index_physical_stats), the default for this script (refer to the parameters section in the script for other options), some finer thresholds somewhere in between both methods can be forced when dealing with statistics update.
One other enhancement is to issue an UPDATE STATISTICS command on the index just before rebuilding it. This can yield better performance in the rebuild phase itself. Does not apply when an index is reorganized instead of rebuilt.
You can look in the changelog section of the usp_AdaptiveIndexDefrag for detailed information on the changes done in every version so far.

Until next time!

Disclaimer: I hope that the information on these pages is valuable to you. This code is not supported under any Microsoft standard support program or service. This code and information are provided "AS IS" without warranty of any kind, either expressed or implied. The entire risk arising out of the use or performance of the script and documentation remains with you. Furthermore, Microsoft, the author or "Blog do Ezequiel" team shall not be liable for any damages you may sustain by using this information, whether direct,
indirect, special, incidental or consequential, including, without limitation, damages for loss of business profits, business interruption, loss of business information
or other pecuniary losseven if it has been advised of the possibility of such damages. Read all the implementation and usage notes thoroughly.611