sqlserver索引优化

1. 查询索引碎片

SELECTOBJECT_NAME(B.OBJECT_ID)表名,B.NAME 索引名称,A.INDEX_TYPE_DESC 索引类型,ROUND(A.AVG_FRAGMENTATION_IN_PERCENT,2)碎片率FROMsys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL)AINNERJOINsys.indexes BONA.OBJECT_ID=B.OBJECT_IDANDA.INDEX_ID=B.INDEX_IDWHERE1=1ANDA.AVG_FRAGMENTATION_IN_PERCENT>30--AND A.AVG_FRAGMENTATION_IN_PERCENT>5 AND A.AVG_FRAGMENTATION_IN_PERCENT<=30ORDERBYOBJECT_NAME(B.OBJECT_ID),A.AVG_FRAGMENTATION_IN_PERCENTDESC

3. 生成优化索引碎片语句

SELECTOBJECT_SCHEMA_NAME(B.OBJECT_ID)架构,OBJECT_NAME(B.OBJECT_ID)表名,B.NAME 索引名,ROUND(A.AVG_FRAGMENTATION_IN_PERCENT,2)碎片率,CASEWHENA.AVG_FRAGMENTATION_IN_PERCENT>30THENN'重新生成索引'ELSEN'重新组织索引'END处理方式,'ALTER INDEX '+QUOTENAME(B.NAME)+' ON '+QUOTENAME(OBJECT_SCHEMA_NAME(B.OBJECT_ID))+'.'+QUOTENAME(OBJECT_NAME(B.OBJECT_ID))+' '+CASEWHENA.AVG_FRAGMENTATION_IN_PERCENT>30THEN'REBUILD'ELSE'REORGANIZE'END生成SQL语句FROMsys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL)AINNERJOINsys.indexes BONA.OBJECT_ID=B.OBJECT_IDANDA.INDEX_ID=B.INDEX_IDWHEREA.AVG_FRAGMENTATION_IN_PERCENT>5ANDB.INDEX_ID>0--AND OBJECT_NAME(B.OBJECT_ID) IN ('INVMB') --指定表ORDERBYCASEWHENA.AVG_FRAGMENTATION_IN_PERCENT>30THENN'重新生成索引'ELSEN'重新组织索引'END,OBJECT_NAME(B.OBJECT_ID),B.INDEX_ID

4. 更新统计全部信息

EXECsp_updatestats

5. 更新统计单表信息

UPDATESTATISTICS[表名]