April 30, 2015 at 11:00 am
I got this query from a tuning book and it does not run in 2012. I believe the issue is in the substring section, and the error mentions a store procedure I'm not even using. If someone can help it would be greatly appricated thanks.
SELECT DTL.resource_type,
CASE WHEN DTL.resource_type IN ( 'DATABASE', 'FILE', 'METADATA' )
THEN DTL.resource_type
WHEN DTL.resource_type = 'OBJECT'
THEN OBJECT_NAME(DTL.resource_associated_entity_id)
WHEN DTL.resource_type IN ( 'KEY', 'PAGE', 'RID' )
THEN ( SELECT OBJECT_NAME(object_id)
FROM sys.partitions
WHERE sys.partitions.hobt_id = DTL.resource_associated_entity_id )
ELSE 'Unidentified'
END AS [Parent Object],
DTL.request_mode AS [Lock Type] ,
DTL.request_status AS [Request Status] ,
DOWT.wait_duration_ms AS [wait duration ms] ,
DOWT.wait_type AS [wait type] ,
DOWT.session_id AS [blocked session id] ,
DES_blocked.login_name AS blocked_user,
SUBSTRING(dest_blocked.text, der.statement_start_offset / 2,
( CASE WHEN der.statement_end_offset = -1
THEN DATALENGTH(dest_blocked.text)
ELSE der.statement_end_offset
END - der.statement_start_offset )) / 2 AS blocked_command,
DOWT.blocking_session_id AS [blocking session id] ,
DES_blocking.login_name AS [blocking user] ,
DEST_blocking.text AS [blocking command] ,
DOWT.resource_description AS [blocking resource detail]
FROMsys.dm_tran_locks DTL
INNER JOIN sys.dm_os_waiting_tasks DOWT
ON DTL.lock_owner_address = DOWT.resource_address
INNER JOIN sys.dm_exec_requests DER
ON DOWT.session_id = DER.session_id
INNER JOIN sys.dm_exec_sessions DES_blocked
ON DOWT.session_id = DES_Blocked.session_id
INNER JOIN sys.dm_exec_sessions DES_blocking
ON DOWT.blocking_session_id = DES_Blocking.session_id
INNER JOIN sys.dm_exec_connections DEC
ON DTL.request_session_id = DEC.most_recent_session_id
CROSS APPLY sys.dm_exec_sql_text(DEC.most_recent_sql_handle) AS DEST_Blocking
CROSS APPLY sys.dm_exec_sql_text(DER.sql_handle) AS DEST_Blocked
WHEREDTL.resource_database_id = DB_ID()
April 30, 2015 at 11:19 am
Posted too soon, sorry. Figured it out.
April 30, 2015 at 11:19 am
lmacdonald (4/30/2015)
I got this query from a tuning book and it does not run in 2012. I believe the issue is in the substring section, and the error mentions a store procedure I'm not even using. If someone can help it would be greatly appricated thanks.SELECT DTL.resource_type,
CASE WHEN DTL.resource_type IN ( 'DATABASE', 'FILE', 'METADATA' )
THEN DTL.resource_type
WHEN DTL.resource_type = 'OBJECT'
THEN OBJECT_NAME(DTL.resource_associated_entity_id)
WHEN DTL.resource_type IN ( 'KEY', 'PAGE', 'RID' )
THEN ( SELECT OBJECT_NAME(object_id)
FROM sys.partitions
WHERE sys.partitions.hobt_id = DTL.resource_associated_entity_id )
ELSE 'Unidentified'
END AS [Parent Object],
DTL.request_mode AS [Lock Type] ,
DTL.request_status AS [Request Status] ,
DOWT.wait_duration_ms AS [wait duration ms] ,
DOWT.wait_type AS [wait type] ,
DOWT.session_id AS [blocked session id] ,
DES_blocked.login_name AS blocked_user,
SUBSTRING(dest_blocked.text, der.statement_start_offset / 2,
( CASE WHEN der.statement_end_offset = -1
THEN DATALENGTH(dest_blocked.text)
ELSE der.statement_end_offset
END - der.statement_start_offset )) / 2 AS blocked_command,
DOWT.blocking_session_id AS [blocking session id] ,
DES_blocking.login_name AS [blocking user] ,
DEST_blocking.text AS [blocking command] ,
DOWT.resource_description AS [blocking resource detail]
FROMsys.dm_tran_locks DTL
INNER JOIN sys.dm_os_waiting_tasks DOWT
ON DTL.lock_owner_address = DOWT.resource_address
INNER JOIN sys.dm_exec_requests DER
ON DOWT.session_id = DER.session_id
INNER JOIN sys.dm_exec_sessions DES_blocked
ON DOWT.session_id = DES_Blocked.session_id
INNER JOIN sys.dm_exec_sessions DES_blocking
ON DOWT.blocking_session_id = DES_Blocking.session_id
INNER JOIN sys.dm_exec_connections DEC
ON DTL.request_session_id = DEC.most_recent_session_id
CROSS APPLY sys.dm_exec_sql_text(DEC.most_recent_sql_handle) AS DEST_Blocking
CROSS APPLY sys.dm_exec_sql_text(DER.sql_handle) AS DEST_Blocked
WHEREDTL.resource_database_id = DB_ID()
You should also post the full text of the error messages you are getting when attempting to run the above code.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply