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