This is just a bug fix from my last post of this code. I ran into a case where you would not get notified if the only indexes in a database that were being repeatedly defragged had fill factors of 50 or less. I would think this would be a pretty rare case, but it happened to me, so maybe it isn’t so rare after all.
As usual, this script should be run in sync with the frequency of how often you clear out the CommandLog table or you could end up with unnecessarily small recommendations for fill factors.
CREATE PROCEDURE [dbo].[up_IndexDefragFrequencyCheck]
(
@DatabaseToInvestigate VARCHAR(100) = NULL
,@DatabasesToSkip VARCHAR(MAX) = 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. A value of *
will scan all databases with log entries in the
CommandLog table.
@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
v1.4 - 9/16/15 - SJS - Added check for missing database to scan and missing CommandLog db
- Now a value of ALL_DATABASES for @DatabaseToInvestigate will scan all
databases with entries in the CommandLog table
v1.5 - 9/23/15 - SJS - Added output to indicate if SQL edition supports online index rebuilds
- Now identifies Developer edition as supporting online index rebuilds
v1.6 - 9/23/15 - SJS - Added check for valid mail profile name. Note - this does not check if the executing
user has permission to use the profile. It only checks that it exists.
- Added new input parameter @DatabasesToSkip, which can contain a CSV list
of databases to skip
- Modified input parameter @DatabaseToInvestigate to support CSV list of
of databases to investigate
v1.7 - 11/16/15 - SJS - Fixed bug where notices of indexes @ 50 or less fill factor were
note being reported if they were the only indexes in the db that
were being repeatedly defragged.
*/
SET NOCOUNT ON;
DECLARE @SQLstr VARCHAR(2000);
DECLARE @EmailBody VARCHAR(MAX);
DECLARE @CRLF CHAR(2);
DECLARE @EmailSubject VARCHAR(200);
DECLARE @SQLVersion TINYINT;
DECLARE @SupportsOnlineRebuilds BIT;
DECLARE @delimiter CHAR(1) ;
DECLARE @Items TABLE (Item NVARCHAR(4000));
DECLARE @LenList INT;
DECLARE @ld INT;
SET @CRLF = CHAR(13) + CHAR(10);
SET @delimiter = ',';
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'
OR LEFT(CAST(SERVERPROPERTY('edition') AS VARCHAR(50)),
9) = 'Developer'
BEGIN
SET @SupportsOnlineRebuilds = 1;
SELECT 'SQL Server edition does support online index rebuilds.';
END;
ELSE
BEGIN
SET @SupportsOnlineRebuilds = 0;
SELECT 'SQL Server edition does not support online index rebuilds.';
END;
/* Data validation */
IF @DatabaseToInvestigate IS NULL
OR @DatabaseToInvestigate = ''
BEGIN
SELECT 'A database to investigate must be specified.';
RETURN;
END;
IF NOT EXISTS ( SELECT 1
FROM sys.databases
WHERE name = @DatabaseToInvestigate
AND state = 0 )
AND @DatabaseToInvestigate <> 'ALL_DATABASES'
AND @DatabaseToInvestigate NOT LIKE '%,%'
BEGIN
SELECT 'The database '
+ @DatabaseToInvestigate
+ ' is not accessible or '
+ 'does not exist.';
RETURN;
END;
IF @CommandLogDatabase IS NULL
OR @CommandLogDatabase = ''
BEGIN
SELECT 'A database holding the CommandLog table must be specified.';
RETURN;
END;
IF NOT EXISTS ( SELECT 1
FROM sys.databases
WHERE name = @CommandLogDatabase
AND state = 0 )
BEGIN
SELECT 'The specified commandLog database, '
+ @CommandLogDatabase + ', is not '
+ 'accessible or does not exist.';
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;
IF NOT EXISTS ( SELECT 1
FROM msdb.dbo.sysmail_profile sp
WHERE name = @MailProfileName )
BEGIN
SELECT 'The specified mail profile, '
+ @MailProfileName
+ ', does not exist.';
RETURN;
END;
--============================================================================
-- Build list of dbs to check
--============================================================================
IF EXISTS ( SELECT name
FROM tempdb.sys.objects
WHERE name = '##DatabasesToInvestigate' )
BEGIN
DROP TABLE ##DatabasesToInvestigate;
END;
CREATE TABLE ##DatabasesToInvestigate (the_database
sysname);
IF @DatabaseToInvestigate = 'ALL_DATABASES'
BEGIN
SET @SQLstr = 'INSERT INTO ##DatabasesToInvestigate
(the_database)
SELECT DISTINCT DatabaseName
FROM [' + @CommandLogDatabase
+ '].dbo.CommandLog
ORDER BY DatabaseName;';
EXEC(@SQLstr);
END;
ELSE
BEGIN
/* insert dbs to check */
IF @DatabaseToInvestigate IS NOT NULL
BEGIN
SET @LenList = LEN(@DatabaseToInvestigate)
+ 1;
SET @ld = LEN(@delimiter);
WITH a AS (SELECT
[start] = 1
,[end] = COALESCE(NULLIF(CHARINDEX(@delimiter,
@DatabaseToInvestigate,
1), 0), @LenList)
,[value] = SUBSTRING(@DatabaseToInvestigate,
1,
COALESCE(NULLIF(CHARINDEX(@delimiter,
@DatabaseToInvestigate,
1), 0), @LenList)
- 1)
UNION ALL
SELECT
[start] = CONVERT(INT, [end])
+ @ld
,[end] = COALESCE(NULLIF(CHARINDEX(@delimiter,
@DatabaseToInvestigate,
[end] + @ld), 0),
@LenList)
,[value] = SUBSTRING(@DatabaseToInvestigate,
[end] + @ld,
COALESCE(NULLIF(CHARINDEX(@delimiter,
@DatabaseToInvestigate,
[end] + @ld), 0),
@LenList)
- [end] - @ld)
FROM a
WHERE [end] < @LenList
)
INSERT @Items
SELECT [value]
FROM a
WHERE LEN([value]) > 0
OPTION (MAXRECURSION 0);
SELECT Item
+ ' database name extracted'
FROM @Items;
INSERT INTO ##DatabasesToInvestigate
(the_database)
SELECT Item
FROM @Items;
END;
END;
/* Remove inaccessible databases */
SELECT the_database
+ ' database skipped (not accessible)'
FROM ##DatabasesToInvestigate
WHERE the_database IN (SELECT name
FROM sys.databases
WHERE state <> 0);
DELETE FROM ##DatabasesToInvestigate
WHERE the_database IN (SELECT name
FROM sys.databases
WHERE state <> 0);
/* Remove databases no longer present */
SELECT the_database
+ ' database skipped (no longer present)'
FROM ##DatabasesToInvestigate
WHERE the_database NOT IN (SELECT name
FROM sys.databases);
DELETE FROM ##DatabasesToInvestigate
WHERE the_database NOT IN (SELECT name
FROM sys.databases);
/* Remove databases requested to be skipped */
IF @DatabasesToSkip IS NOT NULL
BEGIN
DELETE FROM @Items;
SET @LenList = LEN(@DatabasesToSkip) + 1;
SET @ld = LEN(@delimiter);
WITH a AS (SELECT [start] = 1
,[end] = COALESCE(NULLIF(CHARINDEX(@delimiter,
@DatabasesToSkip,
1), 0), @LenList)
,[value] = SUBSTRING(@DatabasesToSkip,
1,
COALESCE(NULLIF(CHARINDEX(@delimiter,
@DatabasesToSkip,
1), 0), @LenList)
- 1)
UNION ALL
SELECT [start] = CONVERT(INT, [end])
+ @ld
,[end] = COALESCE(NULLIF(CHARINDEX(@delimiter,
@DatabasesToSkip,
[end] + @ld), 0),
@LenList)
,[value] = SUBSTRING(@DatabasesToSkip,
[end] + @ld,
COALESCE(NULLIF(CHARINDEX(@delimiter,
@DatabasesToSkip,
[end] + @ld), 0),
@LenList)
- [end] - @ld)
FROM a
WHERE [end] < @LenList
)
INSERT @Items
SELECT [value]
FROM a
WHERE LEN([value]) > 0
OPTION (MAXRECURSION 0);
SELECT Item
+ ' database skipped (per input parameter)'
FROM @Items;
DELETE FROM ##DatabasesToInvestigate
WHERE the_database IN (SELECT
Item
FROM
@Items);
END;
/* Now loop through all databases and do the work
Note: re-using @DatabaseToInvestigate variable. This is poor coding
but eliminates me having to re-write everything following this to
add in looping functionality.
*/
WHILE @DatabaseToInvestigate IS NOT NULL
BEGIN
SELECT TOP 1
@DatabaseToInvestigate = the_database
FROM ##DatabasesToInvestigate;
IF @@rowcount = 0
BREAK; /* no more dbs */
--============================================================================
-- 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 )
OR 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 = @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 @SupportsOnlineRebuilds = 1
BEGIN
SET @EmailBody = @EmailBody
+ 'SQL Server edition does support online index rebuilds.'
+ @CRLF + @CRLF;
END;
ELSE
BEGIN
SET @EmailBody = @EmailBody
+ 'SQL Server edition does NOT support online index rebuilds.'
+ @CRLF + @CRLF;
END;
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;
DELETE FROM ##DatabasesToInvestigate
WHERE the_database = @DatabaseToInvestigate;
END;
IF EXISTS ( SELECT name
FROM tempdb.sys.objects
WHERE name = '##DatabasesToInvestigate' )
BEGIN
DROP TABLE ##DatabasesToInvestigate;
END;
SET NOCOUNT OFF;
