Taming the Log-Eating Defrag Beast

SQL Server Standard Edition can be a beautiful thing. Since failover clustering, 64-bit support and the corresponding big memory became available with Standard Edition in 2005, it can handle most SQL workloads demanded by a small or medium business. Unless you are running a pretty huge system that truly requires Enterprise functionality like table partitioning or async database mirroring, it's often perfectly safe to run with Standard x64 on a two socket, multicore machine with enough RAM, and save a huge amount of license cost. Most of my work has been in the small/medium business space, and to be honest I've seen more cases where people shell out for Enterprise and didn't really need it than cases where people have stretched Standard Edition past its limits (with some exceptions). The most common blunder is paying to run Enterprise Edition on a storage system that's too slow - it can be far better to run Standard on adequate hardware than Enterprise on too few disks - and cheaper, to boot. But I digress.

There is a pain point with running Standard Edition systems that you want to be available near 24 x 7: index maintenance. Offline index rebuild is a blocking operation and can interfere with your applications even in the wee hours. Online index rebuild is an Enterprise Edition feature, and that's costly. Likewise, parallelism for index rebuild is Enterprise only, and index rebuild on Standard Edition can only use one processor core - so it can be much slower. See http://msdn.microsoft.com/en-us/library/cc645993.aspx for a summary of Standard's limitations. Basically you can't rebuild indexes on Standard Edition and keep your system fully online.

It is possible to defrag indexes (and manually update statistics) instead, and do so without blocking. But there's a challenge: index defrag (now ALTER INDEX ... REORGANIZE) can produce a huge amount of transaction log, filling LDF files and/or whole log disks, so you need to pull a bit of a stunt to really make that work, or else your pager is sure to buzz in the middle of the night with a full disk, or worse, and nobody likes that!

Here's what I have done with decent success to make index defrag on Standard Edition really work:

1. Make your usual Transaction Log Backup SQL Agent job.
2. Make one schedule for that job on a "normal" interval like every 15 minutes. Enable this schedule.
3. Make a second, special schedule, for the same log backup job, that runs every minute. Disable this second schedule.
4. Name the two schedules carefully, such that you can enable and disable them by name, programmatically. I use the names "<mydatabase> Log Backup Schedule" and "<mydatabase> Defrag Log Backup Schedule"
5. Create a script, like the one that follows, to defrag indexes that will enable the one-minute-interval log backup while doing its work, then disable it afterward. As an added precaution, have the script check how full the transaction log file is before proceeding.
6. Test very carefully: the server where you run this must have enough disk space for log file(s) to be able to handle a little over a minute of defrag activity before the second job schedule comes on and starts taking log backups.
7. If your testing works, then schedule this process more frequently than with index rebuilds (perhaps nightly). Why? Because with more frequent runs, there's less defrag work to do each time, and the amount of log generated is more likely to be sane. If you have a long interval between defrag runs, and your indexes become severely fragmented, then the next run has more work to do and generates more log records.

Disclaimer: this script has worked well for me, but it's somewhat complicated, and if implemented incorrectly will cause you pain and suffering. Please carefully read and understand it, and implement only after careful testing. 

Selective Defrag Script

SET QUOTED_IDENTIFIER ON ;
SET ANSI_NULLS ON ;
SET NOCOUNT ON ;
 
-- Maximum duration in minutes, past which 
-- new rebuilds will not start:
DECLARE @maxDur int ;
SET @maxdur = 60 * 3 ;
 
-- Threshold for fragmentation in percent, above 
-- which index will defrag:
DECLARE @fragLimit int ;
SET @fragLimit = 20 ;
 
-- Maximum percent used for log files when starting a defrag:
DECLARE @logLimit int ;
SET @logLimit = 25 ;
 
-- Gather fragmentation stats for fragged indexes:
SELECT  frag.[object_id] oid,
        schema_name(o.schema_id) [schema],
        o.name [table],
        i.name [index],
        i.index_id indid,
        ps.function_id partitionScheme,
        frag.partition_number [partition],
        frag.page_count,
        frag.avg_fragmentation_in_percent,
        'ALTER INDEX ' + quotename(i.name) 
        + ' ON ' + quotename(schema_name(o.schema_id)) + '.'
        + quotename( o.name ) + ' REORGANIZE'
        + CASE WHEN ps.function_id IS NOT NULL
               THEN ' PARTITION = '
                    + CAST(frag.partition_number AS varchar(100))
               ELSE ''
          END + ';' + CHAR(13) AS rebuildCmd
INTO    #fraggedIndexes
FROM    sys.dm_db_index_physical_stats(DB_ID(), DEFAULT, DEFAULT, DEFAULT,
                                       DEFAULT) frag
        INNER JOIN sys.objects AS o ON o.object_id = frag.object_id
        INNER JOIN sys.indexes AS i ON i.object_id = frag.object_id
                                       AND i.index_id = frag.index_id
        LEFT JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
WHERE   frag.avg_fragmentation_in_percent > @fragLimit
        AND i.[type] IN ( 1, 2 ) -- clustered or nonclustered, not heap or XML
        AND page_count > 100 ;
 
DECLARE @rebuildCmd varchar(max) ;
SET @rebuildCmd = '' ;
 
DECLARE @startTime datetime ;
SET @startTime = GETDATE() ;
 
DECLARE @logspaceused float ;
SET @logspaceused = 0 ;
 
DECLARE @logBackupSchedName varchar(128);
SET @logBackupSchedName = db_name() + ' Defrag Log Backup Schedule';
 
CREATE TABLE #logspace (
     [Database Name] varchar(128),
     [Log Size (MB)] float,
     [Log Space Used (%)] float,
     [Status] int
    ) ;
 
-- Check how much of the transaction log is in use (prevent filling the log)
 
INSERT  #logspace
        EXEC ( 'DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS;' ) ;
 
SELECT  @logspaceused = [Log Space Used (%)]
FROM    #logspace
WHERE   [Database Name] = DB_NAME() ;
 
DELETE  #logspace ;
 
-- Work on the queue of indexes until the allowed time is exceeded
-- or the transaction log file is too full:
 
WHILE EXISTS ( SELECT   1
               FROM     #fraggedIndexes )
    AND @logspaceused < @logLimit
    AND DATEDIFF(minute, @startTime, GETDATE()) < @maxDur 
    BEGIN
 
        -- Enable frequent log backup schedule 
        -- while doing index maintenance
 
        EXEC msdb.dbo.sp_update_schedule 
            @name = @logBackupSchedName,
            @enabled = 1 ;
 
        SELECT TOP 1
                @rebuildCmd = rebuildCmd
        FROM    #fraggedIndexes
        ORDER BY page_count * avg_fragmentation_in_percent DESC ;
 
        PRINT GETDATE() ;
        PRINT @rebuildCmd ;
 
        EXEC ( @rebuildCmd ) ;
 
        DELETE  FROM #fraggedIndexes
        WHERE   rebuildCmd = @rebuildCmd ;
 
        INSERT  #logspace
                EXEC ( 'DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS;' ) ;
 
        SELECT  @logspaceused = [Log Space Used (%)]
        FROM    #logspace
        WHERE   [Database Name] = DB_NAME() ;
 
        DELETE  #logspace ;
    END ;
 
-- Disable frequent log backup schedule after index maintenance
 
EXEC msdb.dbo.sp_update_schedule 
    @name = @logBackupSchedName,
    @enabled = 0 ;
 
DROP TABLE #logspace ;
DROP TABLE #fraggedIndexes ;