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
热门推荐
1 训练狗狗憋尿
2 狗狗追踪训练
3 狗狗训练要多少钱
5 深圳狗狗训练
6 训练狗狗唤回
7 狗狗叫怎么训练
8 狗狗训练班
10 狗狗社会化训练
11 狗狗叫回训练
12 广州狗狗训练学校
13 狗狗训练躺下
14 狗狗随行训练
15 训练狗狗巡回
16 狗狗要怎么训练
17 狗狗喂食训练
18 训练狗狗躺下