Blocking Example Code

In our previous post we looked at detecting and analyzing blocking. This post contains a script with blocking example code. Output from the analysis samples contained in the previous post is also provided.

Not a lot of blocking example code in this lovely picture of a brothel by Joachim Beuchelaer

Not a lot of blocking example code in this lovely picture of a brothel by Joachim Beuchelaer, circa 1533-1575

Blocking Example Code

First, we need to create a table we can use to test blocking. I’m creating this in tempdb since we don’t care if this table hangs around. If we forget to drop it, it will be gone when the server is restarted:

Now, start a transaction, and insert a row into the table:

Switch to a new SSMS window, and run the following:

Notice you see no results. The query will continue to run until the transaction you started in the first SSMS window either commits or rolls back. If you have the blocked process threshold configured for 5 seconds, you’ll only need to wait 5 seconds for a blocked process event to occur. Assuming you’ve configured the Extended Events session in our previous post, you’ll be able to run the analysis queries, and see output similar to the following:

╔═══════════════╦════════════════════════╦═══════════════╦═══════════════════════╦═══════════════════╗
║ Blocked_Proc  ║ Blocked_Text           ║ Blocking_Proc ║ Blocking_Text         ║ Number of         ║
║               ║                        ║               ║                       ║ blocked_processes ║
╠═══════════════╬════════════════════════╬═══════════════╬═══════════════════════╬═══════════════════╣
║ NULL          ║ SELECT *               ║ NULL          ║ BEGIN TRANSACTION     ║ 29                ║
║               ║ FROM dbo.BlockingTest; ║               ║ INSERT INTO           ║                   ║
║               ║                        ║               ║ dbo.BlockingTest (id) ║                   ║
║               ║                        ║               ║ VALUES (1);           ║                   ║
╚═══════════════╩════════════════════════╩═══════════════╩═══════════════════════╩═══════════════════╝

As you can see in the output above, the Blocked_Text column contains the query we’re running in the 2nd SSMS window, while the Blocking_Text column shows the statement that is causing the blocking. Since I waited a long time to cancel the transaction, the Number of blocked_processes column shows 29, indicating the blocking statement was captured by our Blocked Process Extended Events session 29 times.

Thanks for checking out our blocking example code! See the rest of our posts on troubleshooting.