0

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?

  • How about not running it twice? Your duration filter could be in the where clause of the second query and simply eliminate the first one with TOP. Also, instead of re-inventing the wheel, you could use a blocked process report someone already wrote. Finally, I ran this query and don't see a remote scan anywhere. Could you post the actual, post-execution plan somewhere? – Aaron Bertrand Jan 28 '15 at 11:39
1

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.

  • If I regularly collect data from DMVs to user tables I don't have accurate data there. Also the query I posted is executed every 10 seconds so I would prefer some other solution. – BuahahaXD Jan 28 '15 at 10:36
  • Please tell me a bit more about the WITH (NOLOCK) hint. Do you suggest adding it to this query or some other queries using the same objects? – BuahahaXD Jan 28 '15 at 10:37
  • 2
    I would recommend setting the isolation level using SET rather than peppering WITH (NOLOCK) hints all over every single view mentioned in the query. – Aaron Bertrand Jan 28 '15 at 11:40
  • What I mean by using a temp table is merely that you divide your statement into three parts; populate a temp table, execute the select query (with the DMV replaced by the temp table), drop the temp table. This might give you an overall performance increase. What you won't get is strictly atomic data integrity, as in that millisecond that it takes to populate the temp table, records may change in the DMV. – Daniel Hutmacher Jan 28 '15 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.