When I was growing up I remember my mom talking about an old, scary, movie that she saw when she was young. In the movie some teenagers were making prank phone calls saying “I saw what you did and I know who you are.” One of the calls happened to be made to a guy that just killed his wife. Joan Crawford played a woman that was romantically inclined to said murderer. She eventually meets her demise when he stabs her because she knew about the first murder.
How every DBA feels when there is blocking
While blocking in SQL server might not be a felony offense (it isn’t…but it should be – WHO’S WITH ME?) as the DBA you not only want to know what is being blocked, but also who is doing the blocking and what in the H-E-Double-Hockey-Sticks they are doing.
At SQL Saturday Orlando I talked about this very thing and the query I defer to for the information.
IMA GONNA HUNT U DOWN!
It might look complicated but it is actually very simple – query sys.sysprocesses with a cross apply using the sql_handle to get the text of the query, and then an outer apply with the same query again but you are joining to the blocking spid so you can get the text for the query that is doing the blocking. Beyond that, you can filter on various columns and refine your output
WHY ARE YOU BLOCKING YOURSELF? WHY ARE YOU BLOCKING YOURSELF? WHY ARE YOU BLOCKING YOURSELF?
Of course, I know you can’t run this without the code (and I know that’s why you’re here…because I SAW WHAT YOU DID!)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SELECT x.text AS blocking_text , x.nt_username ,x.name AS blocking_db , st.text ,a.nt_username ,d.name AS blocked_db ,a.status , a.* FROM master.sys.sysprocesses a INNER JOIN sys.databases d ON a.dbid = d.database_id CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS st OUTER APPLY (SELECT block.text,aa.nt_username, aa.spid, dd.name FROM master.sys.sysprocesses aa INNER JOIN sys.databases dd ON aa.dbid = dd.database_id CROSS APPLY sys.dm_exec_sql_text(aa.sql_handle) AS block WHERE a.blocked = aa.spid) x WHERE 1=1 --AND a.hostname LIKE 'SOME-HOSTNAME%' --AND a.program_name LIKE 'Some program name%' --AND a.dbid = ???? --AND a.spid IN (???) --and a.status not like 'sleeping%' ----AND (st.text LIKE '%text string you want to isolate%') ---and a.blocked != 0 --AND (a.nt_username LIKE '??????%') |
If you are looking for a good way to troubleshoot blocking I hope this helps. If you have some folks running queries that are making you stabby, run this, find out what is going on, and then remove their access try to help them so they aren’t making you stabby any more. Then tell them they need to buy you a beverage because they are still alive.
