It’s been quite a while since I last took a look at my script to monitor for endless index defragmenting by Ola Hallengren’s index maintenance scripts. Before running this version, I suggest you read my first two posts about this process, including a very important caveat about the need to coordinate the frequency of running this script with the frequency you run Ola’s index maintenance routine.
I have this script running on several of my servers and, over time, I realized there was one thing the email notification was missing: although it gives suggested commands to change the fill factor, it doesn’t tell you why that recommendation was made. There are a couple variables involved in the decision: the number of index reorganizations that have been performed, the number of index rebuilds that have been performed, and the current index fill factor. I have found that I sometimes want to know why the recommendation was made. I have changed this so that the command now includes a comment stating the reason the command was generated.
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
v1.3 - 9/9/15 - SJS - Added comment to command output showing reorg/rebuild count and
current fill factor to explain why command was generated
- Corrected bug where recommendation was not decrementing current
fill factor correctly
*/
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(fill_factor - 10 AS VARCHAR(3))
WHEN (fill_factor > @OverRebuildThresholdNewFF AND
fill_factor <100)
THEN CAST(@OverRebuildThresholdNewFF AS VARCHAR(3))
ELSE NULL /* unknown case */
END + '); ' + @CRLF
+ '/* Analysis: Excessive rebuilds ('
+ CAST(rebuild_count AS VARCHAR(3)) + ') and '
+ 'current fill factor = ' + CAST(fill_factor AS VARCHAR(3))
+ ' */' +@CRLF + @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(fill_factor - 10 AS VARCHAR(3))
WHEN (fill_factor > @OverReorgThresholdNewFF AND
fill_factor <100)
THEN CAST(@OverReorgThresholdNewFF AS VARCHAR(3))
ELSE NULL /* unknown case */
END + '); ' + @CRLF
+ '/* Analysis: Excessive reorgs ('
+ CAST(reorg_count AS VARCHAR(3)) + ') and '
+ 'current fill factor = ' + CAST(fill_factor AS VARCHAR(3))
+ ' */' + @CRLF + @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