Nedanstående skript städar index på en 2005 databas (OBS måste vara i 90-mode på kompabilitet, (dvs SQL2005) Se till att rätt databas är vald innan man startar skriptet, det är generellt och kan köras på alla 2005 databaser. Innan man kör detta kan det vara en poäng att kolla artikeln [kontrollera fragmenteringsgrad på index] {code:sql} SET NOCOUNT ON; DECLARE @objectid int; DECLARE @indexid int; DECLARE @partitioncount bigint; DECLARE @schemaname sysname; DECLARE @objectname sysname; DECLARE @indexname sysname; DECLARE @partitionnum bigint; DECLARE @partitions bigint; DECLARE @frag float; DECLARE @command varchar(8000); -- ensure the temporary table does not exist IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do') DROP TABLE work_to_do; -- conditionally select from the function, converting 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. FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; WHILE @@FETCH_STATUS = 0 BEGIN; SELECT @objectname = o.name, @schemaname = 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 = 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 BEGIN; SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REORGANIZE'; IF @partitioncount > 1 SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum); EXEC (@command); END; IF @frag >= 30.0 BEGIN; SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + @objectname + ' REBUILD'; IF @partitioncount > 1 SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum); EXEC (@command); END; PRINT 'Executed ' + @command; FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; END; -- Close and deallocate the cursor. CLOSE partitions; DEALLOCATE partitions; -- drop the temporary table IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do') DROP TABLE work_to_do; GO {code:sql}

Nedanstående skript lista index fysiska fragmenteringsgrad, mest fragmenterad först Om fragementeringsgrad i procent är mindre än 10% tas den ej med Rekommendationen är att om frag är mellan 10-30% kör en REORGANIZE, om >30% kör REBUILD (kan göras med detta script: [Bygg om alla index i databasen]) OBS på små tabeller (dvs om Page_count är lågt, <10) så kan man få höga procentsiffror på fragmenteringen ändå, men då är fragmentering ändå inget problem {code:sql} SELECT 'objectname' = o.Name , 'indexname' = i.NAME , phys.avg_fragmentation_in_percent , phys.fragment_count , phys.page_count , 'record_count' = (SELECT sum(row_count) FROM sys.dm_db_partition_stats ps WHERE ps.index_id < 2 AND ps.object_id =phys.object_id) , phys.index_type_desc , 'schemaname' = s.NAME , 'partitionnum' = phys.partition_number FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') AS phys INNER JOIN sys.objects AS o ON o.OBJECT_ID=phys.object_id INNER JOIN sys.schemas as s ON s.schema_id = o.SCHEMA_ID INNER JOIN sys.indexes AS i ON i.OBJECT_ID=phys.OBJECT_ID AND i.index_id=phys.index_id WHERE phys.index_id > 0 AND phys.avg_fragmentation_in_percent > 10.0 ORDER BY phys.avg_fragmentation_in_percent desc; {code:sql}