sys.dm_db_index_physical_stats非常慢

Question

我有一个大约4.5TB的数据库，因为我们在一个表(按月划分)上有并行插入(以减少每日负载时间)，因此这个表上的聚集索引往往是严重分散的。当我从这个表上的sys.dm_db_index_physical_stats (Limited)中进行选择时，它需要时间(> 4-5小时)。有没有一种更快更好的方法来检查这个表的分区上的碎片级别，目前所花费的时间是完全不可接受的。

Answer 1

不要为整个数据库运行DMV，而是为特定的表或索引运行它。由于数据库的规模很大，这肯定需要时间。

你必须读保罗·兰德尔的解释关于为什么这个车管所可能需要更多的时间。

DMV的思想是显示索引的物理属性(以及堆的特殊情况)--为了做到这一点，它必须扫描包含索引的页面，计算统计数据。许多DMV支持所谓的谓词下推，这意味着如果指定WHERE子句，DMV在准备信息时会考虑到这一点。这个DMV没有，如果你只要求数据库中逻辑碎片> 30%的索引，它会扫描所有的索引，然后告诉你那些符合你的标准的索引。它必须这样做，因为在分析这些标准之前，它无法知道哪些标准符合您的标准--因此不能支持谓词下推。

您是否尝试过用于索引重建的Ola Hallengren解决方案。

正如他在文章中提到的那样，你可以尝试多种模式。但正如你所说的，有限公司也花了4到5个小时，我想这就是车管所。它实际上是缓慢的，甚至MS对此也有同样的看法。

索引重建被认为是维护活动，应该在服务器负载相对较少或在维护窗口期间完成。

Answer 2

今天看完这篇文章后，我做了一个测试。我在Server 2019上有一个非常零碎的表。

表本身是3GB，索引是6GB。我只使用查询检查了一个表：

SELECT OBJECT_NAME(ips.OBJECT_ID), 
       i.NAME, 
       ips.index_id, 
       index_type_desc, 
       avg_fragmentation_in_percent, 
       avg_page_space_used_in_percent, 
       page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
     INNER JOIN sys.indexes i ON(ips.object_id = i.object_id)
                                AND (ips.index_id = i.index_id)
    WHERE OBJECT_NAME(ips.OBJECT_ID) = 'MyTableName'
ORDER BY avg_fragmentation_in_percent DESC;

在一个不受压力的测试数据库上花了01:18h。

所以，是的，有时候它会非常慢

Answer 3

试试这个：

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

信贷：https://www.se.com/ww/en/faqs/FA234246/