Reorganize and Rebuild Indexes in SQL Server
Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server’s performance. Reorganizing and rebuilding indexes is a quick way to optimize your databases’ performance.
We periodically (daily, weekly, or monthly) perform database reorganization on all the indexes on all the tables in our databases and those of clients whom subscribe to our support and maintenance plans.
From time to time we run into folks who either reorganize OR rebuild indexes at any given time. Their reasoning is that indexes should be rebuilt when they are over 30% fragmented and they should be reorganized when they are between 10% and 30% fragmented. Also, running both every time could result in a waste of system resources. We agree, however, we have resolved this problem by creating a script (below) that reorganizes and rebuilds indexes only if those conditions specified are met – in one single shot.
Finally, you might be wandering why we do not use SQL Server’s DBCC DBREINDEX command instead of using a custom script. The reason for this is because DBCC DBREINDEX will not automatically rebuild all of the indexes on all the tables in a database; it can only work on one table at a time. But if you run the following script, you can index all the tables AND in all the databases within the running instance:
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; a DECLARE @partitions BIGINT; DECLARE @frag FLOAT; DECLARE @command NVARCHAR(4000); DECLARE @dbid SMALLINT; -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function -- and convert object and index IDs to names. SET @dbid = Db_id(); SELECT [object_id] AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag, page_count INTO #work_to_do FROM sys.Dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, N'LIMITED') WHERE avg_fragmentation_in_percent &gt; 10.0 -- Allow limited fragmentation AND index_id &gt; 0 -- Ignore heaps AND page_count &gt; 25; -- Ignore small tables -- Declare the cursor for the list of partitions to be processed. DECLARE partitions CURSOR FOR SELECT objectid, indexid, partitionnum, frag 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 &lt; 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 &lt; 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; IF @frag &gt;= 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; IF @partitioncount &gt; 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
Now you can wrap this script within a SQL Server Maintenance Plan and have it run automatically at your desired time interval.