Adaptive Index Defrag

Blog do Ezequiel
Portuguese PFE SQL Server Team


Latest Updates
10/05/2014 - Latest update on the AdaptiveIndexDefrag procedure v1.5.8. Look for it in the "Ezequiel shortcuts" section on the right or just click HERE.

10/05/2014 - Updates to Maintenance plan deployment scripts available HERE

04/06/2014 - The SQL Swiss Army Knife Series Index

Adaptive Index Defrag

Rate This
  • Comments 9

Hello all, because the procedure usp_AdaptiveIndexDefrag has undergone a few more updates, starting now this post will be the only source of information for it, including a full change log at the end of this post. It’s also reachable from the links section on the right anywhere on the blog.

Download latest version here: usp_AdaptiveIndexDefrag.sql
 
Latest update: 10-05-2014 (Check change log)
 
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:
  • If using SQL Server 2012 up to SP1 CU10 (build 11.0.3431), then install the hotfix in http://support.microsoft.com/kb/2969896
  • If using SQL Server 2012 SP2 or SQL Server 2014, and until the hotfix ships in SQL Server 2012 SP2 CU1 and SQL Server 2014 CU2, then besides the @onlineRebuild option, also use @maxdopRestriction = 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.
@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.

@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).

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_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 = 'AdventureWorks2008R2'
    Same as above, except its scope is only the 'AdventureWorks2008R2' database.
  3. EXEC dbo.usp_AdaptiveIndexDefrag @dbScope = 'AdventureWorks2008R2', @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 = 'AdventureWorks2008R2', @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 @locktimeout = 300
    Using the operating defaults in 1, will set the lock timeout value for 300s when doing offline rebuilds.
  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:

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.

 * 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.

Social Media Sharing
|
Leave a Comment
  • Please add 4 and 4 and type the answer here:
  • Post
  • Great work, Pedro !

  • Great product, but will not run online rebuilds on all enterprise version of 2008 r2 64x

  • 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

  • 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.

  • Hi Ross,

    Thanks for your feedback.

    At the moment, Nonclustered indexes can be rebuilt online if the table contains LOB data types, but these are not part of the index as either key or non-key columns. But as you said, this is new to SQL Server 2012, and I'm not accounting for that yet (vNext).

    On your second inquiry, that does not happen anyway. If ONLINE is set on ON, it is only accounted for if the edition and index type allows it, as well as if the index is not partitioned (if a user enters a parameter stating that only partition xx should be rebuilt, but there is more than 1 "partition", than it ignores the ONLINE parameter.

    Cheers

  • 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.

  • Hey Ross,

    Can't see how a loop would be created in the same run. Is that what you're meaning? Would you be interested in taking this offline? Perhaps sending me an email to pedro.lopes@microsoft.com with the output of debug mode?

    Thanks

    PL

  • Hi Pedro!

    Amazing job but I find the code a little bit difficult to read.

    I did some reengineering and I want to share the code with you.

    Can you tell me how I can get in contact with you - thanks!

  • Hello Ross,

    Curious on what you mean. You can reach me at pedro.lopes@microsoft.com

Page 1 of 1 (9 items)