Sql-server – Optimizing a DMV query

dmvexecution-plansql server

I have found a query that is a major performance issue in my environment. I looked at the actual execution plan for this query and found out the main problem is the remote scan (98%). Its remote because SQL Server is accessing its system objects (DMVs).

If you look at the estimated number of rows it says 33, but the actual number of rows is over 16 000.

If it was a regular table query I would look at the statistics for the columns but in this case I don't know what I can do.

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

Do you have any ideas on how to optimize it?

Best Answer

A dynamic management view, like the name implies, isn't a table with indexes and statistics, but rather a view which could potentially use a large number of system tables.

To generally speed up complex DMV queries, you could try dumping the contents of some of the larger DMVs into a temp table or table variable and then use those. You can control the indexing on the temp tables, and it also reduces the number of join operations that the server has to perform.

For the same reason, if your solutions allows for it, consider using WITH (NOLOCK) in order to minimize locking on those system tables, which could otherwise affect your entire database or server.

Related Question