Cleveland DBA

SQL Server administration in Cleveland, OH

The ever-shrinking maintenance window

As I’ve stated before, databases aren’t getting any smaller. Business and legal requirements are mandating that we gather and retain more and more data that, in the past, we might have purged from time to time. It’s a simple fact that database indexes must be maintained, statistics updated, and databases backed up; and the bigger our databases get, the more important basic maintenance becomes. But in these days of 24/7 availability, what is getting smaller is our maintenance window. Some of us are lucky to get a few hours every week or month for maintenance. Others don’t even get that.

I have a OLTP database I support that’s around 300GB. Certainly not huge compared to your average data warehouse, but big enough that index maintenance was becoming cumbersome. When we first migrated this database to SQL Server 2000 several years ago, it was less than 100GB and your out-of-the-box Maintenance Plan tasks were fine for index and statistics maintenance. Maintenance 1.0, if you will.

As the database grew and our maintenance window shrank, however, this solution started taking too much time, so I switched to a more fine-tuned, TSQL approach that you’re probably familiar with already. Basically, I would focus on indexes with a fragmentation higher than 5%, of those, I would rebuild indexes more than 30% fragmented and reindex those less than 30% fragmented. This was Maintenance 2.0.

Well, that was certainly better, but I seemed to be rebuilding the same indexes every time. Obviously the fill-factor on those indexes was set too high, causing page splits (and thus fragmentation) to occur during inserts/updates. So I added logic to drop the existing fillfactor by 5% when the index was rebuilt. I monitor the fillfactor on my indexes periodically to make sure nothing is getting too low. Maintenance 2.1.

The reindexing portion of Maintenance 2.0 and 2.1 takes almost 3 hours to run. I’d like to see that cut down, but I don’t know how I’d be able to on our current system. However, I still wanted to address updating statistics during the maintenance window, and running update stats for the entire database with a 30% sampling was taking over 5 hours. Add to this the fact that I was looking at switching from nightly full backups to a weekly full backup with nightly differentials. I wanted to be able to run the index maintenance, update statistics, and take the full backup all within our weekly maintenance window.

In prior Maintenance versions, I was updating statistics on all of the tables in the database. That was something I could do on a monthly basis, but on a weekly basis it felt like overkill. There were some tables, and some large tables at that, that simply aren’t used. And more importantly, that aren’t updated. That was the key: why update statistics on a table that hasn’t changed since the last time you updated stats? So, in my most recent version, I’ve added logic to update statistics on those indexes that have been updated since the last STATS_DATE. Maintenance 2.2. Or maybe it should be 3.0? Whatever. Here it is. And it ran in just over 3 hours.

USE myDatabase;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @fillfactor int;
DECLARE @command varchar(8000);
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
    DROP TABLE work_to_do;
---- conditionally select from the function, converting object and index IDs to names.
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 5.0 AND index_id > 0
and fragment_count > 1000;
-- Declare the cursor for the list of partitions to be processed.

DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
   FROM partitions
   INTO @objectid, @indexid, @partitionnum, @frag;

        SELECT @objectname =, @schemaname =
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;

        SELECT @indexname = name, @fillfactor = fill_factor
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;

        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

	IF @fillfactor = 0
		SET @fillfactor = 80
		SET @fillfactor = @fillfactor - 5

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding
IF @frag < 30.0
    SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REORGANIZE';
    IF @partitioncount > 1
        SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
    EXEC (@command);
        SELECT @command = 'UPDATE STATISTICS ' + @schemaname + '.' + @objectname + ' (' +@indexname + ') WITH SAMPLE 30 PERCENT'
    EXEC (@command);

IF @frag >= 30.0
    SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + @objectname + ' REBUILD WITH (PAD_INDEX = ON,  FILLFACTOR = '+ cast(@fillfactor as varchar) +') ';
    IF @partitioncount > 1
        SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
    EXEC (@command);
PRINT 'Executed ' + @command;

FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;


DECLARE stats cursor static for
select distinct t.object_id,, s.index_id,
	sys.tables t
	join sys.dm_db_index_usage_stats s
		on s.object_id = t.object_id
		and s.database_id = DB_ID()
		and user_updates > 0
		and STATS_DATE(s.object_id, s.index_id) < s.last_user_update
	join sys.indexes i
		on t.object_id = i.object_id
		and i.index_id = s.index_id
		and s.index_id > 0;

OPEN stats
FETCH NEXT FROM stats INTO @objectid, @objectname, @indexid, @indexname

		SELECT @schemaname =
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;

        SELECT @command = 'UPDATE STATISTICS ' + @schemaname + '.' + @objectname + ' (' +@indexname + ') WITH SAMPLE 30 PERCENT'
		EXEC (@command);
		PRINT 'Executed ' + @command;
		FETCH NEXT FROM stats INTO @objectid, @objectname, @indexid, @indexname

CLOSE stats;

-- drop the temporary table
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
    DROP TABLE work_to_do;

June 2, 2011 Posted by | Maintenance | , | Leave a comment