Adaptive Index Defrag

Article
07/03/2011

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?

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.
tbl_AdaptiveIndexDefrag_Stats_Working, the statistics counterpart of the above table.
tbl_AdaptiveIndexDefrag_log, an index operations logging table, where all the index operations are logged.
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.
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.
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.
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.
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.
usp_AdaptiveIndexDefrag_CurrentExecStats, which can be used to keep track of which indexes were already defragged thus far in the current execution.
usp_AdaptiveIndexDefrag, the main procedure that handles index defragmentation and statistics updates. Takes the input parameters shown before.
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:

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;
EXEC dbo.usp_AdaptiveIndexDefrag @dbScope = 'AdventureWorks2014' Same as above, except its scope is only the 'AdventureWorks2008R2' database.
EXEC dbo.usp_AdaptiveIndexDefrag @dbScope = 'AdventureWorks2014', @tblName = 'Production.BillOfMaterials' Same as above but only acting on the BillOfMaterials table.
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.
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.
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.
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%.
EXEC dbo.usp_AdaptiveIndexDefrag @onlineRebuild = 1 Using the operating defaults in 1, this will try to do online rebuild operations whenever possible.
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.
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.
EXEC dbo.usp_AdaptiveIndexDefrag @timeLimit = 360
Using the operating defaults in 1, will set the running window to just 6 hours.
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).
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:

Version	Date	Comments
1	08-02-2011	Initial release;
1.1	15-02-2011	Added support for maintaining current index padding options;Added logic for exclusion of hypothetical objects;Deal with LOB compaction when reorganizing;Corrected bug with update stats kicking in when not supposed to;Corrected options not compatible with partitioned indexes;
1.2	10-03-2011	Increased control over new or changed database handling;
1.2.1	22-03-2011	Corrected method of finding available processors;
1.3	21-06-2011	Added more options to act upon statistics (IX related or Table-wide);Added finer thresholds for updates on table-wide statistics when reorganizing (when SAMPLED or DETAILED scanMode is selected) * ;Added option for no_recompute on index REBUILD;Added restrictions for spatial and XML indexes;Always rebuild filtered indexes;If found, output list of disabled or hypothetical indexes so that you can act on them;Added range scan count to logging table for comparison;Added update index related stats (with defaults) before rebuild operations. This provides better cardinality estimation, and thus a more time-efficient operation when rebuilding;Bug fix in Reorganize statements.Bug fix in one Rescanning condition.
1.3.1	28-06-2011	Corrected issue with commands running on multiple partitions.Changed behaviour of update statistics when tables have multiple partitions.
1.3.2	01-07-2011	Changed objects named %Exclusions to %Exceptions. When re-deploying, existing %Exclusions table will be renamed and not recreated.Added procedure to check current batch execution progress (usp_CurrentExecStats).
1.3.3	08-07-2011	Corrected issue where explicit change in database scope parameter did not trigger rescan under certain conditions.Corrected statistics update thresholds.
1.3.4	22-07-2011	Bug fix in indexes information when working in SQL 2005.
1.3.5	15-11-2011	Bug fix in logging showing as NULL on some issued commands.Optimizations on support SP usp_AdaptiveIndexDefrag_Exceptions.
1.3.6	17-02-2012	Allow large object names in tables and indexes.
1.3.7	27-02-2012	Enhanced error reporting view to incorporate stats updates.Bug fix when certain index options were chosen together.
1.3.8	28-02-2012	Corrected view that reports last run.Added upgrade mode.
1.3.9	12-03-2012	Fixed upgrade mode in case old data cannot be copied back.
1.4.0	12-04-2012	Fixed issue with case sensitive servers.
1.4.1	17-05-2012	Fixed issue on support SP usp_AdaptiveIndexDefrag_Exceptions.
1.4.2	29-05-2012	Fixed issue on support SP usp_AdaptiveIndexDefrag_CurrentExecStats.Fixed issue with large object IDs.
1.4.3	29-08-2012	Fixed issue with upgrade mode data retention.Fixed issue with format dependent conversions.
1.4.4	10-09-2012	Fixed issue where running the procedure to print commands only, previous execution errors would still be reported.
1.4.5	12-10-2012	Added support for ignoring errors regarding database objects that were dropped since the defrag cycle began;Added support for disabling indexes before rebuilding (space saving feature) - see notes on parameter @disableNCIX.
1.4.6	23-01-2013	Added hard limit of 4 for MaxDOP setting;Changed default for statistics update to updates all stats in table, as opposed to just index related stats;Fixed issue on support SP usp_AdaptiveIndexDefrag_CurrentExecStats reporting incorrect number of already defraged indexes;Fixed null elimination message with vw_LastRun_Log;Incremented debug mode output;Redesigned table wide statistics update (see notes on parameter @updateStatsWhere);Fixed issue with upgrade mode leaving old tables behind.
1.4.7	28-01-2013	Fixed issue with exceptions not working on some days i.e, on a weekday that should not be doing anything, it was;Tuned online rebuild options;Redesigned support SP usp_AdaptiveIndexDefrag_Exceptions.
1.4.9	12-04-2013	Added support for Enterprise Core Edition;Added support for AlwaysOn secondary replicas;Changed maxdop hard limit to 8;Incremented debug mode output;Added support for sys.dm_db_stats_properties in statistics update, if on SQL 2008R2 SP2 or SQL 2012 SP1 or higher.
1.5.0	25-04-2013	Fixed issue with online rebuilds;Fixed issue with commands not being printed when choosing @ExecPrint = 0.
1.5.1	01-05-2013	Fixed issue with page locking off and yet trying index reorganize - should always rebuild;Fixed issue with specific database scope and AlwaysOn replica checking;Enhanced stats lookup for specific table scope;Fixed issue where disable index would also do extra update on previous index related statistic;Added support for online rebuild with LOBs in SQL Server 2012.
1.5.1.1	02-05-2013	Fixed MaxDOP issue introduced in v1.4.9;Fixed issue with DETAILED scan mode;.Fixed issue with extended indexes not being picked up in some cases.
1.5.1.2	05-05-2013	Fixed issue with print command while executing introduced in v1.5.1;Fixed miscellaneous issues with NULL values;Fixed issue where a statistics update error would show in the log associated with an XML or Spatial index.
1.5.1.4	10-05-2013	Fixed issue with statistics update when there is no work to be done, introduced in v1.5.1.
1.5.2	17-06-2013	Added option for lock timeout;Set deadlock priority to lowest possible;Simulate TF 2371 behavior on update statistics threshold;Fixed issue with @updateStatsWhere = 1 where not all non-index related statistics were updated.
1.5.3	02-07-2013	Fixed issue with updating statistics and XML indexes;Fixed issue with log data being partially overwritten;Fixed issue where using @fillfactor to reset fill factor to default would not actually reset.
1.5.3.1	08-07-2013	Fixed issue where using @fillfactor to reset fill factor to default would output command error.
1.5.4	12-09-2013	Changed system database exclusion choices;Fixed fill factor information not getting logged (thanks go to Chuck Lathrope);All statistics update now included in exception days rule.Changed partition handling to avoid unwaranted scanning and speed up process on tables with many partitions.
1.5.5	24-10-2013	Added more verbose to debug mode;Fixed issue with error while keeping original fill factor when it was already set to 0 on the index;Fixed issue with error 35337 or 2706 on update statistics.
1.5.6	28-10-2013	Added SQL 2014 support for online partition rebuild;Tuned LOB support with online operations;Improved detection of scope changes - saves unneeded database scans;Optimized defrag cycle pre-work with partially excluded databases;Fixed issue with skipping partially excluded databases;Added resilience for CS collations.Thanks to António Mendonça (Twitter) for contributions to this version's scope change fixes.
1.5.7	13-01-2014	Fixed issue with support SP usp_AdaptiveIndexDefrag_Exceptions with SQL Server 2005;Fixed issue with support SP usp_AdaptiveIndexDefrag_CurrentExecStats.
1.5.8	10-05-2014	Added SQL 2014 support for Online Lock Priority;Fixed issue introduced in previous version where an Online rebuild operation could not be executed in SQL 2012.
1.5.9	17-11-2014	Fixed issue on support SP usp_AdaptiveIndexDefrag_PurgeLogs.
1.6	20-11-2014	Added resilience when objects are dropped while being scanned, avoiding error 2573.
1.6.1	06-02-2015	Removed dependency of @scan_mode to use TF 2371 behavior for statistics update;Improved support for Columnstore indexes on SQL 2014, with specific rebuild threshold and reorg option.
1.6.2	10/05/2016	Added option to determine whether to exclude blobs from fragmentation scan;Added support for incremental statistics;Fixed PK issue with columnstore fragmentation discovery.Fixed issue where auto created statistics would not be picked up for update.
1.6.3	10/19/2016	Fixed issue with statistics collection in SQL Server 2012 and below;Fixed issue where indexes on views generated error 1934.
1.6.3.1	10/26/2016	Fixed failed migration from v1.6.2 with NULL insert error;Fixed issue when running in debug mode.
1.6.3.2	11/04/2016	Fixed DISABLE index applying to NCCI.Fixed statistics not being updated before index rebuild - introduced in v1.6.2;Fixed misplaced index disable statement if @Exec_Print = 0;Fixed issue with statistics collection in SQL Server 2012 and below;Added statistic related info to log table (rows, mod counter, rows sampled).
1.6.3.3	11/07/2016	Rolled back previously reported issue with REORGANIZE and database names.
1.6.4	11/10/2016	Fixed support for incremental statistics in SQL Server 2016 RTM.
1.6.4.1	11/16/2016	Added support for incremental statistics in SQL Server 2016 SP1.
1.6.4.2	1/20/2017	Fixed support for incremental statistics introduced error 4104.

* 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

Comments

Anonymous
July 03, 2011
The comment has been removed
Anonymous
April 25, 2013
Great product, but will not run online rebuilds on all enterprise version of 2008 r2 64x
Anonymous
April 25, 2013
Hello Bill,Thank you for your feedback. I have found the issue you were referring to and fixed it. You can update directly in your server.Cheers
Anonymous
April 29, 2013
Seems like a awesome maintenance tool. My concern is offline/online logic for BLOB columns. It appears you can rebuild online with sql 2012, but offline in other versions. Also is it possible to only update stats if rows have been updated since last stats operation? Also, without digging into the code, is this logic accounted for...Second, a single partition of an index with multiple partitions cannot be rebuilt ONLINE. You can REORGANIZE a single index partition (which is by nature ONLINE), but you cannot REBUILD a single index partition ONLINE. You can rebuild an entire index (all partitions) ONLINE.
Anonymous
April 30, 2013
The comment has been removed
Anonymous
April 30, 2013
So I tested the tool and found that it was looping through the same index many times on one of my servers. I think it is because it was not able to remove fragmentation because the index was too small. I changed the min page count to 100 and then it was able to get through all the indexes.
Anonymous
April 30, 2013
The comment has been removed
Anonymous
May 21, 2014
The comment has been removed
Anonymous
May 29, 2014
The comment has been removed
Anonymous
November 06, 2014
The comment has been removed
Anonymous
November 06, 2014
Hello,as I can see from the output ofexec usp_AdaptiveIndexDefrag @Exec_Print=0, @printCmds=1, @dbScope='db1'after rebuilding index update statistics is fired for the same tables.As per www.sqlskills.com/.../search-engine-qa-10-rebuilding-indexes-and-updating-statistics it is unnecessary, isn't it?
Anonymous
November 06, 2014
Hello,In the list of objects above number 9 should be usp_AdaptiveIndexDefrag_CurrentExecStats not usp_CurrentExecStats.Thanks for the script!
Anonymous
November 07, 2014
The comment has been removed
Anonymous
November 09, 2014
The comment has been removed
Anonymous
November 09, 2014
Answering based on the defaults set by Adaptive Index defrag, the flow will be:If index is to be reorganized, update index stats afterwards. If index is to be rebuilt, update stats prior to rebuild. If option is selected (default), update all other statistics.
Anonymous
November 19, 2014
The comment has been removed
Anonymous
November 19, 2014
It's now @offlinelocktimeout and @onlinelocktimeout - It was already documented in the parameter list above, but I've now updated the example.Thanks
Anonymous
December 08, 2014
One more thing. Rebuilding with MAXDOP > 1 can cause fragmentation as per this comment chrisadkin.org/.../super-scaling-the-sql-server-2014-parallel-insert-part-2-clustered-index-scans-versus-heap-scans
Anonymous
December 08, 2014
Fragmentation caused by MAXDOP > 1 with ALLOW_PAGE_LOCKS = ON which is by default is greatly reduced due to the bulk allocation cache as per thisblogs.msdn.com/.../how-it-works-online-index-rebuild-can-cause-increased-fragmentation.aspx
Anonymous
December 08, 2014
Hello,That may be encountered when doing ONLINE rebuilds with MAXDOP > 1 and that object happens to not allow PAGE_LOCKS. AdaptiveIndexDefrag has offline rebuild as default, so the defaults are not affected by this potential issue.
Anonymous
January 12, 2015
I think there's a typo in the documentation above."@printCmds defaults to 0 (do not print all commands to screen) or optionally 0 (print all commands to screen)."And thanks for this great and useful script!
Anonymous
January 12, 2015
Oh, and the same in "@debugMode defaults to 0 (do not display debug comments) or optionally 0 (display debug comments). ".Sorry, missed that before posting the last comment.
Anonymous
May 19, 2016
In the section "What objects are created when running the attached script?", item 8, the name of that procedure was changed "exception" instead of "exclusion"... Parameters too.
Anonymous
July 14, 2016
The comment has been removed
- Anonymous
  October 05, 2016
  Hey Antonio, thanks. This and other fixes are in the latest version 1.6.2

Share via

Adaptive Index Defrag

Comments

Additional resources