February 28, 2011 at 9:11 am
I'm in the process of troubleshooting a blocking issue on a production server.
However, the following query does not seem to complete in a reasonable amount of time (1-2 minutes).
I wonder what the reason of that might be; should I wait longer for it to complete?
I'm concerned about the impact on application performance of having it running for too long.
Here is the query:
--Blocking
--http://www.simple-talk.com/sql/database-administration/investigating-transactions-using-dynamic-management-objects/
SELECT
L.[request_session_id] AS [session_id] ,
DB_NAME(L.[resource_database_id]) AS [Database] ,
L.resource_type ,
CASE
WHEN L.resource_type IN ( 'DATABASE', 'FILE', 'METADATA' )
THEN L.resource_type
WHEN L.resource_type = 'OBJECT'
THEN OBJECT_NAME(L.resource_associated_entity_id, L.[resource_database_id])
WHEN L.resource_type IN ( 'KEY', 'PAGE', 'RID' )
THEN (SELECT OBJECT_NAME([object_id])
FROM sys.partitions
WHERE sys.partitions.hobt_id =
L.resource_associated_entity_id
)
ELSE 'Unidentified'
END AS [Parent Object] ,
L.request_mode AS [Lock Type] ,
L.request_status AS [Request Status] ,
R.[blocking_session_id] ,
S.[login_name] ,
CASE L.request_lifetime
WHEN 0 THEN SQL_R.TEXT
ELSE SQL_C.TEXT
END AS [Statement]
FROM
sys.dm_tran_locks L
LEFT JOIN
sys.[dm_exec_requests] R
ON
L.[request_session_id] = R.[session_id]
INNER JOIN
sys.dm_exec_sessions S
ON
L.request_session_id = S.[session_id]
INNER JOIN
sys.dm_exec_connections C
ON
L.[request_session_id] = C.[most_recent_session_id]
OUTER APPLY
sys.dm_exec_sql_text (C.[most_recent_sql_handle]) AS SQL_C
OUTER APPLY
sys.dm_exec_sql_text(R.[sql_handle]) AS SQL_R
WHERE
--L.[resource_database_id] = DB_ID() AND
L.[resource_type] NOT IN ( 'DATABASE', 'METADATA' )
ORDER BY
L.[request_session_id] ;
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 28, 2011 at 9:42 am
might want to put with nolock on it
so it doesnt lock itself
or readuncommitted
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply