Trace Query in Extended Events

Tracing a query is a common task for a DBA. The methods employed are pretty varied. I seem to encounter a new method here and there throughout my journeys and projects.

One method is surprisingly easy and I don’t recall ever paying it any attention until recently. Upon discovering this method, I was appalled that there is still no equivalent method within Extended Events (XE). In this article, I am going to share that method and a viable equivalent for XE until an appropriate GUI integration is created for SSMS.

Query Tracing

First things first, how do we find this supremely easy tracing method? When do we want to use it? Let’s answer that second question first. This is a method we will want to use whenever we have a query that we have just been handed and we want/need to trace the query to figure out things such as resource utilization. To get to this method, we simply right click in the query pane and select “Trace Query in SQL Server Profiler” from the context menu. The following image illustrates that menu option.

After selecting that menu option to trace the query, the next step is amazingly simple too – just execute the query. That is fabulous – if you want to use a tool as inefficient and outdated as Profiler. How do we do this in XEvents? First, we need to capture a bit more detail from this Profiler style trace.

Let’s delve into the properties for that trace session we just started (from the previous image).

Once we have the properties open, the next step is to click the “Column Filters…” button as shown in the following image.

After clicking the “Column Filters…” button, a new window will open, revealing any filters that were defined for that query we wanted to trace. In this case, the SPID for the query window is transferred to the query trace. Thus, in theory, this Profiler trace will only capture data related to the SPID in question. With the filter in hand, and also noting the events being trapped from the properties window, we have adequate information to create an XEvent session to perform the same functionality.

We can easily setup a session in XE through the GUI using the Standard template shown here:

And then modify it to include the missing events as shown here:

Or, one could use the easy button and take advantage of a script. The script option provides a much more robust option while also being far less repetitive than the GUI.

USE master;
GO
-- Create the Event Session
IF EXISTS ( SELECT *
FROM sys.server_event_sessions
WHERE name = N'TraceSQLMenu' )
DROP EVENT SESSION TraceSQLMenu 
    ON SERVER;
GO
EXECUTE xp_create_subdir 'C:DatabaseXE';
GO
DECLARE @SessionId VARCHAR(32) = '59'
,@SQL VARCHAR(MAX) ;
SET @SQL = 'CREATE EVENT SESSION [TraceSQLMenu]
ON SERVER
ADD EVENT sqlserver.existing_connection
(SET
 collect_options_text = (1)
 ACTION
 (
 sqlserver.session_id
   , sqlserver.sql_text
 )
 WHERE ([sqlserver].[session_id] = (' + @SessionId + '))
)
  , ADD EVENT sqlserver.login
(SET
 collect_options_text = (1)
 ACTION
 (
 sqlserver.session_id
   , sqlserver.sql_text
 )
 WHERE ([sqlserver].[session_id] = (' + @SessionId + '))
)
  , ADD EVENT sqlserver.logout
(ACTION
 (
 sqlserver.session_id
   , sqlserver.sql_text
 )
 WHERE ([sqlserver].[session_id] = (' + @SessionId + '))
)
  , ADD EVENT sqlserver.rpc_starting
(ACTION
 (
 sqlserver.session_id
   , sqlserver.sql_text
 )
 WHERE ([sqlserver].[session_id] = (' + @SessionId + '))
)
  , ADD EVENT sqlserver.sp_statement_completed
(ACTION
 (
 sqlserver.session_id
   , sqlserver.sql_text
 )
 WHERE ([sqlserver].[session_id] = (' + @SessionId + '))
)
  , ADD EVENT sqlserver.sp_statement_starting
(ACTION
 (
 sqlserver.session_id
   , sqlserver.sql_text
 )
 WHERE ([sqlserver].[session_id] = (' + @SessionId + '))
)
  , ADD EVENT sqlserver.sql_batch_starting
(ACTION
 (
 sqlserver.session_id
   , sqlserver.sql_text
 )
 WHERE ([sqlserver].[session_id] = (' + @SessionId + '))
)
  , ADD EVENT sqlserver.sql_statement_starting
(SET
 collect_statement = (1)
 ACTION
 (
 sqlserver.session_id
   , sqlserver.sql_text
 )
 WHERE ([sqlserver].[session_id] = (' + @SessionId + '))
)
ADD TARGET package0.event_file
(SET filename = N''C:DatabaseXETraceSQLMenu'')
WITH ( MAX_MEMORY = 4096 KB
,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
, MAX_DISPATCH_LATENCY = 3 SECONDS
, MAX_EVENT_SIZE = 0 KB
, MEMORY_PARTITION_MODE = NONE
, TRACK_CAUSALITY = ON
, STARTUP_STATE = ON);
ALTER EVENT SESSION TraceSQLMenu ON SERVER
STATE = START;'
PRINT @SQL;
EXECUTE (@SQL);
GO

This session is ready to roll (without a GUI access point obviously) simply by entering the SPID #, into the @SessionId variable, for the query window in question. Once the spid value is entered, and the script is executed, we can easily watch the live data for the spid in question (if we wish).

So, what happens when I need to query a different spid? That’s easy! I just change the value of the @SessionId variable to the spid in question and then run the script again. The script will drop and recreate the session with all of appropriate filters in place and pointing to the correct SPID.

Final Thoughts

The ability to quickly and easily trace a query is important to database professionals. This script provides one useful alternative to trace a specific spid similar to the method of using the context menu to create the trace within SSMS and Profiler.

This is yet another tool in the ever popular and constantly growing library of Extended Events. Are you still stuck on Profiler? Try one of these articles to help remedy that problem (here and here)

The Extended Events library has just about something for everybody. Please take the time to explore it and become more familiar with this fabulous tool!