Tag Archives: blocking

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!)

Transact-SQL

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

SELECTx.textASblocking_text

,x.nt_username

,x.nameASblocking_db

,st.text

,a.nt_username

,d.nameASblocked_db

,a.status

,a.*

FROMmaster.sys.sysprocessesa

INNERJOINsys.databasesdONa.dbid=d.database_id

CROSSAPPLYsys.dm_exec_sql_text(a.sql_handle)ASst

OUTERAPPLY (SELECTblock.text,aa.nt_username,aa.spid,dd.name

FROMmaster.sys.sysprocessesaa

INNERJOINsys.databasesddONaa.dbid=dd.database_id

CROSSAPPLYsys.dm_exec_sql_text(aa.sql_handle)ASblock

WHEREa.blocked=aa.spid)x

WHERE1=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.

Next week I am returning to Orlando not only to visit the mouse at his house, but also to speak at SQL Saturday Orlando! Last year was my first time there and I had a great time. This also happened after the event was pushed back to November 2016 because of Hurricane Matthew.

This year I will talking about deadlocking and blocking – something that is an issue for so many DBAs. Whether this issue is indexes or bad code…or you have been trying in use indexes to cover up bad code, blocks and deadlocks can happen in even the best environments under the right circumstances. If you are going to be at SQL Saturday Orlando come to my session where we will talk about detecting and preventing these arch enemies of the DBA!

The first of the two is a new session for me. When it comes to blocking and deadlocks, I like to be able to identify the cause (and sometimes the culprit) and have the information needed to resolve the issue. Some methods are easier, while with others, you also have to think about the footprint of the troubleshooting itself.

With Making the Leap from Developer to DBA, this session has evolved with time and my own experiences. Anyone who is looking to transition to being a DBA full time should attend to get a real-life perspective on migrating into this role and some of the things you can look forward to.

On top of all this, I have also been invited to be part of the WIT Panel. Come join my self and some other amazing women during lunch as we share and discuss confidence and our experiences in IT.

I’m looking forward to another great SQL Saturday as well as meeting everyone in Phoenix. See y’all there! 🙂