Shaun J Stuart

Just another SQL Server weblog

At my company, it's time for our yearly performance evaluations. The first step of this process is for the employee to perform a self-evaluation. This includes rating yourself from Needs Improvement to Outstanding in 5 areas our company has deemed important. More importantly, it also includes a section where you list your specific achievements and strengths for the past year. While the first part is simply a bunch of checkboxes, this last section is where you can really demonstrate to management how awesome you are and how valuable you are to the company.

When filling out this section, I like to include not only specific actions I have taken that helped the company, but also any steps I have taken to further develop myself professionally. I believe this helps communicate to management that you take your role seriously and you are concerned with staying current in your field. This has the potential to turn into a nice circle of positive reinforcement where the company recognizes the value in furthering your education and becomes willing to spend more training dollars on you.

My 2013 self-appraisal reads:

Accomplishments

  • Retired 1 of 3 remaining SQL Server 2000 servers.
  • Reduced number of SQL Server 2005 servers from 38 to 28.
  • Virtualized several production and development SQL Servers
  • Implemented database compression on our two largest SQL Servers. This saved 1.2 TB of expensive SAN storage while improving database performance.
  • Tuned SQL queries for <reporting database> resulting in query runtimes decreasing from 1.5 hours to less than 1 second.
  • No SQL Server-related actionable items found by audit for second year in a row.

Professional Development

If you were writing a self-appraisal for 2013, how would yours read?

Share

Lately, I noticed that SSMS was taking longer and longer to start. When I started at my company a couple years ago, SSMS would come up fairly quickly. Now, it was taking about 3 minutes to start up. As the time gradually increased, I initially chalked it up to newer versions of SQL - in my time here, I've gone from using the SSMS version that comes with SQL 2005 to 2008 R2 to 2012. We all know software experiences bloat over time as more features get added to newer releases. But today, during an unrelated issue, I happened to be going through my computer's System Event log and saw a bunch of DCOM errors:

SSMS_DCOM_Error

I decided to track down the cause. The timestamps all seemed to indicate that problem occurred shortly after my computer booted. I spent too much time chasing down processes that ran on my machine during the Windows boot process before I finally realized my problem was in how I use SQL Server Management Studio:

DecomServers

As I decommissioned servers, I would move their entries in my SSMS Registered Server list to to a special folder. I kept the entries around because on some of my servers, I connect via Windows authentication, some use SQL authentication, some are referenced by DNS name, some by IP address, etc. I wanted to save all that connection configuration info in case I needed it later for some reason.

As it turns out, this was what was causing SSMS to take so long to launch. When SSMS launches, it tries to query all the servers in the Registered Server list to see if they are offline or online so it can display the little green arrow or red square icon next to the server name. By leaving the decommissioned server entries in the list, SSMS was trying to connect to servers that no longer existed. Thus, on launch, I had to wait for several DCOM timeouts to occur before SSMS was available for use. As soon as I removed these entries, SSMS started quickly once more and the DCOM errors no longer appeared in my Windows event log.

As my length of employment here has increased, the number of decommissioned servers has increased, which explains the slowly increasing SSMS start up delay. Why did this always seem to occur when my system booted?  SSMS is the first program I launch when I power up and log in each morning. Mystery solved!

Share

My new online course is now up and available: Understanding Data Compression in SQL Server. This course is almost 1.5 hours long and covers the technical details of how Data Compression in SQL Server works and when and how to implement it. The course includes demo scripts and examples.

UnderstandingDataCompression

From now until December 31, 2013, the first 100 people to sign up can use this link (or coupon code FALL50) to get a 50% discount.

Don't forget - my other course, Microsoft SQL Server 101, is still available. Use this link for a 15% discount.

Share

I wrote almost a year ago about an issue I ran into with Dynamics / Great Plains. The program intentionally tries to select a non-existent column from various tables as a means to determine if the table exists. Back in the days of SQL 6.5, when the Great Plains code was written, a "table / column not found" error was returned faster than just doing a query to see if the table existed. Unfortunately, in modern versions of SQL Server this error also throws a severity 20 error and, if you are following Microsoft's best practices for configuring alerts on your SQL Server, the server will generate an alert every time this happens.

So I created a Connect item asking that this be changed. Yesterday, I received notification that this issue is "being tracked" and may be fixed in a future release. I'm not going to hold my breath, but at least someone is tracking it and there is a glimmer of hope it will get fixed.

Share

You are an awesome DBA. You have complete control over your dominion. You have all your backups automated and the testing of those backups is automated. You have alerts properly configured on all your servers. You have your index maintenance plans running regularly. You ride a unicorn to the office and George Hamilton asks you for suntanning tips. You are da bomb.

Ola's morning commute

Ola's morning commute

When everything is running smoothly, it can be easy to lose sight of the little details because they are being handled automatically by your routines. But if you take the time to peek under the covers, you may discover some wasteful behavior that your perfect maintenance routines are camouflaging.

I'm specifically talking about index defragmentation. I use Ola Hallengren's index maintenance script because, well, why wouldn't I? It rocks. Ola doesn't ride a unicorn to the office - he rides a flying unicorn. Because his routine works so well, it can be easy to just set it up and forget about it. I was surfing the web the other day and came across this blog post by Tim Ford and I immediately realized he was writing about my situation. Although I had not spent the time to investigate, I just knew I had databases whose indexes are stuck in an endless cycle of defragmenting. They are in use for a week, they get defragged over the weekend, they get used the next week, they get defragged again, etc. Rinse and repeat. This is a waste of resources. Index rebuilds or reorgs generate a lot of transaction log entries, for one thing. It also chews up time in your maintenance window that could be used for other tasks.

So how can you stop this? One way is to change the fill factor on your indexes. By default, SQL Server tries to completely fill index pages and that can lead to heavy fragmentation as rows get modified and inserted. But if you set your fill factor to a lower percentage than the default 0 (which is equivalent to 100%), SQL will leave some space on the pages and then it may have more room to fit newly inserted or modified rows onto the page. Less page splits = less fragmentation.

But you probably don't want to change the fill factor on all your indexes across the board. Specifically targeting the indexes that are most frequently defragmented will allow you to only make changes where you will reap the most benefit.

And this is what Tim's code helps you do. It will scan the log table that Ola's index maintenance routine creates and will identify indexes that are frequently rebuilt or reorganized. I've taken Tim's script and made some additions to it. I've turned it into a stored procedure that you can run that will send out an email it if finds any indexes that are being defragged often. It will also look at the current fill factor and create a rebuild statement to rebuild the index with a lower fill factor to help reduce fragmentation. I've parameterized most everything. Here's how it works:

You call the routine and specify several parameters:

  • @DatabaseToInvestigate - This is the database you want to scan for frequently defragged indexes
  • @CommandLogDatabase - The database where the CommandLog table from Ola's routine is located
  • @CountThreshold - Indexes that are defragged equal to or more than this number of times are flagged as being excessively defragged
  • @OverRebuildThresholdNewFF - This is the new fill factor recommendation for indexes that are being rebuilt more than @CountThreshold number of time. It is only used when the current fill factor is 0 or 100.
  • @OverReorgThresholdNewFF - This is the new fill factor recommendation for indexes that are being reorganized more than @CountThreshold number of time. It is only used when the current fill factor is 0 or 100.
  • @MailProfileName - the name of the mail profile this routine can use to send out the email notification
  • @MailRecipients - the list of email addressed the notification will be sent to. Must be in the form xxx@xxx.xxx. Multiple address should be separated with a semicolon

The procedure works by scanning the CommandLog table for indexes that are either rebuilt or reorganized more than @CounterThreshold times. If it finds any, it checks the current fill factor of those indexes. If the fill factor is 0 or 100, it will create an ALTER INDEX statement with the new fill factor specified in the @OverRebuildThresholdNewFF or @OverReorgThresholdNewFF parameter. (This is because indexes that are reorganized typically have a lower fragmentation value than those that are rebuilt, so they may not need to have their fill factor adjusted quite so much.) If the current fill factor is not 0 or 100, it will create an ALTER INDEX statement where the fill factor is 10% less than the current value. IT WILL NOT CREATE A STATEMENT WITH A FILL FACTOR LESS THAN 50!

If you have indexes that are being repeatedly defragged and the fill factor is set to 50% or less, you've got a heck of a busy table. That may be perfectly normal and a lower fill factor may be exactly what is needed, but I wanted to raise a red flag in this case and bring this to your attention rather than just provide some code that might get run without being looked at. To that end, if the procedure does find this case, it will add some additional text to the email bringing this to your attention and will list the tables and indexes involved.

One very important note on running this procedure: You cannot just mindlessly plug values into these parameters and run the code whenever you want. You need to take into account how often you perform index maintenance and how long you retain records in the CommandLog table. These two factors will determine how you pick your @CountThreshold value and how often you run this routine.

For example, suppose you perform index maintenance each weekend and you retain 30 days worth of log records. In this case, you'd probably choose @CountThreshold to be 3 or 4 and you'd run this procedure once every 30 days. Because you are only retaining 30 days worth of records in the log table, the maximum possible amount of log entries an index could have would be 4. (Typically. You may get 5 depending on how your run schedule coincides with your log purging schedule in months that have 5 weekends, such as November, 2013)).  You only want to run the procedure again after all your logs have had a chance to roll out of the table since the last time this procedure was run. Why is this?

Let's look at what would happen if you defrag weekly, keep 30 days of log records and you run this routine daily. The first time you run it, it may tell you to reduce the fill factor of some indexes. You do that. Then the next day, the routine runs again. But your defrag routine has not run again and you still have all those old log records in your log table. This routine doesn't know you changed the fill factor yesterday, so it's going to recommend you reduce the fill factor by another 10 percent over what you reduced it to yesterday! (Remember, the routine will look at the current fill factor and make suggestions based on that.)

So your @CountThreshold and frequency of running this monitoring routine depend on other factors. Suppose you defrag indexes weekly and you retain 60 days of log records. In this case, you might want to pick @CountThreshold to be 6 or 7 and you would run this monitoring routine every 60 days. Likewise, if you perform index maintenance nightly and you retain 30 days of log records, you might set @CountThreshold to be somewhere between 20 and 30 (depending on how often you want to define "frequently")  and you would run this routine every 30 days.

Here is a screenshot of the email the routine sends out:

Click to embiggen

Click to embiggen

The routine defaults to making commands to rebuild the index with SORT_IN_TEMPDB = ON and ONLINE = ON. The command will fail for XML and spatial indexes because they cannot be rebuilt online. Also, online rebuilds are an Enterprise only feature, so if you are not running Enterprise edition, you'll want to modify this to be off. The commands it prints out are meant to help a DBA, not replace one. They should be evaluated for appropriateness before being run. (This is why I wrote the routine to just print the statements instead of actually executing the command.)

The code for this procedure is below. As always, give any code you get from the internet a good once-over before running it. Let me know if you have any suggestions for improvements. Or if you know of a place to get cheap unicorn food.

CREATE PROCEDURE [dbo].[up_IndexDefragFrequencyCheck]
       (
        @DatabaseToInvestigate VARCHAR(100) = NULL
       ,@CommandLogDatabase VARCHAR(100) = NULL
       ,@CountThreshold TINYINT = 4
       ,@OverRebuildThresholdNewFF TINYINT = 70
       ,@OverReorgThresholdNewFF TINYINT = 80
       ,@MailProfileName SYSNAME
       ,@MailRecipients VARCHAR(MAX) = NULL
	   )
AS /*

	This procedure scans the CommandLog table of Ola Hallengren's index defrag script
		(http://ola.hallengren.com/) and identifies indexes that are being rebuilt or
		reorganized regularly. The original code comes courtesy of Tim Ford, who blogged
		this at http://thesqlagentman.com/2013/10/whats-the-frequency-kenneth/. This routine
		expands his work. The procedure is meant to be run on a regular basis and will send
		an email when it finds indexes being defragged more than a specified number of times.
		It will also suggest rebuilding the index with a new fill factor to reduce the number
		of rebuilds or reorgs. The new fill factor recommended is one you specify, if the current
		fill factor of the index is 0 or 100%, otherwise it is 10% less than the current
		fill factor. It will not recommend anything less than 50%.

		IMPORTANT!!! This routine looks at the log table and the _current_ fill factor setting
		of the indexes. Therefore, in order to get appropriate fill factor recommendations,
		this routine should NOT be run more often than the log table is cleared. FOR EXAMPLE,
		IF YOU KEEP 30 DAYS WORTH OF LOG RECORDS IN YOUR TABLE, THIS ROUTINE SHOULD NOT BE RUN
		MORE OFTEN THAN EVERY 30 DAYS. This is because if you change the fill factor of an index
		it may result in stopping rebuilds, but the log table will still have the records of the
		prior rebuilds, which may trigger another recommendation to reduce the fill factor, which
		would then be 10% less than what you just changed it to.

		Do not blindly run the commands in the email notification! They are meant to make it easier
		for a DBA to change the fill factor, but they should be evaluated first to ensure they
		are appropriate for your situation.

		Input parameters:

		@DatabaseToInvestigate - The database to scan for excessive defrags
		@CommandLogDatabase - Database holding the CommandLog table of Ola's routine
		@CountThreshold - indexes being defragged equal to or greater this number of times
							are flagged as being excessively defragged
		@OverRebuildThresholdNewFF - Initial starting fill factor suggestion for indexes that
							are being rebuilt more than @CountThreshold times. Only used when
							current fill factor is 0 or 100
		@OverReorgThresholdNewFF - Initial starting fill factor suggestion for indexes that
							are being reorganized more than @CountThreshold times. Only used when
							current fill factor is 0 or 100
		@MailProfileName - name of mail profile the routine can use to send email
		@MailRecipients - list of email addresses the notification will be sent to. Must be of
							the form xxx@xxx.xxx  Multiple addresses should be separated by a ;

		Shaun J. Stuart
		shaunjstuart.com

		v1.0 - 10/8/13
		v1.1 - 10/13/13 - SJS - Added support for case where current fill factor between 100 and the
								min thresholds passed in.

*/

       DECLARE @SQLstr VARCHAR(2000)
       DECLARE @EmailBody VARCHAR(MAX)
       DECLARE @CRLF CHAR(2)
       DECLARE @EmailSubject VARCHAR(200)

       SET @CRLF = CHAR(13) + CHAR(10)

/* Data validation */

       IF @DatabaseToInvestigate IS NULL
          OR @DatabaseToInvestigate = ''
          BEGIN
                SELECT  'A database to investigate must be specified.'
                RETURN
          END

       IF @CommandLogDatabase IS NULL
          OR @CommandLogDatabase = ''
          BEGIN
                SELECT  'A database holding the CommandLog table must be specified.'
                RETURN
          END

       IF @MailRecipients IS NULL
          OR @MailRecipients = ''
          BEGIN
                SELECT  'At least one email recipient must be specified.'
                RETURN
          END

       IF @MailRecipients NOT LIKE '%@%.%'
          BEGIN
                SELECT  'Email recipient not valid address format.'
                RETURN
          END

--============================================================================
-- INDEX REBUILD COUNT
--============================================================================

       IF EXISTS ( SELECT   name
                   FROM     tempdb.sys.objects
                   WHERE    name = '##Recommendations' )
          BEGIN
                DROP TABLE ##Recommendations
          END

       IF EXISTS ( SELECT   name
                   FROM     tempdb.sys.objects
                   WHERE    name = '##Index_History' )
          BEGIN
                DROP TABLE ##Index_History
          END

       IF EXISTS ( SELECT   name
                   FROM     tempdb.sys.objects
                   WHERE    name = '##Indexes' )
          BEGIN
                DROP TABLE ##Indexes
          END

       CREATE TABLE ##Recommendations
              (
               CommandLine VARCHAR(MAX)
              );

       CREATE TABLE ##Index_History
              (
               the_database SYSNAME
              ,the_schema SYSNAME
              ,the_object SYSNAME
              ,the_index SYSNAME
              ,index_type VARCHAR(13)
              ,fill_factor TINYINT
              ,rebuild_count INT NULL
              ,reorg_count INT NULL
              );

       CREATE TABLE ##Indexes
              (
               the_schema SYSNAME
              ,the_object SYSNAME
              ,the_index SYSNAME
              ,fill_factor TINYINT
              );

       SET @SQLstr = 'INSERT  INTO ##Indexes
        (the_schema
        ,the_object
        ,the_index
        ,fill_factor)
        SELECT  OBJECT_SCHEMA_NAME(SO.object_id, DB_ID('''
           + @DatabaseToInvestigate + ''')) AS the_schema
               ,SO.name AS the_object
               ,SI.name AS the_index
               ,SI.fill_factor
        FROM    ' + @DatabaseToInvestigate + '.sys.objects SO
                INNER JOIN ' + @DatabaseToInvestigate
           + '.sys.indexes SI ON SO.object_id = SI.object_id
        WHERE   SI.index_id > 0;'
		--PRINT @SQLstr
       EXEC (@SQLstr)

       SET @SQLstr = 'INSERT  INTO ##Index_History
        (the_database
        ,the_schema
        ,the_object
        ,the_index
        ,index_type
        ,fill_factor
        ,rebuild_count
        ,reorg_count)
        SELECT  C.DatabaseName
               ,C.SchemaName
               ,C.ObjectName
               ,C.IndexName
               ,CASE C.IndexType
                  WHEN 1 THEN ''Clustered''
                  ELSE ''Non-Clustered''
                END AS IndexType
               ,IX.fill_factor
               ,COUNT(C.ID) AS rebuild_count
               ,0 AS reorg_count
        FROM    ' + @CommandLogDatabase + '.dbo.CommandLog C
                LEFT JOIN ##Indexes IX ON C.SchemaName = IX.the_schema
                                          AND C.ObjectName = IX.the_object
                                          AND C.IndexName = IX.the_index
        WHERE   C.CommandType = ''ALTER_INDEX''
                AND C.ObjectType = ''U''
                AND C.Command LIKE ''%REBUILD%''
                AND C.DatabaseName = ''' + @DatabaseToInvestigate + '''
        GROUP BY C.DatabaseName
               ,C.SchemaName
               ,C.ObjectName
               ,C.IndexName
               ,IndexType
               ,IX.fill_factor;'
		--PRINT @SQLstr
       EXEC (@SQLstr)
--============================================================================
-- INDEX REORGANIZE (ONLY) COUNT
--============================================================================
       SET @SQLstr = 'INSERT  INTO ##Index_History
        (the_database
        ,the_schema
        ,the_object
        ,the_index
        ,index_type
        ,fill_factor
        ,rebuild_count
        ,reorg_count)
        SELECT  C.DatabaseName
               ,C.SchemaName
               ,C.ObjectName
               ,C.IndexName
               ,CASE C.IndexType
                  WHEN 1 THEN ''Clustered''
                  ELSE ''Non-Clustered''
                END AS IndexType
               ,IX.fill_factor
               ,0 AS rebuild_count
               ,COUNT(C.ID) AS reorg__count
        FROM    ' + @CommandLogDatabase
           + '.dbo.CommandLog C
                LEFT JOIN ##Indexes IX ON C.SchemaName = IX.the_schema
                                          AND C.ObjectName = IX.the_object
                                          AND C.IndexName = IX.the_index
                LEFT JOIN ##Index_History IH ON C.DatabaseName = IH.the_database
                                                AND C.SchemaName = IH.the_schema
                                                AND C.ObjectName = IH.the_object
                                                AND C.IndexName = IH.the_index
        WHERE   C.CommandType = ''ALTER_INDEX''
                AND C.ObjectType = ''U''
                AND C.Command LIKE ''%REORGANIZE%''
                AND C.DatabaseName = ''' + @DatabaseToInvestigate + '''
                AND IH.the_database IS NULL
        GROUP BY C.DatabaseName
               ,C.SchemaName
               ,C.ObjectName
               ,C.IndexName
               ,IndexType
               ,IX.fill_factor;'
		--PRINT @SQLstr
       EXEC (@SQLstr)
--========================================================
-- ACCOUNT FOR INDEXES BOTH REBUILT AND REORGANIZED
--========================================================
       SET @SQLstr = 'UPDATE  ##Index_History
					  SET     reorg_count = C2.reorganize_count
					  FROM    ##Index_History IH
					  INNER JOIN (SELECT  C.DatabaseName
						     ,C.SchemaName
							   ,C.ObjectName
							,C.IndexName
                           ,	COUNT(C.ID) AS reorganize_count
                      FROM    ' + @CommandLogDatabase + '.dbo.CommandLog C
                      WHERE   C.CommandType = ''ALTER_INDEX''
                              AND C.ObjectType = ''U''
                              AND C.Command LIKE ''%REORGANIZE%''
                              AND C.DatabaseName = ''' + @DatabaseToInvestigate
           + '''
			          GROUP BY C.DatabaseName
                            ,C.SchemaName
                            ,C.ObjectName
                            ,C.IndexName
                    ) C2 ON IH.the_database = C2.DatabaseName
                            AND IH.the_schema = C2.SchemaName
                            AND IH.the_object = C2.ObjectName
                            AND IH.the_index = C2.IndexName
					  WHERE   IH.rebuild_count > 0'
--print @SQLstr
       EXEC (@SQLstr)
--============================================================================
-- RETURN THE RESULTS
--============================================================================

       SELECT   the_database
               ,the_schema
               ,the_object
               ,the_index
               ,index_type
               ,ISNULL(CONVERT(VARCHAR(20), fill_factor), 'No longer exists') AS fill_factor
               ,rebuild_count
               ,reorg_count
       FROM     ##Index_History
       WHERE    rebuild_count >= @CountThreshold
                OR reorg_count >= @CountThreshold
       ORDER BY the_database
               ,rebuild_count DESC
               ,reorg_count DESC
               ,the_object
               ,the_index

       INSERT   INTO ##Recommendations
                (CommandLine)
                SELECT  'USE [' + the_database + ']; ' + @CRLF
                        + 'ALTER INDEX [' + the_index + '] ON [' + the_schema
                        + '].[' + the_object + '] ' + @CRLF
                        + 'REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, '
                        + 'STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, ONLINE = ON, '
                        + @CRLF
                        + 'ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = '
                        + CASE WHEN fill_factor = '0'
                               THEN CAST(@OverRebuildThresholdNewFF AS VARCHAR(3))
                               WHEN fill_factor = '100'
                               THEN CAST(@OverRebuildThresholdNewFF AS VARCHAR(3))
                               WHEN fill_factor <= @OverRebuildThresholdNewFF                                THEN CAST(@OverRebuildThresholdNewFF - 10 AS VARCHAR(3))                                
							   WHEN (fill_factor > @OverRebuildThresholdNewFF AND 
								fill_factor <100)
                               THEN CAST(@OverRebuildThresholdNewFF AS VARCHAR(3))
							  ELSE NULL /* unknown case */                           END + '); '  + @CRLF AS RebuildCommand                 FROM    ##Index_History                 WHERE   rebuild_count >= @CountThreshold
                        AND (fill_factor = 0
                             OR fill_factor >= 60)
                UNION ALL
                SELECT  'USE [' + the_database + ']; ' + @CRLF
                        + 'ALTER INDEX [' + the_index + '] ON [' + the_schema
                        + '].[' + the_object + '] ' + @CRLF
                        + 'REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, '
                        + 'STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, ONLINE = ON, '
                        + @CRLF
                        + 'ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = '
                        + CASE WHEN fill_factor = '0'
                               THEN CAST(@OverReorgThresholdNewFF AS VARCHAR(3))
                               WHEN fill_factor = '100'
                               THEN CAST(@OverReorgThresholdNewFF AS VARCHAR(3))
                               WHEN fill_factor <= @OverReorgThresholdNewFF                                THEN CAST(@OverReorgThresholdNewFF - 10 AS VARCHAR(3))                                
							   WHEN (fill_factor > @OverReorgThresholdNewFF AND 
								fill_factor <100)
                               THEN CAST(@OverReorgThresholdNewFF AS VARCHAR(3))
							  ELSE NULL /* unknown case */                           END + '); ' + @CRLF AS RebuildCommand                 FROM    ##Index_History                 WHERE   reorg_count >= @CountThreshold
                        AND (fill_factor = 0
                             OR fill_factor >= 60)

       IF EXISTS ( SELECT   1
                   FROM     ##Recommendations )
          BEGIN
                SET @EmailBody = @CRLF
                    + 'Analysis of the index defrag log table has been performed and it appears '
                    + 'there are indexes that are being repeatedly defragged. Repeated defragging may '
                    + 'indicate the indexes need a lower fill factor to reduce page splits. Repeated '
                    + 'defragging also wastes resources and generates large amounts of potentially '
                    + 'unnecessary transaction log entries. The parameters used to generate this report '
                    + 'are: ' + @CRLF + @CRLF + 'Server: ' + @@SERVERNAME
                    + @CRLF + 'Database: ' + @DatabaseToInvestigate + @CRLF
                    + 'Min number of rebuilds / reorgs flagged as excessive: '
                    + CAST(@CountThreshold AS VARCHAR(3)) + '. ' + @CRLF
                    + 'Starting suggested fill factor for excessive rebuilds: '
                    + CAST(@OverRebuildThresholdNewFF AS VARCHAR(3)) + '. '
                    + @CRLF
                    + 'Starting suggested fill factor for excessive reorgs: '
                    + CAST(@OverReorgThresholdNewFF AS VARCHAR(3)) + '. '
                    + @CRLF + @CRLF + @CRLF
                    + 'Below are the suggested commands to change the fill factor on the indexes in question. '
                    + 'These commands should be carefully evaluated before being run to ensure they are '
                    + 'appropriate for the situation! In particular, if this routine is run more frequently '
                    + 'than the defrag log table is cleared, it may result in inappropriate fill factor '
                    + 'recommendations.' + @CRLF + @CRLF + @CRLF

                IF EXISTS ( SELECT  1
                            FROM    ##Index_History
                            WHERE   (rebuild_count >= @CountThreshold
                                     OR reorg_count >= @CountThreshold)
                                    AND fill_factor <= 50 									AND fill_factor > 0)
                   BEGIN
                         SET @EmailBody = @EmailBody + @CRLF + @CRLF
                             + 'Note: Some indexes were found that are being defragged regularly and have a fill factor '
                             + 'setting of 50 or less. Reducing the fill factor further is not generally recommended '
                             + 'and further investigation is warranted.'
                             + @CRLF + @CRLF

                         INSERT INTO ##Recommendations
                                (CommandLine)
                                SELECT  '/* Database [' + the_database
                                        + '], index [' + the_index + ']'
                                        + ' on [' + the_schema + '].['
                                        + the_object + '] '
                                        + 'is being repeatedly rebuilt and its fill factor '
                                        + 'is already set to 50 or less. This likely is worth a deeper '
                                        + 'investigation. */' AS RebuildCommand
                                FROM    ##Index_History
                                WHERE   rebuild_count >= @CountThreshold
                                        AND fill_factor <= 50 										AND fill_factor > 0
                                UNION ALL
                                SELECT  '/* Database [' + the_database
                                        + '], index [' + the_index + ']'
                                        + ' on [' + the_schema + '].['
                                        + the_object + '] '
                                        + 'is being repeatedly reorganized and its fill factor '
                                        + 'is already set to 50 or less. This likely is worth a deeper '
                                        + 'investigation. */' AS RebuildCommand
                                FROM    ##Index_History
                                WHERE   reorg_count >= @CountThreshold
                                        AND fill_factor <= 50 										AND fill_factor > 0

                   END
                SET @EmailSubject = 'Excessive Index Defragmenting found on '
                    + @@Servername

                EXEC msdb.dbo.sp_send_dbmail
                    @profile_name = @MailProfileName
                   ,@recipients = @MailRecipients
                   ,@subject = @EmailSubject
                   ,@body = @EmailBody
                   ,@query = 'SELECT * FROM ##Recommendations'
                   ,@query_result_width = 32767
                   ,@query_no_truncate = 1

          END

--============================================================================
-- CLEANUP THE MESS
--============================================================================

IF EXISTS ( SELECT  name
            FROM    tempdb.sys.objects
            WHERE   name = '##Index_History' )
   BEGIN
         DROP TABLE ##Index_History
   END

IF EXISTS ( SELECT  name
            FROM    tempdb.sys.objects
            WHERE   name = '##Indexes' )
   BEGIN
         DROP TABLE ##Indexes
   END

IF EXISTS ( SELECT  name
            FROM    tempdb.sys.objects
            WHERE   name = '##Recommendations' )
   BEGIN
         DROP TABLE ##Recommendations
   END

Share