SQL SERVER 2008 R2 重建索引的方法
参考sys.dm_db_index_physical_stats
检查索引碎片情况
1.SELECT 2.OBJECT_NAME(object_id)asobjectname, 3.object_idASobjectid, 4.index_idASindexid, 5.partition_numberASpartitionnum, 6.avg_fragmentation_in_percentASfra 7.FROMsys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,‘LIMITED') 8.WHEREavg_fragmentation_in_percent>10.0ANDindex_id>0; 9. 10.使用脚本中的sys.dm_db_index_physical_stats重新生成或重新组织索引（来源于联机帮助） 11. 12.SETNOCOUNTON; 13.DECLARE@objectidint; 14.DECLARE@indexidint; 15.DECLARE@partitioncountbigint; 16.DECLARE@schemanamenvarchar(130); 17.DECLARE@objectnamenvarchar(130); 18.DECLARE@indexnamenvarchar(130); 19.DECLARE@partitionnumbigint; 20.DECLARE@partitionsbigint; 21.DECLARE@fragfloat; 22.DECLARE@commandnvarchar(4000); 23.–Conditionallyselecttablesandindexesfromthesys.dm_db_index_physical_statsfunction 24.–andconvertobjectandindexIDstonames. 25.SELECT 26.object_idASobjectid, 27.index_idASindexid, 28.partition_numberASpartitionnum, 29.avg_fragmentation_in_percentASfrag 30.INTO#work_to_do 31.FROMsys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,‘LIMITED') 32.WHEREavg_fragmentation_in_percent>10.0ANDindex_id>0; 33.–Declarethecursorforthelistofpartitionstobeprocessed. 34.DECLAREpartitionsCURSORFORSELECT*FROM#work_to_do; 35.–Openthecursor. 36.OPENpartitions; 37.–Loopthroughthepartitions. 38.WHILE(1=1) 39.BEGIN; 40.FETCHNEXT 41.FROMpartitions 42.INTO@objectid,@indexid,@partitionnum,@frag; 43.IF@@FETCH_STATUS<0BREAK; 44.SELECT@objectname=QUOTENAME(o.name),@schemaname=QUOTENAME(s.name) 45.FROMsys.objectsASo 46.JOINsys.schemasassONs.schema_id=o.schema_id 47.WHEREo.object_id=@objectid; 48.SELECT@indexname=QUOTENAME(name) 49.FROMsys.indexes 50.WHEREobject_id=@objectidANDindex_id=@indexid; 51.SELECT@partitioncount=count(*) 52.FROMsys.partitions 53.WHEREobject_id=@objectidANDindex_id=@indexid; 54.–30isanarbitrarydecisionpointatwhichtoswitchbetweenreorganizingandrebuilding. 55.IF@frag<30.0 56.SET@command=N‘ALTERINDEX‘+@indexname+N‘ON‘+@schemaname+N‘.'+@objectname+N‘REORGANIZE'; 57.IF@frag>=30.0 58.SET@command=N‘ALTERINDEX‘+@indexname+N‘ON‘+@schemaname+N‘.'+@objectname+N‘REBUILD'; 59.IF@partitioncount>1 60.SET@command=@command+N‘PARTITION='+CAST(@partitionnumASnvarchar(10)); 61.EXEC(@command); 62.PRINTN‘Executed:‘+@command; 63.END; 64.–Closeanddeallocatethecursor. 65.CLOSEpartitions; 66.DEALLOCATEpartitions; 67.–Dropthetemporarytable. 68.DROPTABLE#work_to_do; 69.GO