"Smart" Reindexing (SQL Server 2005, 2008) | Benchmark IT Consulting

Something that still baffles me about SQL Server is that even in SQL Server 2008 there is no maintenance plan option to do reindexing properly. The key word in that sentence is properly. SQL Server gives you an all or nothing approach to indexing. You have 2 options:

1) Rebuild Indexes

2) Reorganize Indexes

(Great article on the differences between the two : http://decipherinfosys.wordpress.com/2008/02/07/index-rebuilding-vs-index-reorganizing-in-sql-server-2005/ )

If you were to do this through the wizard you can choose individual objects and there are plenty of options to customize but where this falls down is that it does not take into account whether or not an index needs maintenance performed on it.

In SQL Server 2005 DMVs (Dynamic Management Views) were introduced that gave instant access to alot of vital information that prior to SQL Server 2005 wasn’t available or required separate process to gather (anyone remember DBCC SHOWCONTIG?).

Let’s talk about the DMV sys.dm_db_index_physical_stats. “Returns size and fragmentation information for the data and indexes of the specified table or view.” So you would think that with this information readily available that the maintenance plan would have an option to do only the indexes that needed maintenance? nope.

Below is a script that I have been working with for SQL Server 2005 and SQL Server 2008. Because SQL Server 2000 did not have DMVs this script will not work (I have a different script for SQL Server 2000 that I’ll post another time)

The long and the short of what this script does is that it will utilize the sys.dm_db_index_physical_stats DMV and dynamically determine if an index requires maintenance and performs the maintenace.

Variables that require attention
@FragThreshold – This integer value is the minimum percentage that an index is fragmented before maintenance is done

@DoOfflineToo – This bit value indicates whether or not indexes that need to be done offline are done at the same time. This is important because if an index needs to be maintained offline this requires significant locks to the object. (Also please be aware that online index operations are only available on Enterprise or Developer Edition)

This script needs to be run in the context of the database that you are looking to maintain. I usually create an SSIS package and wrap this script in a ForEach loop container that cycles through all the databases but for ease of “blogging” we’ll look at it from a more manual approach.

The “sizzle” of the script is when it decides what to do with the index that is currently being looked at.
DECLARE @FragThreshold INT DECLARE @DoOfflineToo bit


---------------------------------------------------------------
--				VARIABLES TO CHANGE							 --
---------------------------------------------------------------
SELECT @FragThreshold = 10
SELECT  @DoOfflineToo = 0
---------------------------------------------------------------
---------------------------------------------------------------
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @partitioncount bigint
DECLARE @schemaname VARCHAR(130)
DECLARE @objectname VARCHAR(130)
DECLARE @indexname VARCHAR(130)
DECLARE @indextype INT
DECLARE @partitionnum bigint
DECLARE @partitions bigint
DECLARE @frag INT
DECLARE @command VARCHAR(4000)
DECLARE @max INT
DECLARE @min INT
DECLARE @DB_ID INT
DECLARE @DB_NAME NVARCHAR(128)
DECLARE @IndexListCount INT
DECLARE @Edition NVARCHAR(100)
DECLARE @LobCount INT
SELECT @LobCount = 0
SELECT @DB_ID = DB_ID()
SELECT @DB_NAME = DB_NAME()
SELECT  @Edition = CONVERT(NVARCHAR(100), SERVERPROPERTY ('edition')) 
CREATE TABLE #BenchmarkIndexList (ID INT IDENTITY(1,1), 
   DBName VARCHAR(100), 
   objectID INT,
   indexID INT,
   IndexType VARCHAR(30),
   frag INT,
   avg_frag_size_pages INT,
   page_count INT,
   partition_number INT)
CREATE CLUSTERED INDEX IX_CL_IndexList ON #BenchmarkIndexList(ID)
INSERT #BenchmarkIndexList (DBName, objectID, indexID, IndexType, frag, 
                   avg_frag_size_pages, page_count, partition_number)
SELECT DB_NAME(database_id) AS DBName, OBJECT_ID, index_id, index_type_desc AS 'IndexType',
       avg_fragmentation_in_percent, avg_fragment_size_in_pages, page_count, partition_number
FROM   sys.dm_db_index_physical_stats (@DB_ID, NULL, NULL, NULL, 'LIMITED')
WHERE  page_count > 10 AND 
       index_id > 0 AND 
       avg_fragmentation_in_percent > @FragThreshold AND
       index_type_desc NOT LIKE '%XML%'
       
SELECT @IndexListCount = COUNT(1) FROM #BenchmarkIndexList
SELECT @max = (SELECT MAX(ID) FROM #BenchmarkIndexList)
SELECT @min = 1
WHILE @min <= @max
BEGIN
   SELECT  @objectid = objectID, @indexid = indexID, @partitionnum = partition_number, @frag = frag
   FROM    #BenchmarkIndexList
   WHERE   ID = @min
   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, @indextype = TYPE
   FROM    sys.indexes
   WHERE   OBJECT_ID = @objectid AND 
           index_id = @indexid;
   SELECT  @partitioncount = COUNT(1)
   FROM    sys.partitions
   WHERE   OBJECT_ID = @objectid AND 
           index_id = @indexid
       
   SELECT  @LobCount = COUNT(1)
   FROM    sys.index_columns A INNER JOIN
           sys.columns B ON A.OBJECT_ID = B.OBJECT_ID AND A.Column_id = B.Column_ID INNER JOIN
           sys.types C ON B.system_type_id = C.user_type_id
   WHERE   A.OBJECT_ID = @objectID AND
           A.index_id = @indexID AND 
           (C.name IN('xml','image','text','ntext') OR
           (C.name IN('varchar','nvarchar','varbinary','nvarbinary') AND 
           B.max_length = -1))
   IF @frag < @FragThreshold
   BEGIN
       SET @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REORGANIZE';
   END
   IF @frag >= @FragThreshold AND @IndexType <> 1 AND @LobCount = 0 AND @Edition = 'Enterprise Edition'
   BEGIN
       SET @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + @objectname +' REBUILD WITH(ONLINE = ON, SORT_IN_TEMPDB = ON)';
   END
   IF @frag >= @FragThreshold AND @DoOfflineToo = 1 AND (@IndexType = 1 OR @LobCount <> 0 OR @Edition <> 'Enterprise Edition')
   BEGIN
       SET @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + @objectname +' REBUILD WITH(ONLINE = OFF, SORT_IN_TEMPDB = ON)';
   END
   
       IF @frag >= @FragThreshold AND @DoOfflineToo = 0 AND (@IndexType = 1 OR @LobCount <> 0 OR @Edition <> 'Enterprise Edition')
   BEGIN
       SET @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REORGANIZE';
   END
   IF @partitioncount > 1
   BEGIN
       SET @command = @command + ' PARTITION =' + CAST(@partitionnum AS VARCHAR(10));
       EXEC (@command)
   END
   ELSE
   BEGIN 
       EXEC (@command)
   END
   
   SET @min = @min+1
END
DROP TABLE #BenchmarkIndexList