StappsPlace.com

I have been trying to perfect the art of reorganizing/rebuilding indexes in sql server here lately. I know that this is no where near perfection but is a step beyond the capabilities of SQL Server Management Studio simply because SSMS does not have the ability to take into account the percent of fragmentation. SSMS reorganizes and/or rebuilds every single index regardless of the fragmentation level, which can be a hard hit resource wise. This can also cause a maintenance plan to take several times longer than if the plan had the ability to respond dynamically. So, this is where it begins…..how to dynamically defrag indexes based on the percent of fragmentation. Transact SQL is the only way.

– 1. Reorganizes All Indexes Initially
– 2. Rebuilds Indexes IF Frag Is Not Cleared By Initial Reorganize
– Ignores Indexes <= 5% Fragmented
– Ignores Indexes With Page Count < 100
– Compatible With SQL Server 2005 And Later

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘IndexesToDefrag’) DROP TABLE IndexesToDefrag;

– Round 1 – Reorganize All Indexes With Frag > 5%

SELECT
si.name as IndexName,
OBJECT_NAME(si.object_id) as TableName,
avg_fragmentation_in_percent as Frag
INTO IndexesToDefrag
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) ips
Join sys.indexes si on ips.object_id = si.object_id
AND ips.index_id = si.index_id
WHERE avg_fragmentation_in_percent > 5
AND index_type_desc <> ‘HEAP’
AND page_count > 100;

DECLARE @Command VARCHAR(100)
DECLARE @Counter INT
DECLARE @IndexName VARCHAR(100)
DECLARE @NumberOfIndexes INT
DECLARE @TableName VARCHAR(100)
DECLARE @Frag INT

SET @NumberOfIndexes = (SELECT COUNT(*) FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) ips
Join sys.indexes si on ips.object_id = si.object_id
AND ips.index_id = si.index_id
WHERE avg_fragmentation_in_percent > 5
AND index_type_desc <> ‘HEAP’
AND page_count > 100)

SET @Counter = 0

DECLARE DefragCursor CURSOR
FOR SELECT * FROM IndexesToDefrag

OPEN DefragCursor

WHILE @Counter < @NumberOfIndexes
BEGIN
FETCH NEXT FROM DefragCursor INTO @IndexName, @TableName, @Frag
SET @Command = ‘ALTER INDEX ‘ + @IndexName + ‘ ON ‘ + @TableName + ‘ REORGANIZE’ EXEC (@Command) PRINT ‘Executed: ‘ + @command
SET @Counter = @Counter + 1
END

CLOSE DefragCursor
DEALLOCATE DefragCursor

DROP TABLE IndexesToDefrag;

GO

– Round 2 – Rebuild Indexes If Reorganize Was Unsuccessful

SELECT
si.name as IndexName,
OBJECT_NAME(si.object_id) as TableName,
avg_fragmentation_in_percent as Frag
INTO IndexesToDefrag
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) ips Join sys.indexes si ON ips.object_id = si.object_id AND ips.index_id = si.index_id
WHERE avg_fragmentation_in_percent > 5
AND index_type_desc <> ‘HEAP’
AND page_count > 100;

DECLARE @Command VARCHAR(100)
DECLARE @Counter INT
DECLARE @IndexName VARCHAR(100)
DECLARE @NumberOfIndexes INT
DECLARE @TableName VARCHAR(100)
DECLARE @Frag INT

SET @NumberOfIndexes = (SELECT COUNT(*) FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) ips
Join sys.indexes si ON ips.object_id = si.object_id
AND ips.index_id = si.index_id
WHERE avg_fragmentation_in_percent > 5
AND index_type_desc <> ‘HEAP’
AND page_count > 100)

SET @Counter = 0

DECLARE DefragCursor CURSOR
FOR SELECT * FROM IndexesToDefrag

OPEN DefragCursor

WHILE @Counter < @NumberOfIndexes
BEGIN
FETCH NEXT FROM DefragCursor INTO @IndexName, @TableName, @Frag
SET @Command = ‘ALTER INDEX ‘ + @IndexName + ‘ ON ‘ + @TableName + ‘ REBUILD’
EXEC (@Command)
PRINT ‘Executed: ‘ + @command
SET @Counter = @Counter + 1
END

CLOSE DefragCursor
DEALLOCATE DefragCursor

DROP TABLE IndexesToDefrag;