Collecting Poor Performing Queries And Storing Them For Analysis (Using Extended Events)

In my previous article, I have illustrated the way to collect poor performing queries using profiler. However, using SQL Server Profiler to trace poor performing queries will consume lot of server resources and slowdowns the query executions.

So it is not advisable to keep running the profiler for long time like hours or days. The better option is to use the Extend Events. Here I will illustrate collecting poor performing queries using extended events and store them for analysis.

Creating & Setting Up an Extended Events Session

(A) Creating A New Session

1. In SQL Server Management Studio, go to Object Explorer > Management > Extended Events > Sessions.
   
2. Right-click the Sessions folder and from the right-click menu select New Session...
3. In the New Session pop-up window, select General from the left panel and enter Session Name. I have named it LongRunningQuery.
   

(B) Add Events & Actions

1. Now, choose Events page from the left panel. In the Event Library, search box, type in sql_stat. From the filtered events, select sql_statement_completed event and click the right arrow. The sql_statement_completed event is now listed under the Selected events list in the right.
   
2. Then, select the Configure button at the right top, above the selected events. The configuration section will emerge from the right side.
3. Now, from the Global Fields (Actions), select the actions by checking the check box. For this illustration I’ve selected, database_name and sql_text. If needed you can select more events.
   

(C) Add Filters

Now, go to the Filter(predicate) panel. Click inside the filter panel to add a filter clause. In Field, select duration. Under operator select >=. Under Value enter 1000. This filter clause will track and collect only the queries which are running for 1000 Milliseconds (1 second) or higher.

(D) Set Storage Location

1. You need to set a location and method to store the collected action and events. So, choose the Data Storage page from the left panel. In the main panel, under Type, select event_file. Select a folder and file name in File name on server field. Change the Maximum file size if needed. By default the maximum file size will be 1 GB. For this illustration I’ve changed it to 10 MB.
   
2. Now, go to the Advanced page. Change the event retention mode to Multiple event loss. Enter Maximum dispatch latency as 1 second. Change the Maximum event size
   

(E) Save The Session

1. Finally, press the OK button.
2. To check whether the session is created properly, go to Object Explorer > Management > Extended Events > Sessions. You will see the new session LongRunningQuery. You can edit the session by right-clicking and select edit in right-click menu.
   

Collecting Poor Queries

(A) Starting the Extended Event Session

By default, the session will be in inactive state. Follow the below steps to start the session and track the events. To start the xEvent session, right-click the session LongRunningQuery and select Start Session in the right-click menu. The session will start collecting the events and store them in the location you have set.

(B) Viewing Collected Queries

To see the poor performing queries collected by the session, double-click on the event file package0.event_file underneath the session. A query window will open with the list of tracked events. On selecting an event, you can see the details of the query in the result window.

(C) Stop Collecting

Once you are done with the poor query collection, disable the session to stop collecting. To disable the session and to stop it from collecting the data, right-click the session name and select Stop Session.

Next Step

• Steps by step illustration to capture only the poor performing stored procedures using extended events.