SQL Server高级进阶之索引碎片维护

?

1 2 3 4 5 6 7 8 9

SELECT OBJECT_SCHEMA_NAME(B.OBJECT_ID) 架构,OBJECT_NAME(B.OBJECT_ID) 表名,B.NAME 索引名,ROUND(A.AVG_FRAGMENTATION_IN_PERCENT,2) 碎片率,     CASE WHEN A.AVG_FRAGMENTATION_IN_PERCENT>30 THEN N'重新生成索引' ELSE N'重新组织索引' END 处理方式,     'ALTER INDEX '+QUOTENAME(B.NAME)+' ON '+QUOTENAME(OBJECT_SCHEMA_NAME(B.OBJECT_ID))+'.'+QUOTENAME(OBJECT_NAME(B.OBJECT_ID))+' '         +CASE WHEN A.AVG_FRAGMENTATION_IN_PERCENT>30 THEN 'REBUILD' ELSE 'REORGANIZE' END 生成SQL语句 FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A INNER JOIN sys.indexes B ON A.OBJECT_ID=B.OBJECT_ID AND A.INDEX_ID=B.INDEX_ID WHERE A.AVG_FRAGMENTATION_IN_PERCENT>5 AND B.INDEX_ID>0     --AND OBJECT_NAME(B.OBJECT_ID) IN ('Basic_Language')    --指定表 ORDER BY CASE WHEN A.AVG_FRAGMENTATION_IN_PERCENT>50 THEN N'重新生成索引' ELSE N'重新组织索引' END,OBJECT_NAME(B.OBJECT_ID),B.INDEX_ID

　　把语句拷贝处理执行