March 7, 2016 at 4:57 am
I've got this query below to find the sql_text of the waiting process but how do I get the sql_text for the process causing the blocking?
SELECT session_id, blocking_session_id,text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE session_id > 50
March 7, 2016 at 5:09 am
sys.dm_exec_requests has a column called blocking_session_id. Do a self join on session_id = blocking_session_id and a second CROSS APPLY to get the text itself.
John
March 7, 2016 at 5:55 am
Could you provide an example? When I run the following command no text shows for the SQL_TEXT
SELECT
spid
,sp.STATUS
,loginame = SUBSTRING(loginame, 1, 12)
,hostname = SUBSTRING(hostname, 1, 12)
,blk = CONVERT(CHAR(3), blocked)
,open_tran
,dbname = SUBSTRING(DB_NAME(sp.dbid),1,10)
,cmd
,waittype
,waittime
,last_batch
,SQLStatement =
SUBSTRING
(
qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2
)
,qt.[text]
FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0
March 7, 2016 at 4:33 pm
Hello,
try to check WhoIsActive, is very helpful for me..Adam has also 30days blog like documentation..
http://sqlblog.com/blogs/adam_machanic/archive/tags/who+is+active/sp_5F00_whoisactive/default.aspx
March 10, 2016 at 11:12 am
You could quickly throw your SPID into DBCC INPUTBUFFER
March 11, 2016 at 2:27 am
smitty-1088185 (3/7/2016)
Could you provide an example? When I run the following command no text shows for the SQL_TEXTSELECT
spid
,sp.STATUS
,loginame = SUBSTRING(loginame, 1, 12)
,hostname = SUBSTRING(hostname, 1, 12)
,blk = CONVERT(CHAR(3), blocked)
,open_tran
,dbname = SUBSTRING(DB_NAME(sp.dbid),1,10)
,cmd
,waittype
,waittime
,last_batch
,SQLStatement =
SUBSTRING
(
qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2
)
,qt.[text]
FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0
Don't use sysprocesses - it's deprecated and only there for backwards compatibility. Try something like this:
SELECT
blocked.session_id AS BlockedSessionID
,blocked.blocking_session_id AS BlockingSessionID
,blockedtext.text AS BlockedText
,blockingtext.text AS BlockingText
FROM sys.dm_exec_requests blocked
LEFT JOIN sys.dm_exec_requests blocking
ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blockedtext
CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) blockingtext
WHERE blocked.session_id > 50
John
March 19, 2016 at 2:13 am
Hi All,
You may try the script below. it is an enhanced version of the blocking query. Additionally, it captures the blocked table and index name.
;WITH T1 AS (
SELECT blocking.session_id AS blocking_session_id ,
blocked.session_id AS blocked_session_id ,
waitstats.wait_type AS blocking_resource ,
waitstats.wait_duration_ms ,
waitstats.resource_description ,
DB_NAME(tl.resource_database_id) AS DatabaseName,
blocked_cache.text AS blocked_text ,
blocking_cache.text AS blocking_text
FROM
sys.dm_exec_connections AS blocking
INNER JOIN sys.dm_exec_requests blocked
ON blocking.session_id = blocked.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle)
blocked_cache
CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle)
blocking_cache
INNER JOIN sys.dm_os_waiting_tasks waitstats
ON waitstats.session_id = blocked.session_id
INNER JOIN sys.dm_tran_locks tl
ON tl.lock_owner_address = waitstats.resource_address
WHERE waitstats.wait_duration_ms >= 2000
), T2A AS (
SELECT blocking_session_id, resource_description,
CHARINDEX('hobtid', resource_description) AS StartPos,
SUBSTRING(resource_description, CHARINDEX('hobtid', resource_description), LEN(resource_description)) AS StartText
FROM T1
), T2B AS (
SELECT blocking_session_id, resource_description, StartPos, StartText, SUBSTRING(StartText, 0, CHARINDEX(' ', StartText)) AS hotbid_text
FROM T2A
), T2C AS (
SELECT blocking_session_id, resource_description, CAST(SUBSTRING(hotbid_text, CHARINDEX('=', hotbid_text)+1, LEN(hotbid_text)) AS BIGINT) AS hobt_id
FROM T2B
), T2D AS (
SELECT T2C.blocking_session_id, T2C.resource_description, OBJECT_SCHEMA_NAME(object_id) AS schema_name, object_name(object_id) as object_name, object_id, partition_id, index_id, partition_number, p.hobt_id, rows
FROM sys.partitions p INNER JOIN T2C ON p.hobt_id = T2C.hobt_id
)
SELECT GETDATE() AS DateTimeCaptured, T1.blocking_session_id, T1.blocked_session_id, T1.blocking_resource, T1.wait_duration_ms, T1.resource_description, T1.DatabaseName, T1.blocking_text, T1.blocked_text,
T2D.schema_name + '.' + T2D.object_name AS blocked_object_name, T2D.object_id AS blocked_object_id, T2D.index_id as blocked_index_id, i.name AS blocked_index_name, i.type_desc AS blocked_index_type
FROM T1 INNER JOIN T2D ON T1.blocking_session_id = T2D.blocking_session_id AND T1.resource_description = T2D.resource_description
INNER JOIN sys.indexes i ON i.object_id = T2D.object_id AND i.index_id = T2D.index_id
I hope this helps.
Best Regards,
Ricky
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply