Intelligent SQL Server REORGANIZE and REBUILD Script

I've been doing a lot of database analysis recently, and fortunately I'm one of those people that enjoys it. I'm primarily a .Net developer, but I've spent many years looking after SQL Server databases, helping out (and even educating) DBAs, writing ETL packages and designing both large and complex databases.

The database in question was in pretty bad shape; missing, unnecessary, and extremely fragmented indexes, missing primary and foreign keys, bad normalisation, and (the main reason I decided to look into it) undesired table locks during ETL operations. It's a 24/7 system, and live lookups are performed around the clock - data is imported early in the morning (when usage is at a minimum), which is when most of errors occur.

Amongst many other pre-emptive actions I've recently performed on the database, I decided to set up regular table index maintenance job to keep queries as quick as possible. Unfortunately the database is SQL Server Standard edition, and with it being a 24/7 system, we had to schedule some downtime to do a REBUILD of the indexes (the vast majority of the indexes on the primary application tables are at 90% or greater fragmentation), but I wanted to try to eliminate, or at least reduce any future downtime.

Some of you will know that an index can be rebuilt (on-line or offline, depending on the version of SQL Server you have installed) or reorganized (which can only be performed on-line). You might also know that you shouldn't just run these commands willy-nilly, and shouldn't rebuild or reorganize at all under certain conditions. This article on MSDN provides a decent overview of when you should perform a rebuild or reorganize operation, but I also found quite a bit of evidence out there that says performing a REBUILD or REORGANIZE against small indexes yields no noticeable performance improvement.

Armed with this information, I set out to find an already written table maintenance script, but failed to find anything that was robust enough for my needs. I wanted to be able to specify a number of parameters that determine which indexes are rebuilt, determine if an index can be rebuilt online or not, and perform optional logging.

The script below has been tested on SQL Server 2008 R2 and SQL Server 2012, and although I'm sure it'll work on previous versions of SQL Server, I would do some testing before running it.


----------------------------------------------------------------------------------------------------
-- Developer:   Chris Sebok
-- Date:        10th May 2013
-- Description: A stored procedure that rebuilds or reorganizes indexes intelligently depending on
--              the SQL Server version.
----------------------------------------------------------------------------------------------------
/*
-- Pre-run server configuration

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
*/

/*
DECLARE @Database                VARCHAR(100)  SET @Database                = 'MyDatabase'
DECLARE @PageCountThreshold      INT           SET @PageCountThreshold      = 1000
DECLARE @ReorganiseThreshold     DECIMAL(5,2)  SET @ReorganiseThreshold     = 5.00
DECLARE @RebuildThreshold        DECIMAL(5,2)  SET @RebuildThreshold        = 30.00
DECLARE @Debug                   BIT           SET @Debug                   = 1
DECLARE @SafeMode                BIT           SET @SafeMode                = 1
DECLARE @DefaultFillFactorIfZero INT           SET @DefaultFillFactorIfZero = 90
DECLARE @LogPath                 VARCHAR(1000) SET @LogPath                 = 'D:\logs\mydatabase\'

EXEC spReindex @Database, @LogPath, @Debug, @SafeMode, @PageCountThreshold, @ReorganiseThreshold, @RebuildThreshold, @DefaultFillFactorIfZero

*/
IF OBJECT_ID('spReindex') IS NOT NULL
    DROP PROC spReindex
GO

CREATE PROC spReindex (
    @Database                VARCHAR(100),           -- Database to reindex
    @LogPath                 VARCHAR(1000),          -- The path to store log files in. Must be prefixed with a back slash.
    @Debug                   BIT            = 1,     -- 0 = Index work is carried out. 1 = Index statements are not executed, but everything is logged as normal (for analysis)
    @SafeMode                BIT            = 1,     -- 0 = Rebuilds indexes regardless of SQL Server version. 1 = Only rebuilds indexes if they can be rebuilt online.
    @PageCountThreshold      INT            = 1000,  -- Number of paged in the index before a rebuild / reorganize occurs
    @ReorganiseThreshold     DECIMAL(5,2)   = 5.00,  -- The lower percentage threshold for a REORGANIZE
    @RebuildThreshold        DECIMAL(5,2)   = 30.00, -- The lower percentage threshold for a REBUILD
    @DefaultFillFactorIfZero INT            = 90     -- The default fillfactor percentage for an index if it's currently set to 0 (100%)
)
AS

    -- Validation
    IF RTRIM(LTRIM(ISNULL(@Database, ''))) = ''
        BEGIN
            PRINT CONVERT(VARCHAR, GETDATE(), 113) + ': ' + '@Database can not be null or empty.'
            RETURN
        END

    IF ISNULL(@ReorganiseThreshold, 0) >= ISNULL(@RebuildThreshold, 0) OR ISNULL(@ReorganiseThreshold, 0) = 0 OR ISNULL(@RebuildThreshold, 0) = 0
        BEGIN
            PRINT CONVERT(VARCHAR, GETDATE(), 113) + ': ' + '@ReorganiseThreshold must be less than @RebuildThreshold, and neither can be null or zero.'
            RETURN
        END

    IF RIGHT(@LogPath, 1) != '\'
        BEGIN
            PRINT CONVERT(VARCHAR, GETDATE(), 113) + ': ' + '@LogPath must end with a backslash (\), and be a valid path.'
            RETURN
        END

    IF DB_ID(@Database) IS NULL
        BEGIN
            PRINT CONVERT(VARCHAR, GETDATE(), 113) + ': ' + '@Database (' + @Database + ') does not exist.'
            RETURN
        END


    -- Sproc variables
    DECLARE @OnlineRebuildEnabled BIT             SET @OnlineRebuildEnabled = CASE WHEN CONVERT(VARCHAR, SERVERPROPERTY('Edition')) LIKE '%Enterprise%' THEN 1 ELSE 0 END
    DECLARE @LogFile              VARCHAR(2000)   SET @LogFile              = @LogPath + CONVERT(VARCHAR, DATEPART(yyyy, GETDATE())) + '_' + RIGHT('00' + CONVERT(VARCHAR, DATEPART(MM, GETDATE())), 2) + '_spReindex.log'
    DECLARE @StartTime            DATETIME
    DECLARE @EndTime              DATETIME
    DECLARE @Difference           INT
    DECLARE @Prefix               VARCHAR(500)
    DECLARE @Instruction          VARCHAR(8000)
    DECLARE @IndexStatement       NVARCHAR(4000)

    -- Cursor variables
    DECLARE @Table VARCHAR(200), @Schema VARCHAR(200)
    DECLARE @IndexName VARCHAR(500), @Fragmentation DECIMAL(5, 2), @Fillfactor INT, @Pages INT, @Type VARCHAR(30)


    -- Manage log directory
    SET @Instruction = 'md ' + @LogPath
    EXEC master..xp_cmdshell @Instruction, 'NO_OUTPUT';


    -- Start cursor operations
    DECLARE cTables CURSOR READ_ONLY FAST_FORWARD FOR
        SELECT
            s.name AS SchemaName,
            t.name AS TableName
        FROM sys.tables            (nolock) t
        INNER JOIN sys.schemas    (nolock) s ON t.schema_id = s.schema_id
        WHERE t.[type] = 'u'

    OPEN cTables

    FETCH NEXT FROM cTables INTO @Schema, @Table

    WHILE @@FETCH_STATUS = 0
        BEGIN
            
            DECLARE cIndexes CURSOR READ_ONLY FAST_FORWARD FOR
                SELECT
                    I.name,
                    ROUND(S.avg_fragmentation_in_percent, 2),
                    i.fill_factor,
                    s.page_count,
                    i.type_desc
                FROM sys.dm_db_index_physical_stats (DB_ID(@Database), OBJECT_ID(@Table), NULL, NULL, NULL) S
                INNER JOIN sys.indexes I (nolock) ON (
                    S.object_id        = I.object_id
                    AND S.index_id    = I.index_id
                )
                WHERE name IS NOT NULL -- Ignore heap indexes

            OPEN cIndexes

            FETCH NEXT FROM cIndexes INTO @IndexName, @Fragmentation, @Fillfactor, @Pages, @Type

            WHILE @@FETCH_STATUS = 0
                BEGIN
                    -- Reset index statement
                    SET @IndexStatement = ''
                    
                    SET @Prefix = @Database + '.' + @Schema + '.' + @Table + '.' + @IndexName

                    SET @Instruction = 'echo ' + CONVERT(VARCHAR, GETDATE(), 113) + ': ' + @Prefix + ':: Index info: Type = ' + @Type + ', No. of Pages = ' + CONVERT(VARCHAR, @Pages) + ', Fragmentation = ' + CONVERT(VARCHAR, @Fragmentation) + '%, Fill Factor = ' + CONVERT(VARCHAR, @Fillfactor) + '%) >> ' + @LogFile
                    EXEC master..xp_cmdshell @Instruction, 'NO_OUTPUT';

                    -- Make sure the index should be rebuilt or reorganized
                    IF @Pages >= @PageCountThreshold AND @Fragmentation >= @ReorganiseThreshold
                        BEGIN
                            -- Rebuild or reorganize?
                            IF @Fragmentation >= @RebuildThreshold
                                BEGIN
                                    -- REBUILD
                                    IF @OnlineRebuildEnabled = 1 OR @SafeMode = 0 OR (@SafeMode = 1 AND @OnlineRebuildEnabled = 1)
                                        BEGIN
                                            SET @IndexStatement = 'ALTER INDEX ' + @IndexName + ' ON ' + @Schema + '.' + @Table + ' REBUILD WITH (' + CASE WHEN @OnlineRebuildEnabled = 1 THEN 'ONLINE = ON, ' ELSE '' END + 'FILLFACTOR = ' + CONVERT(VARCHAR, CASE WHEN @Fillfactor = 0 THEN @DefaultFillFactorIfZero ELSE @Fillfactor END) +');'
                                        END
                                    ELSE
                                        BEGIN
                                            SET @Instruction = 'echo ' + CONVERT(VARCHAR, GETDATE(), 113) + ': ' + @Prefix + '; unable to rebuild index ONLINE, SQL Enterprise required, or server must be taken offline. >> ' + @LogFile
                                            EXEC master..xp_cmdshell @Instruction, 'NO_OUTPUT';
                                            GOTO FetchNext -- Move to next index
                                        END
                                END
                            ELSE
                                BEGIN
                                    -- REORGANIZE
                                    SET @IndexStatement = 'ALTER INDEX ' + @IndexName + ' ON ' + @Schema + '.' + @Table + ' REORGANIZE;'
                                END

                            SET @Instruction = 'echo ' + CONVERT(VARCHAR, GETDATE(), 113) + ': ' + @Prefix + '; Running: ' + @IndexStatement + ' >> ' + @LogFile
                            EXEC master..xp_cmdshell @Instruction, 'NO_OUTPUT';

                            -- Perform reindex and time it
                            SET @StartTime = GETDATE()

                            IF @Debug = 0
                                BEGIN
                                    EXEC sp_executesql @IndexStatement
                                END

                            SET @EndTime = GETDATE()

                            SET @Difference = DATEDIFF(ss, @StartTime, @EndTime)

                            SET @Instruction = 'echo ' + CONVERT(VARCHAR, GETDATE(), 113) + ': ' + @Prefix + '; Finished. Time elapsed ' + CONVERT(VARCHAR, @Difference) + ' seconds (' + CONVERT(VARCHAR, @Difference / 60) + ' minutes) >> ' + @LogFile
                            EXEC master..xp_cmdshell @Instruction, 'NO_OUTPUT';
                        END
                    ELSE
                        BEGIN
                            -- No need to index
                            SET @Instruction = 'echo ' + CONVERT(VARCHAR, GETDATE(), 113) + ': ' + @Prefix + '; Index does not require reorganizing or rebuilding. >> ' + @LogFile
                            EXEC master..xp_cmdshell @Instruction, 'NO_OUTPUT';
                        END
FetchNext:
                    FETCH NEXT FROM cIndexes INTO @IndexName, @Fragmentation, @Fillfactor, @Pages, @Type
                END

            CLOSE cIndexes
            DEALLOCATE cIndexes

            FETCH NEXT FROM cTables INTO @Schema, @Table
        END

    CLOSE cTables
    DEALLOCATE cTables
GO

Popular posts from this blog

TDD and Unit Testing with Moq

In my last post, I mentioned that I've been using TDD and mocking quite a bit this year, and I've fallen in love. It's something I'm very keen on continuing with, as I've now seen that the benefits outweigh the extra time needed to develop your tests. I've written my fair share of stand alone unit and integration tests throughout the years, but until recently I've not used object mocking or stubbing. I've never really appreciated what tests are, and what benefits they give me as a developer, other than they let me write a quick bit of throw-away code that enables me to test the functionality of a class or a method. Only now - 16 years in to my career - do I realise how critical unit tests are to an application, especially in enterprise level systems that have a lot of code reuse and crossover. Not only do well written tests test the functionality of your code base, they also allow you to modify anything with complete confidence that your changes aren...
Read more

Handling uploads with MVC4, JQuery, Plupload and CKEditor

I spent a considerable amount of time trying to get MVC to work nicely with various file uploaders while I was writing  RustyShark , and I have to admit it's been pretty frustrating at times. On the plus side, it's taught me a lot about how Microsoft MVC works behind the scenes, and allowed me to play with quite a few different uploading tools that're out there. My upload page on RustyShark has quite a complex model - each review is related to multiple Formats (PS3, 360, DVD, Blu Ray etc), each review / format combination can have multiple images associated with it, and each image has additional meta data attached, such as a caption and an upload date. The upload form is created using a combination of Editor Templates, JQuery and AJAX. I will be stripping down any examples to their bare bones to make things a little clearer. This article assumes you are familiar with MVC fundamentals such as creating controllers, views and models within Visual Studio 2010 or later, you ...
Read more

Generating a self-signed SSL certificate for my QNAP NAS

I recently bought a new NAS (Network Attached Storage) to deal with some of my remote work requirements. Initially I was going to build a server to do the job from spare parts, but I was concerned about power consumption and of course the amount of time it would take to build, configure and administrate. In the end, I had a little look at a few reviews and I decided on the QNAP 219P+. I can honestly say without a doubt that it's one of the best pieces of kit I've bought for a while. It's very feature rich, (almost) everything seems to work perfectly, the interface is well organised and easy to use, and maybe the most important part - it's fast . I'm having a few issues getting the WebDAV service accepting connections, though I think it's Windows' fault, not the QNAP. It's a little on the pricey side for home users, but I didn't mind paying what I did considering how good it is. One of the things I wanted to do was secure my remote communication w...
Read more