SQL SERVER 2008 R2 method of rebuilding indexes _mssql2008

Reference sys.dm_db_index_physical_stats

Check index fragmentation

1.SELECT 2.object_name (object_id) as ObjectName, 3.object_id as Objectid, 4.index_id as IndexID, 5.partition_number as PA Rtitionnum, 6.avg_fragmentation_in_percent as Fra 7.FROM sys.dm_db_index_physical_stats (db_id (), NULL, NULL, NULL, ' LIM
ited ') 8.WHERE avg_fragmentation_in_percent > 10.0 and index_id > 0; 
9.10. Use the sys.dm_db_index_physical_stats in the script to regenerate or rearrange the index (from online Help) 11.
12.SET NOCOUNT on;
13.DECLARE @objectid int;
14.DECLARE @indexid int;
15.DECLARE @partitioncount bigint;
16.DECLARE @schemaname nvarchar (130);
17.DECLARE @objectname nvarchar (130);
18.DECLARE @indexname nvarchar (130);
19.DECLARE @partitionnum bigint;
20.DECLARE @partitions bigint;
21.DECLARE @frag float;
22.DECLARE @command nvarchar (4000); 23.–conditionally Select tables and indexes from the Sys.dm_db_index_physical_stats function 24.–and convert object and
Index IDs to names. 25.SELECT 26.object_id as Objectid, 27.index_id as IndexID, 28.partition_number as Partitionnum, 29.avg_fragmentation_in _perceNT as Frag 30.INTO #work_to_do 31.FROM sys.dm_db_index_physical_stats (db_id (), NULL, NULL, NULL, ' LIMITED ') 32.WHERE avg
_fragmentation_in_percent > 10.0 and index_id > 0;
33.–declare the cursor for the "List of partitions to be processed."
34.DECLARE partitions CURSOR for SELECT * from #work_to_do;
35.–open the cursor.
36.OPEN partitions;
37.–loop through the partitions.
38.WHILE (1=1) 39.BEGIN;
40.FETCH NEXT 41.FROM Partitions 42.INTO @objectid, @indexid, @partitionnum, @frag;
43.IF @ @FETCH_STATUS < 0 break;  44.SELECT @objectname = QuoteName (o.name), @schemaname = QuoteName (s.name) 45.FROM sys.objects as O 46.JOIN sys.schemas as
s on s.schema_id = o.schema_id 47.WHERE o.object_id = @objectid;
48.SELECT @indexname = QuoteName (name) 49.FROM sys.indexes 50.WHERE object_id = @objectid and index_id = @indexid;
51.SELECT @partitioncount = count (*) 52.FROM sys.partitions 53.WHERE object_id = @objectid and index_id = @indexid; 54.–30 is a arbitrary decision on which to SWItch between reorganizing and rebuilding. 55.IF @frag < 30.0 56.SET @command = N ' ALTER INDEX ' + @indexname + n ' on ' + @schemaname + n '. ' + @objectname + n ' REO
Rganize '; 57.IF @frag >= 30.0 58.SET @command = n ' ALTER INDEX ' + @indexname + n ' on ' + @schemaname + n '. ' + @objectname + n ' RE
Build ';
59.IF @partitioncount > 1 60.SET @command = @command + N ' partition= ' + CAST (@partitionnum as nvarchar (10));
61.EXEC (@command);
62.PRINT N ' executed: ' + @command;
63.END;
64.–close and deallocate the cursor.
65.CLOSE partitions;
66.DEALLOCATE partitions;
67.–drop the temporary table.
68.DROP TABLE #work_to_do; 69.GO