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; SET NOCOUNT ON; 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. SELECT 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. PRINT '************FINISHED LOADING TABLE, REBUILDING INDEXES**************'; DECLARE partitions CURSOR FOR SELECT * FROM work_to_do; -- Open the cursor. OPEN partitions; -- Loop through the partitions. FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; WHILE @@FETCH_STATUS = 0 BEGIN; SELECT @objectname = o.name, @schemaname = s.name 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 ELSE SET @fillfactor = @fillfactor - 5 -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding IF @frag < 30.0 BEGIN; 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); END; IF @frag >= 30.0 BEGIN; 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); END; PRINT 'Executed ' + @command; FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; END; -- Close and deallocate the cursor. CLOSE partitions; DEALLOCATE partitions; PRINT '************FINISHED REINDEXING, UPDATING STATS ON REMAINING TABLES**************'; DECLARE stats cursor static for select distinct t.object_id, t.name, s.index_id, i.name from 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 WHILE @@FETCH_STATUS = 0 BEGIN SELECT @schemaname = s.name 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 END; CLOSE stats; DEALLOCATE stats; -- drop the temporary table IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do') DROP TABLE work_to_do; GO