Two months ago I blogged about a procedure I wrote that, if you are using Ola Hallengren’s index maintenance routine, can look for indexes that are being repeatedly defragged. This usually indicates that the fill factor can be adjusted downward so that the index doesn’t need to be defragged every time your maintenance routine runs. Why do you care if indexes are being repeatedly defragged? For one, the defragmentation process is logged, so it generates lots of log records that need to be backed up, which increases your backup times, restore times, and backup file sizes. If you are using log shipping or any other high availability solution that utilizes the log file, it simply creates more work for that process.
Without covering all of that again, let me just point you to my previous post which not only talks about this in a bit more detail, but also features unicorns.
I’ve made some improvements to the code:
- The old version used to generate commands to rebuild indexes using the ONLINE = ON argument. This is an Enterprise-only feature and this new version is now smart enough to detect if you are running Enterprise edition or not and sets the option correctly. (ONLINE = ON should also be generated on SQL 2008 Datacenter edition, but I do not have a copy of that to test with. If someone can verify this works, please let me know.)
- The generated ALTER INDEX code used the PARTITION = ALL argument. This was not introduced until SQL 2008, so when running on a SQL 2005 server, the generated command would fail. Oops. The code now checks for this and will not include it on SQL 2005 machines.
- I’ve modified the subject line of the email that is generated to include the name of the database where excessive defragmentation was found. This is helpful when you are checking several databases on the same server.
- There were two locations where I forgot to put square brackets around the database name when generating commands, which caused errors when database name included a period. This has been fixed.
Be sure to revisit the original post for instructions on how to use this procedure. Pay special attention to the bits about how often to run this and how it needs to be correlated with how often you clear Ola’s history table.
Known issues:
- The generated code will only work on non-partitioned indexes or indexes that are partitioned using aligned partitions (partitions that are based on equivalent partition functions). That’s just how PARTITION = ALL works. However, even if you have non-aligned partitioned indexes, this routine will still identify them for you if they are being defragged constantly – you’ll just have to write your own code to change the fill factor.
- This only examines one database at a time. Eventually, I want to modify this so it will look at all databases in Ola’s log table at once, but that requires more coding than I have time to do right now. It’s on the To Do list though.
Let me know if you find this useful or have any suggestions for improvements!
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.
v1.2 - 1/16/14 - SJS - Added check for SQL 2005, which does not support the PARTITION = ALL option
- Added check for Enterprise edition to set ONLINE = ON or OFF
- Added square brackets around DB name where they were not before to
handle db names with periods in them
- Added database name to email subject line
*/
DECLARE @SQLstr VARCHAR(2000)
DECLARE @EmailBody VARCHAR(MAX)
DECLARE @CRLF CHAR(2)
DECLARE @EmailSubject VARCHAR(200)
DECLARE @SQLVersion TINYINT
DECLARE @SupportsOnlineRebuilds BIT
SET @CRLF = CHAR(13) + CHAR(10)
SET @SQLVersion = LEFT(CAST(SERVERPROPERTY('productversion') AS VARCHAR(15)),
CHARINDEX('.',
CAST(SERVERPROPERTY('productversion') AS VARCHAR(15)))
- 1)
/* SQLVersion: 9 = SQL 2005, 10 = SQL 2008 or 2008 R2, 11 = 2012 */
IF LEFT(CAST(SERVERPROPERTY ('edition')AS VARCHAR(50)),10) = 'Enterprise'
OR LEFT(CAST(SERVERPROPERTY ('edition')AS VARCHAR(50)),10) = 'Datacenter'
BEGIN
SET @SupportsOnlineRebuilds = 1
END
ELSE
BEGIN
SET @SupportsOnlineRebuilds = 0
END
/* 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 ' +
CASE
WHEN @SQLVersion = 9 THEN ' '
WHEN @SQLVersion > 9 THEN 'PARTITION = ALL '
END
+ 'WITH (PAD_INDEX = OFF, '
+ 'STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, ' +
CASE @SupportsOnlineRebuilds
WHEN 1 THEN 'ONLINE = ON, '
WHEN 0 THEN 'ONLINE = OFF, '
END
+ @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 ' +
CASE
WHEN @SQLVersion = 9 THEN ' '
WHEN @SQLVersion > 9 THEN 'PARTITION = ALL '
END
+ 'WITH (PAD_INDEX = OFF, '
+ 'STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, ' +
CASE @SupportsOnlineRebuilds
WHEN 1 THEN 'ONLINE = ON, '
WHEN 0 THEN 'ONLINE = OFF, '
END
+ @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 + ', database ' + @DatabaseToInvestigate
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
Looks like there is an extra single-quote preceding productversion near the top
SET @SQLVersion = LEFT(CAST(SERVERPROPERTY(”productversion’) AS
Yes, there is and, believe it or not, I put it there intentionally. If there is only a single quote (as there should be), my blog plug-in that formats and colors the code for some reason doesn’t see it and the remainder of the code shows in red, like it was a quoted string. Try as I might, I can’t seem to overcome this. So I put two quotes in there, figuring it would be an easy error for people to spot and fix. Sorry.
Well, I fixed it. Turns out, the plugin was choking on the single quote in “weren’t” in the third item of my v1.2 comment. Changed it to “were not” and all is well.
Yep, was easy enough to find as it looked a little odd to begin with…
Thanks
Cheers