在数据库上加入具有许多分区表的系统表和 DMV 时出现性能问题？

Question

我每天两次对环境中的每个数据库运行以下查询以进行跟踪。我有一个相对繁忙的服务器，其数据库对此查询的计数为 95k 行(此数据库包含许多大约 20 个表，这些表可以有许多分区，有些超过 300 个(。在我的所有其他服务器上，它运行良好，但是对于这个数据库，我开始遇到问题，它曾经在几分钟内完成，但现在将运行超过 18 分钟。

如果我只做一个 COUNT，它会在大约 1 秒内返回 93462。

SELECT i.name 需要 2 秒(返回 93462 行(

SELECT p.rows 至少需要 18 分钟

sp_whoisactive显示没有WAIT_INFO。 我尝试使用 SQL Sentry 计划资源管理器的 WAIT STATS 功能，但由于查询从未完成(我让它运行的最长时间为 18 分钟(，因此我没有得到任何结果。

这是在 SQL Server 2016 SP1-CU2、企业版、64 位上

非常感谢所有的帮助！

SELECT distinct 'mydbname' AS database_name,
SCHEMA_NAME(o.schema_id) AS table_schema,
o.name AS table_name,
i.name AS index_name,
o.type_desc as type_desc_full,
CASE i.type 
WHEN 0 THEN 'HP' 
WHEN 1 THEN 'C' 
WHEN 2 THEN 'NC'
WHEN 3 THEN 'XM'
WHEN 4 THEN 'Sp'
WHEN 5 THEN 'CS' --clustered columnstore, which doesn't exist (yet)
WHEN 6 THEN 'cs' --nonclustered columnstore, available in 2012.
ELSE 'UK' END AS type_desc_brief,
MAX(a.type) as allocation_type,
MAX(p.rows) AS rows_in_table,
SUM(a.total_pages * 8/1024) AS Total_MB,
avg(u.user_seeks) AS user_seeks,
MAX(last_user_seek) AS last_user_seek,
avg(u.user_lookups) AS user_lookups,
MAX(last_user_lookup) AS last_user_lookup,
avg(u.user_scans) AS  user_scans,
MAX(last_user_scan) AS last_user_scan,
avg(u.user_updates) AS  user_updates,
MAX(last_user_update) AS last_user_update
FROM    sys.indexes i
JOIN    sys.objects o
ON i.object_id = o.object_id
LEFT JOIN sys.dm_db_index_usage_stats u
ON i.object_id = u.object_id
AND i.index_id = u.index_id
AND u.database_id = DB_ID()
inner JOIN sys.partitions as p
on i.object_id = p.object_id
and i.index_id = p.index_id
INNER JOIN sys.allocation_units AS a
on (a.type = 2 AND p.partition_id = a.container_id)
OR ((a.type = 1 OR a.type = 3) AND p.hobt_id = a.container_id)
--WHERE   o.type_desc NOT IN ('SYSTEM_TABLE', 'INTERNAL_TABLE','SQL_TABLE_VALUED_FUNCTION')           -- No system tables!
GROUP BY SCHEMA_NAME(o.schema_id),
o.name,
i.name,
o.type_desc,
CASE i.type 
WHEN 0 THEN 'HP' 
WHEN 1 THEN 'C' 
WHEN 2 THEN 'NC'
WHEN 3 THEN 'XM'
WHEN 4 THEN 'Sp'
WHEN 5 THEN 'CS' --clustered columnstore
WHEN 6 THEN 'cs' --nonclustered columnstore, available in 2012.
ELSE 'UK' END

Answer 1

我已经看到由于访问sys.partitions而阻塞。 我相信sys.partitions.rows来自stats对象。 可能从很多很多单独的对象中收集，任何一个统计对象都可能是阻塞的位置。

sys.dm_session_wait_stats有什么启示吗？