加入dm_db_index_physical_stats非常慢

Question

我在下面的MS Dynamics 2012数据库中运行一个小查询，它运行超过5分钟，我不得不取消它，它显示的是PAGEIOLATCH_SH等待类型。数据库数据文件为560 is，在Server 2012 SP1上。

SELECT TOP 1 A.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A 
INNER JOIN sys.objects B
ON A.object_id = B.object_id
INNER JOIN sys.indexes C 
ON B.object_id = C.object_id AND A.index_id = C.index_id
INNER JOIN sys.partitions D 
ON B.object_id = D.object_id AND A.index_id = D.index_id

我在服务器上检查了磁盘活动的资源监视器，当查询运行时，读取(B/秒)从最初的23,000上升到超过13,000,000，然后在查询被取消后恢复。

有什么原因吗？

Answer 1

磁盘活动很高，因为它需要将整个数据库拉到RAM中进行分析。如果您用较少的NULL调用sys.dm_db_index_physical_stats，它将能够在数据库的一个分段上运行您的查询，然后该子部分将运行得更快。

遗憾的是，您的TOP 1并没有阻止它进行所有的计算，因为您正在用所有这些NULL调用主函数。

Answer 2

...it运行超过5分钟，我不得不取消它，它显示PAGEIOLATCH_SH等待类型。

当Server正在等待从持久存储中获取内存中没有的数据或索引页时，将发生PAGEIOLATCH_SH等待。

在sys.dm_db_index_physical_stats动态管理功能模式下运行的LIMITED (默认)需要读取b树索引的非叶级的页，对于尚未处于缓存中的堆，需要读取IAM/PFS页。对于560 O.的数据库，这很可能导致重要的I/O。

实际上，这是一个更长的查询，我使用它来获得由页面计数和碎片级别筛选的顶级N个分段索引。

你可能在这里重新发明方向盘。流行的免费索引维护解决方案包括：

• Ola Hallengren索引和统计维护脚本；和
• 由午夜引小组重新索引

这些不一定会使碎片评估更快，但它们确实提供了高度可配置的预先构建的解决方案，在世界各地广泛使用。特别是，Minion重新索引允许在维护窗口之外收集碎片信息。