参考sys.dm_db_index_physical_stats
检查索引碎片情况
1.select 2.object_name(object_id) as objectname, 3.object_id as objectid, 4.index_id as indexid, 5.partition_number as partitionnum, 6.avg_fragmentation_in_percent as fra 7.from sys.dm_db_index_physical_stats (db_id(), null, null , null, ‘limited') 8.where avg_fragmentation_in_percent > 10.0 and index_id > 0; 9. 10.使用脚本中的 sys.dm_db_index_physical_stats 重新生成或重新组织索引 （来源于联机帮助） 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 an arbitrary decision point at 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‘ reorganize'; 57.if @frag >= 30.0 58.set @command = n‘alter index ‘ + @indexname + n‘ on ‘ + @schemaname + n‘.' + @objectname + n‘ rebuild'; 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