Index Fragmentation - SQL Server Query Performance Tuning - page 257

My script performs the following actions:

• Walks all databases on the system and identifies indexes on user tables in each database that

meets the fragmentation criteria and saves them in a temporary table

• Based on the level of fragmentation, reorganizes lightly fragmented indexes and rebuilds those

that are highly fragmented

Here's how to analyze and resolve database fragmentation (store this where appropriate on your system; I have a
designated database for enterprise-level scripts):

CREATE PROCEDURE IndexDefrag
AS
DECLARE @DBName NVARCHAR(255),
@TableName NVARCHAR(255),
@SchemaName NVARCHAR(255),
@IndexName NVARCHAR(255),
@PctFrag DECIMAL,
@Defrag NVARCHAR(MAX)
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'#Frag') )
DROP TABLE #Frag;
CREATE TABLE #Frag (
DBName NVARCHAR(255),
TableName NVARCHAR(255),
SchemaName NVARCHAR(255),
IndexName NVARCHAR(255),
AvgFragment DECIMAL)
EXEC sys.sp_MSforeachdb 'INSERT INTO #Frag ( DBName, TableName, SchemaName, IndexName, AvgFragment )
SELECT ''?'' AS DBName ,t.Name AS TableName ,sc.Name AS SchemaName ,i.name AS IndexName ,s.avg_
fragmentation_in_percent FROM ?.sys.dm_db_index_physical_stats(DB_ID(''?''), NULL, NULL,
NULL, ''Sampled'') AS s JOIN ?.sys.indexes i ON s.Object_Id = i.Object_id
AND s.Index_id = i.Index_id JOIN ?.sys.tables t ON i.Object_id = t.Object_Id JOIN ?.sys.schemas sc
ON t.schema_id = sc.SCHEMA_ID

WHERE s.avg_fragmentation_in_percent > 20
AND t.TYPE = ''U''
AND s.page_count > 8
ORDER BY TableName,IndexName';

DECLARE cList CURSOR
FOR
SELECT *
FROM #Frag

OPEN cList;
FETCH NEXT FROM cList
INTO @DBName,@TableName,@SchemaName,@IndexName,@PctFrag;