Re indexing database tables in SQL Server

Re indexing database tables in SQL server is a very wide topic. Ideally, we have database maintenance tasks scheduled to run during maintenance window to rebuild indexes along with other maintenance activities. The indexes require rebuild as they get fragmented in time because of DML operations being run on tables.

Query to quickly rebuild an index on a table.

Query 1: Rebuild an index
USE AdventureWorks2014
GO
ALTER INDEX [AK_Address_rowguid] ON Person.Address REBUILD;

Query to quickly reorganize an index on a table

-- Query 2: Reorganize an index
USE AdventureWorks2014
GO
ALTER INDEX [AK_Address_rowguid] ON Person.Address REORGANIZE;

Query to rebuild/reorganize all indexes on a table

--Query 3: Rebuild All indexes of a particular table
DECLARE 
	@dynamic_sql nvarchar(2000),
	@Operation varchar(15),
	@rebuild_options nvarchar(max),
	@execute int
-- specify REBUILD/REORGANIZE
SET @Operation=' REBUILD '
-- specify rebuild options
SET @rebuild_options = ' WITH (FILLFACTOR=90) '
-- print/execute
SET @execute=0

-- get the list of tables to rebuild
DECLARE rebuild_cursor CURSOR FOR
SELECT 'ALTER INDEX [' + ind.name
+ '] ON [' + OBJECT_SCHEMA_NAME(obj.object_id,db_id()) + '].[' +  obj.name + '] ' 
	+ @Operation + @rebuild_options
FROM sys.objects obj join sys.indexes ind 
ON obj.object_id=ind.object_id
WHERE obj.type='U' and ind.index_id>0

OPEN rebuild_cursor
FETCH NEXT FROM rebuild_cursor into @dynamic_sql

WHILE @@FETCH_STATUS=0
BEGIN
	-- Print but not execute
	if(@execute=0)
	PRINT @dynamic_sql

	-- Print and execute
	if(@execute=1)
	BEGIN
	PRINT 'Done.....' + @dynamic_sql
	EXEC(@dynamic_sql)
	END
	FETCH NEXT FROM rebuild_cursor into @dynamic_sql
END
close rebuild_cursor
deallocate rebuild_cursor

The above query declares a cursor to iterate through all indexes in a table and generates the index rebuild statement as shown in first query. The statement generated can be only printed to check the index rebuild queries or can be directly executed based on @executed variable, when 0 the queries are printed and when 1 the index rebuild query is executed. In addition to this, you can specify the index rebuild options to variable @rebuild_options such as fill factor, online etc and can also decide whether to rebuild/reorganize based on @operation variable.

The above query can be scheduled as SQL Agent Job to run during database maintenance window. I would suggest not running it production hours. It will affect overall database performance.

The above query is good however not good enough to decide which index to rebuild and which not. It rebuilds/reorganizes all indexes which in turn will create lot of T-logs and will take lot of time to finish.  The better way is to rebuild/reorganize selected index. This selection is made on general Microsoft guideline as stated below

1. Rebuild: fragmentation > 30%
2. Reorganize: fragmentation > 5% and fragmentation < 30%

The fragmentation corresponds to avg_fragmentation_in _percent column from sys.dm_db_index_physical_stats function. The below query is taken from http://msdn.microsoft.com/en-us/library/ms188917.aspx section D. Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes

-- Query 4: http://msdn.microsoft.com/en-us/library/ms188917.aspx (Section D)
USE [AdventureWorks2014]
GO
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130); 
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000); 
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert 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 > 10.0 AND index_id > 0;

-- 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.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(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 = QUOTENAME(name)
        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;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

The query as mentioned earlier, decides which indexes are to be rebuild/reorganized. It takes care of defragmenting partitions too. It queries sys.dm_db_index_physical_stats to get list of all indexes with fragmentation greater than 10% thus avoiding overhead of rebuilding each and every index. It then iterates through the index list and rebuilds indexes with fragmentation greater than or equal to 30% and reorganizes indexes with fragmentation lesser than 30%.

The query can be scheduled or can be used to quickly rebuild indexes to resolve performance as and when needed.

 
Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook