优化DMV查询

Question

在我的环境中，我发现了一个主要的性能问题。我查看了这个查询的实际执行计划，发现主要问题是远程扫描(98%)。因为Server正在访问它的系统对象(DMV)，所以它的远程。

如果看一下估计的行数，它表示为33行，但实际行数超过16 000行。

如果这是一个普通的表查询，我会查看列的统计信息，但在本例中，我不知道我能做什么。

DECLARE @Duration FLOAT
DECLARE @Date DATETIME
DECLARE @MinDuration FLOAT
SET @MinDuration = 5
SET @Duration = null

SELECT TOP 1 @Duration = wt.wait_duration_ms / 1000.0
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
order by wt.wait_duration_ms desc

IF (@Duration IS NOT NULL and @Duration > @MinDuration )
BEGIN
    SET @Date = GETDATE()
    INSERT INTO Log_Locks
        SELECT
        db.name DBName,
        tl.request_session_id,
        wt.blocking_session_id,
        OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
        tl.resource_type,
        h1.TEXT AS RequestingText,
        h2.TEXT AS BlockingTest,
        tl.request_mode,
        wt.wait_duration_ms,
        @Date,
        es.host_name BlockedHostName,
        ec2.client_net_address BlockedClientNetAddress
        FROM sys.dm_tran_locks AS tl
        INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
        INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
        INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
        INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
        INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
        left join sys.dm_exec_sessions es ON es.session_id = wt.blocking_session_id
        CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
        CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
END

你对如何优化它有什么想法吗？

Answer 1

动态管理视图(顾名思义)不是一个包含索引和统计信息的表，而是一个可能使用大量系统表的视图。

为了通常加速复杂的DMV查询，您可以尝试将一些较大的DMV的内容转储到临时表或表变量中，然后使用这些变量。您可以控制临时表的索引，还可以减少服务器必须执行的连接操作的数量。

出于同样的原因，如果您的解决方案允许，请考虑使用WITH (NOLOCK)，以尽量减少对这些系统表的锁定，否则可能会影响整个数据库或服务器。