mercredi 28 janvier 2015

Optimizing a DMV query


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?





Aucun commentaire:

Enregistrer un commentaire