December 3, 2008 at 1:28 pm
OK. Here's the drill. I'm trying to build a script that gathers blocking information. I've got two sessions, one that opens a transaction and then leaves it open and a second that gets blocked by the first.
First off, you can hit sys.dm_os_waiting_tasks to get sessions that are waiting on something, say, for a lock to clear. That DMV displays, among other things, the blocking session id value in the blocking_session_id field. It also shows the resource_address which you can combine with the lock_owner_address field inside the sys.dm_tran_locks table. Like this:
SELECT *
FROM sys.dm_tran_locks tl
JOIN sys.dm_os_waiting_tasks wt
ON tl.lock_owner_address = wt.resource_address
Works fine. If you want to get the query that's being blocked, you need to get the query_handle. This is available in sys.dm_exec_requests:
SELECT *
FROM sys.dm_tran_locks tl
JOIN sys.dm_os_waiting_tasks wt
ON tl.lock_owner_address = wt.resource_address
JOIN sys.dm_exec_requests wr
ON wr.session_id = tl.request_session_id
This too works. With that you can get the query text. But let's say I want to also get the query text for the query that is causing the blocking. Well, as mentioned before, the session_id is available in sys.dm_os_waiting_tasks, so I'll go ahead and join it.
SELECT *
FROM sys.dm_tran_locks tl
JOIN sys.dm_os_waiting_tasks wt
ON tl.lock_owner_address = wt.resource_address
JOIN sys.dm_exec_requests wr
ON wr.session_id = tl.request_session_id
JOIN sys.dm_exec_requests br
ON br.session_id = wt.blocking_session_id
Here's where the breakdown occurs. The session_id in the sys.dm_exec_requests, on my system, is 52. I've verified this using the sql_handle to look at the sql text. However, the blocking session_id is listed as 53. I also see locks in the sys.dm_tran_locks table for 53, but not for 52.
My question. Where did the session_id 53 come from and how do I join it back to session_id 52 so that I can go and get the query text because there is no session_id equal to 53 in sys.dm_exec_requests.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 3, 2008 at 1:49 pm
I so cannot take credit for this and cannot remember where I found it, but this will show all the current sessions, the blocking, the queries etc.
You could remove the relevant pieces and log to a table where needed.
SELECT
x.session_id,
COALESCE(x.blocking_session_id, 0) AS blocking_session_id,
x.Status,
x.TotalCPU,
x.Start_time,
x.totalElapsedTime,
x.totalReads,
x.totalWrites,
x.Writes_in_tempdb,
( SELECT substring(text,x.statement_start_offset/2,
(case when x.statement_end_offset = -1
then len(convert(nvarchar(max), text)) * 2
else x.statement_end_offset end - x.statement_start_offset)/2)
FROM sys.dm_exec_sql_text(x.sql_handle)
FOR XML PATH(''), TYPE ) AS text,
db_name(x.database_id) as DBName ,
(SELECT object_name(objectid) FROM sys.dm_exec_sql_text(x.sql_handle)) as ObjName,
x.Wait_type,
x.Login_name,
x.Host_name,
( SELECT
p.text
FROM
(
SELECT
MIN(sql_handle) AS sql_handle
FROM sys.dm_exec_requests r2
WHERE
r2.session_id = x.blocking_session_id
) AS r_blocking
CROSS APPLY
(
SELECT substring(text,x.statement_start_offset/2,
(case when x.statement_end_offset = -1
then len(convert(nvarchar(max), text)) * 2
else x.statement_end_offset end - x.statement_start_offset)/2)
FROM sys.dm_exec_sql_text(r_blocking.sql_handle)
FOR XML PATH(''), TYPE
) p (text)
) AS Blocking_text
FROM
( SELECT
r.session_id,
s.host_name,
s.login_name,
r.start_time,
r.sql_handle,
r.database_id,
r.blocking_session_id,
r.wait_type,
r.status,
r.statement_start_offset,
r.statement_end_offset,
SUM(r.total_elapsed_time) as totalElapsedTime,
SUM(r.reads) AS totalReads,
SUM(r.writes) AS totalWrites,
SUM(r.cpu_time) AS totalCPU,
SUM(tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) AS writes_in_tempdb
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
JOIN sys.dm_db_task_space_usage tsu ON s.session_id = tsu.session_id and r.request_id = tsu.request_id
WHERE r.status IN ('running', 'runnable', 'suspended')
GROUP BY
r.session_id,
s.host_name,
s.login_name,
r.start_time,
r.sql_handle,
r.database_id,
r.blocking_session_id,
r.wait_type,
r.status,
r.statement_start_offset,
r.statement_end_offset
) x
where x.session_id <> @@spid
order by x.totalCPU desc
December 3, 2008 at 4:17 pm
Wow! That one is busy. Thanks for posting it.
Interestingly enough, it's not returning query text for the blocking process either.
Now that I think about it, maybe it's my test. I'm opening a transaction, executing some code and then leaving the transaction open while I execute some other code from another connection to create the blocking situation. Here's the current sample (this will be changing, but it'll get show you what's going on:
--create a dummy table
IF(SELECT OBJECT_ID('t1')) IS NOT NULL
DROP TABLE t1
GO
CREATE TABLE t1 (c1 INT, c2 INT, c3 DATETIME)
INSERT INTO t1 VALUES(11, 12, GETDATE())
INSERT INTO t1 VALUES(21, 22, GETDATE())
--run this code in one connection
BEGIN TRAN User1
UPDATE t1 SET c3 = GETDATE()
--run this code in a different connection
BEGIN TRAN User2
SELECT c2 FROM t1 WHERE c1 = 11
COMMIT
Voila! You have blocking. However... I'm thinking that while the locks are retained by the first transaction because it doesn't have a ROLLBACK or COMMIT, the query has already executed and left scope so that there isn't, in this test case, a query to identify as the blocking query.
If that's right, then the simple query I was starting with is actually working fine. However, that is another way to get the job done, clearly. Thanks again.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply