Collecting the Blocked Process Report (XEvents and Server Side Trace)

unblocked-clean-upI’m a big fan of the built-in Blocked Process Report in SQL Server. It’s come in handy for troubleshooting blocking situations for me many times.

I wanted a friendly way to share code to configure and manage the Blocked Process Report, so I’ve created a gist on GitHub sharing TSQL that:

  • Enables the Blocked Process Report (BPR)
  • Collects the BPR with an Extended Events trace
  • Collects the BPR using a Server Side SQL Trace (in case you don’t care XEvents or are running an older version of SQL Server)
  • Lists out the Extended Events and SQL Traces you have running, and gives you code to stop and delete traces if you wish

View or download the code from GitHub, or get it below.

Tools to help decode the Blocked Process Report after you collect it

https://gist.github.com/f93c10a00d01d0f4ba00b84d9ac72e1b

 

 

Previous Post
Actual Time Statistics in Execution Plans: Elapsed CPU Time and more
Next Post
Tracing Deadlock Graphs: Extended Events or Server Side Trace

Related Posts

10 Comments. Leave new

Garry Bargsley
January 16, 2017 11:15 am

This is great information. I read your article and enabled this in a server side trace for a continued problem. I captured the problem and am not struggling to identify the root cause. Do you know how I can interpret the results to identify the type of lock causing the blocking and object?

Reply
Garry Bargsley
January 16, 2017 12:40 pm

Thank you, I will check both resources out.

Reply
Garry Bargsley
January 18, 2017 6:49 am

I have a question maybe you can help with. We have a SSIS package that has a long select query with multiple joins and temp variables. This process is doing a Sch-S lock. Then we have another job that runs at a similar time that is taking a Sch-M lock on the same schema that the Sch-S lock is on. Then anything that runs behind the process with the Sch-M lock is blocked and starts to back up. Is there a way to make the first process not take the Sch-S lock so it does not cause the Sch-M to cause ripple blocking?

Reply

    Interesting!

    You need to have a shared schema lock to query a table. Even with NOLOCK/read uncommitted. Even with optimistic locking (snapshot / RCSI). Basically, SQL Server needs an assurance that the table you’re reading isn’t going to be dropped out from under it while you’re using it.

    What kind of command requires the SCH-M lock? That’s more unusual. If the command is something like an index rebuild or partition switch, you may be able to get around the blocking chain by using the WAIT_AT_LOW_PRIORITY option on the command. (SQL Server 2014 and higher.)

    Reply
      Garry Bargsley
      January 18, 2017 10:51 am

      Our ETL guys have tried to be fancy with their data loads and are doing a sp_rename with an alter schema mixed in.

      Reply

        Ah. Yeah, that’s a hard one.

        I just had an idea for one thing that might help. But it may be utterly terrible. I’ll play with it and report back.

        Reply

        Alright, I did some quick testing, and there is an option that sucks less. It’s still not perfect, and it only works on SQL Server 2014 and higher, but it’s something.

        To change in the new data, you could ask your ETL team to test out a partition switching pattern instead of sp_rename. This will work even in old versions on Standard Edition as long as the tables only have one partition (every table has at least one).

        Here’s the basic pattern, although you might not want to use this duration or abort the blockers, it’s up to you:
        BEGIN TRAN

        ALTER TABLE dbo.ProductionTable SWITCH PARTITION 1 TO dbo.ProductionTableOld PARTITION 1
        WITH ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ));

        –Anyone who tries to query the table after the switch has happened and before
        –the transaction commits will be blocked: we’ve got a schema mod lock on the table

        ALTER TABLE dbo.StagingTable SWITCH PARTITION 1 TO dbo.ProductionTable PARTITION 1

        COMMIT

        The benefit here is that WAIT_AT_LOW_PRIORITY will not cause that big blocking chain you’re seeing now. It will simply sit to the side and wait, and the command controls how long, and whether it kills others, kills itself, or simply waits. It’s all up to you.

        You do have to create a table to switch out to, if you want to avoid deletes (TRUNCATE TABLE WITH PARTITION doesn’t have the WAIT_AT_LOW_PRIORITY even in 2016), but you wouldn’t be using any more space than you’re using now– you could just truncate it immediately after switch out.

        I’ll write up a blog post today with more code examples. I’m kind of excited that this works! Not sure why I didn’t put this together six months ago when I was thinking about how alter table switch works even in Standard Edition.

        Reply
Garry Bargsley
January 18, 2017 12:37 pm

That is so helpful. Thank you so much for your input and research. Locking and blocking is not my specialty.

Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu