You are an awesome DBA. You have complete control over your dominion. You have all your backups automated and the testing of those backups is automated. You have alerts properly configured on all your servers. You have your index maintenance plans running regularly. You ride a unicorn to the office and George Hamilton asks you for suntanning tips. You are da bomb.
When everything is running smoothly, it can be easy to lose sight of the little details because they are being handled automatically by your routines. But if you take the time to peek under the covers, you may discover some wasteful behavior that your perfect maintenance routines are camouflaging.
I’m specifically talking about index defragmentation. I use Ola Hallengren’s index maintenance script because, well, why wouldn’t I? It rocks. Ola doesn’t ride a unicorn to the office – he rides a flying unicorn. Because his routine works so well, it can be easy to just set it up and forget about it. I was surfing the web the other day and came across this blog post by Tim Ford and I immediately realized he was writing about my situation. Although I had not spent the time to investigate, I just knew I had databases whose indexes are stuck in an endless cycle of defragmenting. They are in use for a week, they get defragged over the weekend, they get used the next week, they get defragged again, etc. Rinse and repeat. This is a waste of resources. Index rebuilds or reorgs generate a lot of transaction log entries, for one thing. It also chews up time in your maintenance window that could be used for other tasks.
So how can you stop this? One way is to change the fill factor on your indexes. By default, SQL Server tries to completely fill index pages and that can lead to heavy fragmentation as rows get modified and inserted. But if you set your fill factor to a lower percentage than the default 0 (which is equivalent to 100%), SQL will leave some space on the pages and then it may have more room to fit newly inserted or modified rows onto the page. Less page splits = less fragmentation.
But you probably don’t want to change the fill factor on all your indexes across the board. Specifically targeting the indexes that are most frequently defragmented will allow you to only make changes where you will reap the most benefit.
And this is what Tim’s code helps you do. It will scan the log table that Ola’s index maintenance routine creates and will identify indexes that are frequently rebuilt or reorganized. I’ve taken Tim’s script and made some additions to it. I’ve turned it into a stored procedure that you can run that will send out an email it if finds any indexes that are being defragged often. It will also look at the current fill factor and create a rebuild statement to rebuild the index with a lower fill factor to help reduce fragmentation. I’ve parameterized most everything. Here’s how it works:
You call the routine and specify several parameters:
- @DatabaseToInvestigate – This is the database you want to scan for frequently defragged indexes
- @CommandLogDatabase – The database where the CommandLog table from Ola’s routine is located
- @CountThreshold – Indexes that are defragged equal to or more than this number of times are flagged as being excessively defragged
- @OverRebuildThresholdNewFF – This is the new fill factor recommendation for indexes that are being rebuilt more than @CountThreshold number of time. It is only used when the current fill factor is 0 or 100.
- @OverReorgThresholdNewFF – This is the new fill factor recommendation for indexes that are being reorganized more than @CountThreshold number of time. It is only used when the current fill factor is 0 or 100.
- @MailProfileName – the name of the mail profile this routine can use to send out the email notification
- @MailRecipients – the list of email addressed the notification will be sent to. Must be in the form xxx@xxx.xxx. Multiple address should be separated with a semicolon
The procedure works by scanning the CommandLog table for indexes that are either rebuilt or reorganized more than @CounterThreshold times. If it finds any, it checks the current fill factor of those indexes. If the fill factor is 0 or 100, it will create an ALTER INDEX statement with the new fill factor specified in the @OverRebuildThresholdNewFF or @OverReorgThresholdNewFF parameter. (This is because indexes that are reorganized typically have a lower fragmentation value than those that are rebuilt, so they may not need to have their fill factor adjusted quite so much.) If the current fill factor is not 0 or 100, it will create an ALTER INDEX statement where the fill factor is 10% less than the current value. IT WILL NOT CREATE A STATEMENT WITH A FILL FACTOR LESS THAN 50!
If you have indexes that are being repeatedly defragged and the fill factor is set to 50% or less, you’ve got a heck of a busy table. That may be perfectly normal and a lower fill factor may be exactly what is needed, but I wanted to raise a red flag in this case and bring this to your attention rather than just provide some code that might get run without being looked at. To that end, if the procedure does find this case, it will add some additional text to the email bringing this to your attention and will list the tables and indexes involved.
One very important note on running this procedure: You cannot just mindlessly plug values into these parameters and run the code whenever you want. You need to take into account how often you perform index maintenance and how long you retain records in the CommandLog table. These two factors will determine how you pick your @CountThreshold value and how often you run this routine.
For example, suppose you perform index maintenance each weekend and you retain 30 days worth of log records. In this case, you’d probably choose @CountThreshold to be 3 or 4 and you’d run this procedure once every 30 days. Because you are only retaining 30 days worth of records in the log table, the maximum possible amount of log entries an index could have would be 4. (Typically. You may get 5 depending on how your run schedule coincides with your log purging schedule in months that have 5 weekends, such as November, 2013)). You only want to run the procedure again after all your logs have had a chance to roll out of the table since the last time this procedure was run. Why is this?
Let’s look at what would happen if you defrag weekly, keep 30 days of log records and you run this routine daily. The first time you run it, it may tell you to reduce the fill factor of some indexes. You do that. Then the next day, the routine runs again. But your defrag routine has not run again and you still have all those old log records in your log table. This routine doesn’t know you changed the fill factor yesterday, so it’s going to recommend you reduce the fill factor by another 10 percent over what you reduced it to yesterday! (Remember, the routine will look at the current fill factor and make suggestions based on that.)
So your @CountThreshold and frequency of running this monitoring routine depend on other factors. Suppose you defrag indexes weekly and you retain 60 days of log records. In this case, you might want to pick @CountThreshold to be 6 or 7 and you would run this monitoring routine every 60 days. Likewise, if you perform index maintenance nightly and you retain 30 days of log records, you might set @CountThreshold to be somewhere between 20 and 30 (depending on how often you want to define “frequently”) and you would run this routine every 30 days.
Here is a screenshot of the email the routine sends out:
The routine defaults to making commands to rebuild the index with SORT_IN_TEMPDB = ON and ONLINE = ON. The command will fail for XML and spatial indexes because they cannot be rebuilt online. Also, online rebuilds are an Enterprise only feature, so if you are not running Enterprise edition, you’ll want to modify this to be off. The commands it prints out are meant to help a DBA, not replace one. They should be evaluated for appropriateness before being run. (This is why I wrote the routine to just print the statements instead of actually executing the command.)
The code for this procedure is below. As always, give any code you get from the internet a good once-over before running it. Let me know if you have any suggestions for improvements. Or if you know of a place to get cheap unicorn food.
CREATE PROCEDURE [dbo].[up_IndexDefragFrequencyCheck] ( @DatabaseToInvestigate VARCHAR(100) = NULL ,@CommandLogDatabase VARCHAR(100) = NULL ,@CountThreshold TINYINT = 4 ,@OverRebuildThresholdNewFF TINYINT = 70 ,@OverReorgThresholdNewFF TINYINT = 80 ,@MailProfileName SYSNAME ,@MailRecipients VARCHAR(MAX) = NULL ) AS /* This procedure scans the CommandLog table of Ola Hallengren's index defrag script (http://ola.hallengren.com/) and identifies indexes that are being rebuilt or reorganized regularly. The original code comes courtesy of Tim Ford, who blogged this at http://thesqlagentman.com/2013/10/whats-the-frequency-kenneth/. This routine expands his work. The procedure is meant to be run on a regular basis and will send an email when it finds indexes being defragged more than a specified number of times. It will also suggest rebuilding the index with a new fill factor to reduce the number of rebuilds or reorgs. The new fill factor recommended is one you specify, if the current fill factor of the index is 0 or 100%, otherwise it is 10% less than the current fill factor. It will not recommend anything less than 50%. IMPORTANT!!! This routine looks at the log table and the _current_ fill factor setting of the indexes. Therefore, in order to get appropriate fill factor recommendations, this routine should NOT be run more often than the log table is cleared. FOR EXAMPLE, IF YOU KEEP 30 DAYS WORTH OF LOG RECORDS IN YOUR TABLE, THIS ROUTINE SHOULD NOT BE RUN MORE OFTEN THAN EVERY 30 DAYS. This is because if you change the fill factor of an index it may result in stopping rebuilds, but the log table will still have the records of the prior rebuilds, which may trigger another recommendation to reduce the fill factor, which would then be 10% less than what you just changed it to. Do not blindly run the commands in the email notification! They are meant to make it easier for a DBA to change the fill factor, but they should be evaluated first to ensure they are appropriate for your situation. Input parameters: @DatabaseToInvestigate - The database to scan for excessive defrags @CommandLogDatabase - Database holding the CommandLog table of Ola's routine @CountThreshold - indexes being defragged equal to or greater this number of times are flagged as being excessively defragged @OverRebuildThresholdNewFF - Initial starting fill factor suggestion for indexes that are being rebuilt more than @CountThreshold times. Only used when current fill factor is 0 or 100 @OverReorgThresholdNewFF - Initial starting fill factor suggestion for indexes that are being reorganized more than @CountThreshold times. Only used when current fill factor is 0 or 100 @MailProfileName - name of mail profile the routine can use to send email @MailRecipients - list of email addresses the notification will be sent to. Must be of the form xxx@xxx.xxx Multiple addresses should be separated by a ; Shaun J. Stuart shaunjstuart.com v1.0 - 10/8/13 v1.1 - 10/13/13 - SJS - Added support for case where current fill factor between 100 and the min thresholds passed in. */ DECLARE @SQLstr VARCHAR(2000) DECLARE @EmailBody VARCHAR(MAX) DECLARE @CRLF CHAR(2) DECLARE @EmailSubject VARCHAR(200) SET @CRLF = CHAR(13) + CHAR(10) /* Data validation */ IF @DatabaseToInvestigate IS NULL OR @DatabaseToInvestigate = '' BEGIN SELECT 'A database to investigate must be specified.' RETURN END IF @CommandLogDatabase IS NULL OR @CommandLogDatabase = '' BEGIN SELECT 'A database holding the CommandLog table must be specified.' RETURN END IF @MailRecipients IS NULL OR @MailRecipients = '' BEGIN SELECT 'At least one email recipient must be specified.' RETURN END IF @MailRecipients NOT LIKE '%@%.%' BEGIN SELECT 'Email recipient not valid address format.' RETURN END --============================================================================ -- INDEX REBUILD COUNT --============================================================================ IF EXISTS ( SELECT name FROM tempdb.sys.objects WHERE name = '##Recommendations' ) BEGIN DROP TABLE ##Recommendations END IF EXISTS ( SELECT name FROM tempdb.sys.objects WHERE name = '##Index_History' ) BEGIN DROP TABLE ##Index_History END IF EXISTS ( SELECT name FROM tempdb.sys.objects WHERE name = '##Indexes' ) BEGIN DROP TABLE ##Indexes END CREATE TABLE ##Recommendations ( CommandLine VARCHAR(MAX) ); CREATE TABLE ##Index_History ( the_database SYSNAME ,the_schema SYSNAME ,the_object SYSNAME ,the_index SYSNAME ,index_type VARCHAR(13) ,fill_factor TINYINT ,rebuild_count INT NULL ,reorg_count INT NULL ); CREATE TABLE ##Indexes ( the_schema SYSNAME ,the_object SYSNAME ,the_index SYSNAME ,fill_factor TINYINT ); SET @SQLstr = 'INSERT INTO ##Indexes (the_schema ,the_object ,the_index ,fill_factor) SELECT OBJECT_SCHEMA_NAME(SO.object_id, DB_ID(''' + @DatabaseToInvestigate + ''')) AS the_schema ,SO.name AS the_object ,SI.name AS the_index ,SI.fill_factor FROM ' + @DatabaseToInvestigate + '.sys.objects SO INNER JOIN ' + @DatabaseToInvestigate + '.sys.indexes SI ON SO.object_id = SI.object_id WHERE SI.index_id > 0;' --PRINT @SQLstr EXEC (@SQLstr) SET @SQLstr = 'INSERT INTO ##Index_History (the_database ,the_schema ,the_object ,the_index ,index_type ,fill_factor ,rebuild_count ,reorg_count) SELECT C.DatabaseName ,C.SchemaName ,C.ObjectName ,C.IndexName ,CASE C.IndexType WHEN 1 THEN ''Clustered'' ELSE ''Non-Clustered'' END AS IndexType ,IX.fill_factor ,COUNT(C.ID) AS rebuild_count ,0 AS reorg_count FROM ' + @CommandLogDatabase + '.dbo.CommandLog C LEFT JOIN ##Indexes IX ON C.SchemaName = IX.the_schema AND C.ObjectName = IX.the_object AND C.IndexName = IX.the_index WHERE C.CommandType = ''ALTER_INDEX'' AND C.ObjectType = ''U'' AND C.Command LIKE ''%REBUILD%'' AND C.DatabaseName = ''' + @DatabaseToInvestigate + ''' GROUP BY C.DatabaseName ,C.SchemaName ,C.ObjectName ,C.IndexName ,IndexType ,IX.fill_factor;' --PRINT @SQLstr EXEC (@SQLstr) --============================================================================ -- INDEX REORGANIZE (ONLY) COUNT --============================================================================ SET @SQLstr = 'INSERT INTO ##Index_History (the_database ,the_schema ,the_object ,the_index ,index_type ,fill_factor ,rebuild_count ,reorg_count) SELECT C.DatabaseName ,C.SchemaName ,C.ObjectName ,C.IndexName ,CASE C.IndexType WHEN 1 THEN ''Clustered'' ELSE ''Non-Clustered'' END AS IndexType ,IX.fill_factor ,0 AS rebuild_count ,COUNT(C.ID) AS reorg__count FROM ' + @CommandLogDatabase + '.dbo.CommandLog C LEFT JOIN ##Indexes IX ON C.SchemaName = IX.the_schema AND C.ObjectName = IX.the_object AND C.IndexName = IX.the_index LEFT JOIN ##Index_History IH ON C.DatabaseName = IH.the_database AND C.SchemaName = IH.the_schema AND C.ObjectName = IH.the_object AND C.IndexName = IH.the_index WHERE C.CommandType = ''ALTER_INDEX'' AND C.ObjectType = ''U'' AND C.Command LIKE ''%REORGANIZE%'' AND C.DatabaseName = ''' + @DatabaseToInvestigate + ''' AND IH.the_database IS NULL GROUP BY C.DatabaseName ,C.SchemaName ,C.ObjectName ,C.IndexName ,IndexType ,IX.fill_factor;' --PRINT @SQLstr EXEC (@SQLstr) --======================================================== -- ACCOUNT FOR INDEXES BOTH REBUILT AND REORGANIZED --======================================================== SET @SQLstr = 'UPDATE ##Index_History SET reorg_count = C2.reorganize_count FROM ##Index_History IH INNER JOIN (SELECT C.DatabaseName ,C.SchemaName ,C.ObjectName ,C.IndexName , COUNT(C.ID) AS reorganize_count FROM ' + @CommandLogDatabase + '.dbo.CommandLog C WHERE C.CommandType = ''ALTER_INDEX'' AND C.ObjectType = ''U'' AND C.Command LIKE ''%REORGANIZE%'' AND C.DatabaseName = ''' + @DatabaseToInvestigate + ''' GROUP BY C.DatabaseName ,C.SchemaName ,C.ObjectName ,C.IndexName ) C2 ON IH.the_database = C2.DatabaseName AND IH.the_schema = C2.SchemaName AND IH.the_object = C2.ObjectName AND IH.the_index = C2.IndexName WHERE IH.rebuild_count > 0' --print @SQLstr EXEC (@SQLstr) --============================================================================ -- RETURN THE RESULTS --============================================================================ SELECT the_database ,the_schema ,the_object ,the_index ,index_type ,ISNULL(CONVERT(VARCHAR(20), fill_factor), 'No longer exists') AS fill_factor ,rebuild_count ,reorg_count FROM ##Index_History WHERE rebuild_count >= @CountThreshold OR reorg_count >= @CountThreshold ORDER BY the_database ,rebuild_count DESC ,reorg_count DESC ,the_object ,the_index INSERT INTO ##Recommendations (CommandLine) SELECT 'USE [' + the_database + ']; ' + @CRLF + 'ALTER INDEX [' + the_index + '] ON [' + the_schema + '].[' + the_object + '] ' + @CRLF + 'REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, ' + 'STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, ONLINE = ON, ' + @CRLF + 'ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = ' + CASE WHEN fill_factor = '0' THEN CAST(@OverRebuildThresholdNewFF AS VARCHAR(3)) WHEN fill_factor = '100' THEN CAST(@OverRebuildThresholdNewFF AS VARCHAR(3)) WHEN fill_factor <= @OverRebuildThresholdNewFF THEN CAST(@OverRebuildThresholdNewFF - 10 AS VARCHAR(3)) WHEN (fill_factor > @OverRebuildThresholdNewFF AND fill_factor <100) THEN CAST(@OverRebuildThresholdNewFF AS VARCHAR(3)) ELSE NULL /* unknown case */ END + '); ' + @CRLF AS RebuildCommand FROM ##Index_History WHERE rebuild_count >= @CountThreshold AND (fill_factor = 0 OR fill_factor >= 60) UNION ALL SELECT 'USE [' + the_database + ']; ' + @CRLF + 'ALTER INDEX [' + the_index + '] ON [' + the_schema + '].[' + the_object + '] ' + @CRLF + 'REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, ' + 'STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, ONLINE = ON, ' + @CRLF + 'ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = ' + CASE WHEN fill_factor = '0' THEN CAST(@OverReorgThresholdNewFF AS VARCHAR(3)) WHEN fill_factor = '100' THEN CAST(@OverReorgThresholdNewFF AS VARCHAR(3)) WHEN fill_factor <= @OverReorgThresholdNewFF THEN CAST(@OverReorgThresholdNewFF - 10 AS VARCHAR(3)) WHEN (fill_factor > @OverReorgThresholdNewFF AND fill_factor <100) THEN CAST(@OverReorgThresholdNewFF AS VARCHAR(3)) ELSE NULL /* unknown case */ END + '); ' + @CRLF AS RebuildCommand FROM ##Index_History WHERE reorg_count >= @CountThreshold AND (fill_factor = 0 OR fill_factor >= 60) IF EXISTS ( SELECT 1 FROM ##Recommendations ) BEGIN SET @EmailBody = @CRLF + 'Analysis of the index defrag log table has been performed and it appears ' + 'there are indexes that are being repeatedly defragged. Repeated defragging may ' + 'indicate the indexes need a lower fill factor to reduce page splits. Repeated ' + 'defragging also wastes resources and generates large amounts of potentially ' + 'unnecessary transaction log entries. The parameters used to generate this report ' + 'are: ' + @CRLF + @CRLF + 'Server: ' + @@SERVERNAME + @CRLF + 'Database: ' + @DatabaseToInvestigate + @CRLF + 'Min number of rebuilds / reorgs flagged as excessive: ' + CAST(@CountThreshold AS VARCHAR(3)) + '. ' + @CRLF + 'Starting suggested fill factor for excessive rebuilds: ' + CAST(@OverRebuildThresholdNewFF AS VARCHAR(3)) + '. ' + @CRLF + 'Starting suggested fill factor for excessive reorgs: ' + CAST(@OverReorgThresholdNewFF AS VARCHAR(3)) + '. ' + @CRLF + @CRLF + @CRLF + 'Below are the suggested commands to change the fill factor on the indexes in question. ' + 'These commands should be carefully evaluated before being run to ensure they are ' + 'appropriate for the situation! In particular, if this routine is run more frequently ' + 'than the defrag log table is cleared, it may result in inappropriate fill factor ' + 'recommendations.' + @CRLF + @CRLF + @CRLF IF EXISTS ( SELECT 1 FROM ##Index_History WHERE (rebuild_count >= @CountThreshold OR reorg_count >= @CountThreshold) AND fill_factor <= 50 AND fill_factor > 0) BEGIN SET @EmailBody = @EmailBody + @CRLF + @CRLF + 'Note: Some indexes were found that are being defragged regularly and have a fill factor ' + 'setting of 50 or less. Reducing the fill factor further is not generally recommended ' + 'and further investigation is warranted.' + @CRLF + @CRLF INSERT INTO ##Recommendations (CommandLine) SELECT '/* Database [' + the_database + '], index [' + the_index + ']' + ' on [' + the_schema + '].[' + the_object + '] ' + 'is being repeatedly rebuilt and its fill factor ' + 'is already set to 50 or less. This likely is worth a deeper ' + 'investigation. */' AS RebuildCommand FROM ##Index_History WHERE rebuild_count >= @CountThreshold AND fill_factor <= 50 AND fill_factor > 0 UNION ALL SELECT '/* Database [' + the_database + '], index [' + the_index + ']' + ' on [' + the_schema + '].[' + the_object + '] ' + 'is being repeatedly reorganized and its fill factor ' + 'is already set to 50 or less. This likely is worth a deeper ' + 'investigation. */' AS RebuildCommand FROM ##Index_History WHERE reorg_count >= @CountThreshold AND fill_factor <= 50 AND fill_factor > 0 END SET @EmailSubject = 'Excessive Index Defragmenting found on ' + @@Servername EXEC msdb.dbo.sp_send_dbmail @profile_name = @MailProfileName ,@recipients = @MailRecipients ,@subject = @EmailSubject ,@body = @EmailBody ,@query = 'SELECT * FROM ##Recommendations' ,@query_result_width = 32767 ,@query_no_truncate = 1 END --============================================================================ -- CLEANUP THE MESS --============================================================================ IF EXISTS ( SELECT name FROM tempdb.sys.objects WHERE name = '##Index_History' ) BEGIN DROP TABLE ##Index_History END IF EXISTS ( SELECT name FROM tempdb.sys.objects WHERE name = '##Indexes' ) BEGIN DROP TABLE ##Indexes END IF EXISTS ( SELECT name FROM tempdb.sys.objects WHERE name = '##Recommendations' ) BEGIN DROP TABLE ##Recommendations END
3 thoughts on “Monitoring For Endless Index Defragmenting”