Monitoring For Endless Index Defragmenting – October 2015

This is the first of three big script updates this month, so stay tuned!

I’ve written before about how my SQL script evolve over time. Well, the script I just published last month has already been updated. I had been deploying the script on all the SQL Servers I manage, rather than just the handful I know had specific databases I wanted to monitor, and in the process of doing that, I realized there were a couple of things I could do to make things a little easier:

  • Using ALL_DATABASES as the value for @DatabaseToInvestigate will look at all databases with entries in the CommandLog table.
  • Added a check for missing and inaccessible databases.
  • Added output showing if current SQL edition supports online index rebuilds.
  • Added developer edition to list of editions that support online index rebuilds.
  • Added a new input parameter – @DatabasesToSkip. This can contain a comma separated list of databases you do not want the script to monitor.
  • Modified the @DatabaseToInvestigate input parameter to accept a comma separated list of databases to check. You can still just use a single database name or ALL_DATABASES to run against all the databases with entries in the CommandLog log table. (Should have changed the name to be plural, but didn’t want to introduce bugs.)
  • Added a check to make sure the specified mail profile exists. I don’t check to see if the account running the script actually has permission to use that profile however, so you’ll most like want to use the public mail profile here.

Note that the default delimiter in the @DatabasesToSkip and @DatabaseToInvestigate parameters is a comma, but that can be changed in the script.

There was one other change I wanted to put in – I wanted the command generation portion to identify if an index had LOB columns and thus could not be rebuilt online and set that option appropriately. That was going to require some more work and, it turns out, that restriction went away with SQL 2012, so I opted to not implement this. (You still can’t rebuild XML or spatial indexes online, but there are none of those in my environment.) Given that the commands don’t run automatically and require a DBA to execute them, I figured this was acceptable. It’s a simple matter to change the generated command.

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.

I think I’m happy with this script now and don’t foresee any changes in the future. Let me know if you have any recommendations!

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


*/

      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 )
                     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;


Share

Monitoring For Endless Index Defragmenting – September 2015

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




Share

New Online Course: Understanding Statistics In SQL Server

I’m pleased to announce my new online course is now up and available: Understanding Statistics in SQL Server. This course is almost 2 hours long and will teach you everything you need to know about statistics in SQL Server – what they look like, how they are used, and how to maintain them. I also go over common problems caused by out of date statistics and how to find and fix them. It includes demos and sample scripts.

course

Use this link to sign up (or the coupon code BLOG15OFF) to get a 15% discount.

Don’t forget my other courses are still available:

Microsoft SQL Server 101. Use this link for a 15% discount.

Understanding Data Compression in SQL Server. Use this link for a 15% discount.

SQL Server Maintenance Plans. User this link for a 15% discount.

 

Share

The Certification Shuffle

MCSA_SQL12_BlkLast weekend, I passed the last test needed to upgrade my MCTS: SQL Server 2008 certification to the MCSA: SQL Server 2012 certification. I still need to take one more exam to upgrade my MCITP: Database Administrator 2008 to the MCSE: Data Platform certification. I’ll  probably do this, but I’m not sure I’ll continue with Microsoft certifications after this. Here’s why:

Back in 2012, when Microsoft changed the (then still fairly new) MCTS and MCITP certifications to the MCSA and MCSE certs, I wrote what I liked about the change and also about several reasons why I didn’t like it.  As mentioned then, the new MCSE cert will no longer be tied to a specific version of SQL Server and will require re-certification every 3 years. At the time, I wrote “I have a strong suspicion that the re-certification exams are going to focus on the latest version of SQL Server that is out at the time. This puts people who work for companies that do not like to be on the “bleeding edge” at a disadvantage.

Sure enough… 3 years later, this appears to be exactly what has happened. The MCSE cert stated out with SQL 2012. In April 2014, the exam was updated with SQL 2014 topics. (See this and this (PDFs)). In January 2016, the MS 70-459 exam, which let you upgrade your MCITP: Database Administrator 2008 to MCSE: Data Platform will be retired. This is right around the time SQL Server 2016 will be out. So you can bet the exam for MCSE:Data Platform will be updated to include SQL 2016 features at that point.

And to make matters worse, Microsoft has announced that the MCSA level certification is ending with SQL 2012. Or, more specifically, that no MCSA: SQL Server 2014 will be offered – which more or less implies that there won’t be one for any other versions of SQL Server as well. (See the first question in the Q & A section in the link for more info.)

Taken altogether, let’s look at what this means for the SQL Server database professional in 2016 and beyond. No MCSA cert will be around, so we’ll all be forced to get the MCSE certification. This exam will focus primarily on the shiny new features of the latest version of SQL Server. SQL Server is now on a 2 year release cycle. This cert requires re-certification every 3 years. This means we will always be tested on the latest features of the latest version of SQL Server. OK, I can see why Microsoft wants to emphasize that. The big problem? Odds are, we won’t have any experience actually using those features.

Most vendors I deal with are just now starting to support SQL 2012 – and it’s been out for 3 or 4 years already. They simply don’t have the manpower to re-test their products on a new database version every two years. As a result, the day-to-day work of a DBA tends to be more with older versions of SQL Server, because that is all our vendors will allow us to use.

Can you see the disconnect? Microsoft is asking up to get certified on products we don’t use. I thought certifications were supposed to demonstrate that DBAs had experience and proficiency with certain software. This new policy will simply demonstrate the DBAs have “book learning” about a product. There will be no guarantee that they actually have experience using a product.

I’m already not a big fan of certifications. I think they are expensive and of dubious worth. In my opinion, they primarily are useful when you are looking for a new job and need to get past that first batch of resume screeners looking to see if a candidate meets the minimum requirements for a position. With the new changes, I think their worth drops even more. I’m not sure I’ll bother with any more certifications going forward.

Share

The Only Constant Is Change

8044372740_3ddac1850d_mMonitoring the health of your SQL Servers is a standard part of a DBA’s job. One of the most important parts, to tell you the truth. It’s tempting to think of databases as unchanging. You can check them once and, if they are healthy, your job is done. There is no need to check it again. But, just like people, their health can change over time.

I run my own health check scripts on my SQL Servers every quarter. I am amazed at what I find. I typically spent 2 to 3 weeks going through all my servers and correcting all the issues the script turns up. But what is even more amazing is how often I find problems cropping up again the next time I perform the health check.

One of the more common things I find are untrusted or disabled foreign keys and check constraints. Each quarter, these pop up in my checks and, each quarter, I re-enable them. They always seem to get disabled somewhere down the line. Sometimes it’s an in-house process that disables them. In that case, I can talk to the developers and correct the problem at the source. Sometimes, it’s a third party app that I have no control over.

I also find a lot of issues regarding jobs. One common one is jobs without error notifications. Often times, third party products install SQL jobs and they almost never set up a failure notification. I also find vendor jobs that perform index maintenance. Because I have my own index maintenance routines on my servers, I disable these jobs in favor of my own (after verifying my own will run as frequently as the vendor’s did). Because I don’t always know when new software is installed or upgraded, these issues come up each time I run my health checks, even if I have corrected the issue the last time I ran the check.

Bottom line: your SQL Servers are living things and they change over time. Even if you have them configured and running exactly they way you want them to right now, over time, they will change. Monitor them on a regular basis.

FYI, my health check script is a variation on Brent Ozar Unlimited’s sp_Blitz script. If you don’t have your own script to use, theirs is a great one to start with.

Share