Reorganize and Rebuild Indexes in SQL Server

Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server’s performance.

Time to clean house

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:

[code lang=”sql”]
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 > 10.0 — Allow limited fragmentation
AND index_id > 0 — Ignore heaps
AND page_count > 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 < 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
[/code]

Now you can wrap this script within a SQL Server Maintenance Plan and have it run automatically at your desired time interval.