效率碎了一地,重新来过!

标签（空格分隔）： 索引碎片

---

很多时候我们维护的数据库在运行了一段时间后(多则3个月,少则数十个小时),查询语句的效率会突然下降.就像瓷瓶摔到地上突然碎裂一样, 执行效率也碎成了渣渣.
原因就是因为表中的数据不断的变更,造成了索引碎片率的增加(不仅仅是这个原因,还有其他原因,我讲在后续的篇章中逐步和大家分享)
这次我们就来看看怎么拯救这个碎了一地的效率.

首先执行下面的SQL:

SELECT OBJECT_NAME (ips.[object_id]) AS 'Object Name',
si.name AS 'Index Name',  
ROUND (ips.avg_fragmentation_in_percent, 2) AS 'Fragmentation',  
ips.page_count AS 'Pages',  
ROUND (ips.avg_page_space_used_in_percent, 2) AS 'Page Density'
FROM sys.dm_db_index_physical_stats(DB_ID ('#DBName#'), NULL, NULL, NULL, DEFAULT) as ips 
CROSS APPLY sys.indexes si
WHERE si.object_id = ips.object_id  
AND si.index_id = ips.index_id  
AND ips.index_level = 0 -- only the leaf level  
AND ips.avg_fragmentation_in_percent > 10 
AND OBJECT_NAME (ips.[object_id]) ='#TableName#'--这里可以指定表名
GO

执行完毕后,将可以看到指定数据库(#DBName#)中的指定表(或者全部表)中所有索引中碎片率超过10%的索引信息.

Index_Frag.png

根据上述结果可以得知相应的索引碎片情况.
根据经验,碎片率在10%~30%时,重新整理索引收益较高.(alter index XXX on XXX reorganize;)
当碎片率超过30%时,重建索引的收益则更高.(alter index XXX on XXX rebuild;)

注意:重建索引会导致极大的性能影响,因此一定要在业务低峰期进行重建工作,一般情况下都需要选择在线重建(rebuild后加上with online = on)

---

还有需要注意的是,重建完毕后重新检查一下碎片信息.确保效果.
最终还要重新整理一下统计信息和清理一下执行计划:

整理统计信息:

    --更新全库的统计信息:
    EXEC [sys].[sp_updatestats]
    GO
    --更新指定表的全部统计信息:
    UPDATE STATISTICS Sales.SalesOrderDetail;
    GO

清理执行计划

DBCC FREEPROCCACHE

特别注意:
上述所有操作一定要在业务低峰期进行!