When we have a performance problem in the database, the first thing we do is to list current queries using dmws.
You can find a nice query for this process in the article “How to See Current Queries On SQL Server“. You can use this query to get an idea of the source of the performance problem. See the Wait_type section to see why the queries are waiting. A good database expert usually understands the cause of the problem by using this query and can produce solutions.
But sometimes you may want to identify long-running queries and improve these queries one by one. At this point, we’ll identify long-running queries using Extended Events.
On SSMS, right click on Management-> Extended Events -> Session and click on the New Session Wizard.
In the incoming screen, we give a name to the session as follows. I do not select “Start the event session at server startup”. Because I don’t want this session to start automatically when the server starts. Click Next to proceed. On the next screen, select “Do not use template” and click next.
In the screen that appears, write “completed” in the “Event Library” section, and select the following events by double-clicking.
The selected events will be listed in the selected events section. Click Next to proceed.
On the next screen, select the following options.
- Client hostname
- Client app name
- Database name
- Sql text
- User name
On the next screen we will create the filter as below.
The “Duration> 1000” condition means that we want to capture queries that take longer than 1 second.
You may not see every condition in here, but you can see every condition after you create the extended event session.
You can filter by the following way after you create Extended Event Session.
Right-click on Session and click Properties. Then add filters by clicking Configure on Events.
If you want to create a filter after you create the Extended Events Session, you must set a separate filter for each of the selected events.
In this example you should create duration filter for the below events:
- rpc_complered
- sp_statement_completed
- sql_batch_completed
- sql_statement_completed
Go to the sp_statement_completed tab. As you seen the filter didnt created for this event. If you want you must add.
On the next screens, click next next and finish to complete the process. Then start the session as follows.
Right-click on the session and click “Watch live data”to see the queries that take longer than 1 second.
If you want to filter in the result set, you can do this by right-clicking in the upper-left corner by clicking “Filter by this Value”.
