How To: XEvents as Profiler

A common excuse for not delving into extended events seems to be the jump from Profiler to extended events.  There appears to be an inherent fear with how to use extended events.  In addition to that fear, is the fear that extended events does not do what Profiler can do so easily.

Today, I would like to share a short tutorial on how to use extended events as if it were a profiling session.  I am only going to show one of the many possible means to profile via extended events.  Just understand that this means you have flexibility and a way to do more with this simple example than what I am going to share.

Requirement

You need to capture TSQL queries that are occurring against your instance in order to try and determine the code that is being passed to SQL Server from the application.

How-To

Rather than jump to Profiler, you are going to need to jump to a fresh, clean query window in SSMS.  Just like in Profiler, you will want to capture certain “statement” oriented events.  For this session, I want to start with sp_statement_starting and sql_statement_starting.  I am not going to use the _completed forms of those events because I want to capture as much as I can – without capturing too much noise.  Sometimes, there may be a _starting without a coordinated _completed and that could throw a wrench in the works.

With the basic information in hand, we are ready to implement some of the things learned in previous XEvents posts (here and here).  Let’s go ahead and start setting up the session that will capture the information we seek.

USE master;
GO
-- Create the Event Session
IF EXISTS(SELECT * 
          FROM sys.server_event_sessions 
          WHERE name='TraceIncomingQueries')
    DROP EVENT SESSION TraceIncomingQueries 
    ON SERVER;
GO
CREATE EVENT SESSION TraceIncomingQueries
ON SERVER
ADD EVENT sqlserver.sp_statement_starting (
SET collect_object_name=(1)
ACTION(sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.client_hostname,sqlserver.client_app_name)
WHERE sqlserver.client_app_name <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'
),
ADD EVENT sqlserver.sql_statement_starting(
    ACTION(sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.client_hostname,sqlserver.client_app_name)
WHERE sqlserver.database_name='AdventureWorks2014'
AND sqlserver.client_app_name <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'
)
ADD TARGET package0.event_file(SET filename=N'C:DatabaseXETraceIncomingQueries.xel')
/* start the session */ALTER EVENT SESSION TraceIncomingQueries 
ON SERVER 
STATE = START;
GO

One caveat to consider is in the sp_statement_starting event, I have specified a specific set option be used.

SET collect_object_name=(1)

This particular option is disabled by default.  If you want to know the object_name of code that is being used (triggers, stored procedures, etc), then this option must be enabled.

I have specified the same actions to be used for each of the events I am trapping, but this is purely up to you.  If you do not wish to capture these actions, then you can remove them or add different actions as necessary.  I have also specified a different predicate for each of the events.  This is something that works so much better in XEvents than Profiler – the filtering that can be applied is much more flexible.

Once the session is started, then it is merely a matter of figuring out how to look at the data.  I generally use TSQL to parse the data from the file, but you could also fiddle with the GUI as well.  You can access that by right-clicking the event session in management studio and then selecting “Watch Live Data”.  If everything is configured properly, then you will start to see data after the first event is triggered.  From within the GUI, you can pick and choose which columns to display in your view.  You can pause the display of the session data, or you can even “erase” the data from the current view (just like in Profiler).  You can even filter, aggregate or group the data in the view from the GUI.

The use of the GUI is up to you.  I still prefer to use the script route.  With that route, here is a sample of what you may need to write in order to display the data from the session data file that has been captured.

use master;
GO
SELECT
event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    event_data.value('(event/@timestamp)[1]', 'varchar(50)') AS [TIMESTAMP],
event_data.value('(event/action[@name="database_name"]/value)[1]', 'varchar(max)') AS DBName
,event_data.value('(event/data[@name="statement"]/value)[1]', 'varchar(max)') AS SQLText
,event_data.value('(event/data[@name="object_name"]/value)[1]', 'varchar(max)') AS ObjName
,event_data.value('(event/action[@name="session_id"]/value)[1]', 'varchar(max)') AS SessionID
,event_data.value('(event/action[@name="nt_username"]/value)[1]', 'varchar(max)') AS ExecUser
,event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'varchar(max)') AS Client_HostName,
event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'varchar(max)') AS Client_AppName
FROM(
SELECT CONVERT(XML, t2.event_data) AS event_data
 FROM (
  SELECT target_data = convert(XML, target_data)
   FROM sys.dm_xe_session_targets t
    INNER JOIN sys.dm_xe_sessions s 
        ON t.event_session_address = s.address
   WHERE t.target_name = 'event_file'
    AND s.name = 'TraceIncomingQueries') cte1
   CROSS APPLY cte1.target_data.nodes('//EventFileTarget/File') FileEvent(FileTarget)
   CROSS APPLY  sys.fn_xe_file_target_read_file(FileEvent.FileTarget.value('@name', 'varchar(1000)'), NULL, NULL, NULL) t2)
    AS evts(event_data);

It really is that simple.  Now you have a means to run a Profiler-like trace on your server without the impact of Profiler.  XEvents provides the means to run a more evolved Profiler session on your data to capture things like queries from an application.  XEvents does all of this without the severe penalty of Profiler and with many additional bonuses.  Try it out and enjoy!

For more uses of Extended Events, I recommend my series of articles designed to help you learn XE little by little.

Interested in another article demonstrating the power of XE over Profiler? Check this one out!

This is the first article in the 2018 “12 Days of Christmas” series. For the full list of articles, please visit this page.