Configuring Extended Events to find Locking Issues in SQL Server

By:   |   Comments (5)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Locking and Blocking


The time might come when a customer reports the famous "database slowness issue" with the application and your first course of action is to check queries that are currently executing in SQL Server. After a quick glimpse (using your favorite method), you might be able to see queries waiting for others to complete and immediately the word "locks" pops in your head.

Currently there are multiple ways to capture the queries that cause blocking, such as:

  • Extended Events: considered as "the best way" by the SQL Server purists, but you simply might not like them or haven’t had time to experiment with them. Nevertheless, this tip is intended to show you how simple it is to collect and visualize the information.
  • Profiler: a very popular way to go, but it is starting to be considered as an old outdated method.
  • sp_who2: the "BlkBy" column can tell you which query is causing the issue in your particular scenario.
  • sp_whoisactive: the famous and very useful stored procedure developed by Adam Machanic. With this one you can find the information in a similar way as with sp_who2, but with the advantage is you can even fetch the execution plan for the queries.

The usage of Extended Events gives you a powerful way to capture a ton of information from your system. In this tip we are going to take a quick look at how to configure an event to capture lock information and generate a report based on that information.

I’m going to make the demonstration using SQL Server Management Studio (SSMS), but it can be perfectly done through T-SQL as well. In the very end I’ll quickly show you how to do this with T-SQL.

Steps to Configure Extended Events for Acquired Locks

In SSMS, expand the "Management" section, expand "Extended Events" and right click on the "Sessions" and select "New Session Wizard".

create new extended event session

Enter a name you would like for your particular session (it can be whatever you want, but anything descriptive is always very helpful). You can choose to start the session at server startup (it is completely up to you).

create new extended event session

In this section we are not going to select a template.

create new extended event session

While you are typing the event you are interested in, a set of matches is presented to you for selection.  As you can see, by typing "lock", a set of lock event classes are shown to you. For this tip, we will focus only on the "lock_acquired" event.

create new extended event session

In this section we are going to select some fields, for demonstration purposes only (you can pick any you find convenient for your particular case).

create new extended event session

From the values you picked in the previous step, you can add any particular filter you might want to apply for your session.

create new extended event session

In this section, you can specify where to save the collected data.

create new extended event session

In this final screen, you can review everything you have selected for your session before making it run.

create new extended event session

When you click "Finish" you will see a "Success" message. In here, you can choose to start the session right away or you can do it later manually.

create new extended event session

Viewing the Collected Data from Extended Events

Now that you have left the Extended Events session running, it is time to see the data it collects.

In SSMS, expand up the "Extended Events" and right click your session and choose "Watch Live Data".

view extended event session data

Here you will see something like this.  You can customize this view with the information you want to include from the fields in the bottom section of the screen. All you have to do is right click the field you want to add and then you select "Show Column in Table". In this particular example, I have included just the "sql_text" and the "duration" of the lock.

view extended event session data

At this point, you might be aware that locking is a very common thing within relational database management systems, so it is nothing extraordinary that we see all those "lock_aquired" messages captured right off the bat. Here’s where the "duration" field will become very handy. All you have to do at this point, is create a filter to display those locks that are related directly to blocking in your system, so you can start your troubleshooting course of action!  To do this, you right-click in the column you want to filter and choose "Filter by this Value".

view extended event session data

The filter we want to apply is for all those locks with duration > 0, and then hit apply.

view extended event session data

After applying the filter, you will be able to see all the events that we are really interested at looking into and it will look something similar to this. This is just an event of a lock example I forced, for demonstration purposes.

view extended event session data

If you want to export the result set to a file, you can do so by selecting "Export to" from the menus.

export extended event session data

Creating Extended Event Session using T-SQL

As you saw above there are several steps to create the extended event session.  It is definitely a lot easier to use the GUI to generate the scripts that having to write the script.  But I wanted to show you how simple the query looks after going through all of the steps in the GUI.

The code below is the exact same session described in all the steps above:

ADD EVENT sqlserver.lock_acquired(
    WHERE ([sqlserver].[database_name]=N'test'))
ADD TARGET package0.event_file(SET filename=N'locks_test')

I hope you found this information very useful!

Next Steps
  • This tip was focused on a single event class (lock_aquired), but there are many more classes that you will probably find helpful as well.
  • You will want to invest some time in discovering all the available classes and their purpose, and hopefully this tip helps you to cover the creation/interaction part for the extended events sessions.
  • You can find very detailed information for more extended events classes at Microsoft’s website.
  • You can find a ton of blocking and locking information at, if you want to dive deeper into this particular area.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Alejandro Cobar Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Saturday, October 12, 2019 - 8:19:36 AM - Erik Dosa Back To Top (82752)

Hi Alejandro,

thank you for the tip. Although, I do not think this extended event is the way to catch the query causing the blocking. What this event collects, is actually the query that is waiting for the lock being placed on the resource. So the real query that blocked this query out is not caught at all.

Apart from Blocked process report being collected, I found it quite difficult to catch the query that holds the lock for some duration of time. I used Pair Maching lock_aquired - lock_released, but that was not much reliable. May be by my mistake for not setting it correctly.

I wonder if there is an easy way to collect queries that keep lock aquired for let say duration >= 10 seconds.


Thursday, June 13, 2019 - 8:21:51 PM - Alejandro Cobar Back To Top (81462)


I believe your issue is related to the version of SSMS you are currently using. As far as I know, SSMS included a GUI for extended events starting from SQL Server 2012. Therefore, I think that if you download the latest version of SSMS, you will be able to see the Extended Events option under the "Management" category.

Wednesday, June 12, 2019 - 12:18:34 PM - Pedro Back To Top (81441)

Extended Events doesn't appear on my Adminitration Menú can I get it?.

Monday, November 5, 2018 - 3:33:49 PM - Alejandro Cobar Back To Top (78162)

In the wizard, in the "Capture Global fields", you can select the "sql_text" field.

When the live data is going on, you should be able to see it at the bottom list of fields. When you find it, right-click on it and select "Show Column in Table".

That should do the trick!

Monday, November 5, 2018 - 11:23:47 AM - Shaun Back To Top (78158)

It doesn't seem possible to output the sql_text column using your example. Although you can select it in the wizard, the column doesn't appear in the live data. So I can apply the duration filter, but can't grab the sql text that caused the long running lock.  Your screenshot shows sql_text in the details section at the bottom, that doesn't appear for me. Is this a SSMS issue or a SQL Server issue, or am I just missing something?

I'm running 2016 SP1-CU8.


get free sql tips
agree to terms