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
- Rebuild: fragmentation > 30%
- 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.