Inside the XEvent Profiler

XEvent Profiler is a new feature of SQL Server Management Studio v17.3. I applaud the SQL Server Tools Team for this feature. I feel like it will go a long way towards putting the legacy SQL Profiler to bed.

Sometimes you cannot do any better than taking it straight from the source. Here is Microsoft’s explanation of XEvent Profiler.

Unlike SQL Profiler, XEvent profiler is directly integrated into SSMS and is built on top of the scalable Extended Events technology in the SQL engine. This feature enables quick access to a live streaming view of diagnostics events on the SQL server. This view can be customized and those customizations can be shared with other SSMS users as a .viewsettings file. The session created by XE Profiler is less intrusive to the running SQL server than a similar SQL trace would be when using SQL Profiler. This session can be customized by the user as well, using the existing XE session properties UI or by TSQL.

Ever since Extended Events (XE) was released, I have struggled with the difficultly of setting up a new session. XE is very powerful but I have had to force myself to learn it and move away from Profiler, because I know it is the right thing to do, not because the feature made it easier to carry out my goals. Now, with XEvent Profiler, spawning an ad hoc session to look at a problem is a lot simpler. In fact, I think this new method is easier than SQL Profiler ever was. The direct SSMS integration is my favorite piece. So, let’s take a tour of the feature.

How to use XEvent Profiler

XEvent Profiler is an SSMS feature, not a SQL Server feature. XEvent Profiler should work with any SQL Server version that has the Extended Events feature. Although, admittedly, I only tested it on 2016 and 2017. The important part is that you will need SSMS v17.3 or higher, download it here and upgrade now.

In SSMS’s object explorer there is now an XE Profiler node, at the bottom [1]. After it is used on an instance for the first time, the Extended Events sessions are found under Management > Extended Events > Sessions [2]. Unlike typical XE sessions [3], there are no targets defined. Instead, they will use a hidden stream target which instantiates when the Watch Live Data feature starts.

To begin the session(s), you simply double-click the Standard or TSQL profiler. Both sessions are light weight, compared to SQL Profiler, but the TSQL is a reduced scope when compared to the Standard. If you choose to use the Standard, there is no need for the TSQL because all the same events are captured in Standard.

Immediately after you double-click on a session, you might see the Quick Start Session window. For me, this window was never up long enough for me to even see the words. I snagged this screenshot from BOL so you can see what it could look like.

Then, the Watch Live Data window opens within SSMS. I like the live data interface. It is important, however, to customize the view. There is a lot of data which is not displayed by default.

Right click on one of the column headers and you will find the Column Chooser. This is where you can add more information and remove columns that you do not care to see now. The column must be displayed for you to configure filters on it. Below, I am adding duration to the view so that I can sort or filter on it. Also, I will remove some clutter from the results with a couple more filters.

Any customizations you make to the live data view are saved as part of the local user’s .viewsettings file. This keeps your preferences saved and allows for sharing the file with other users.

Kicking the tires on XEvent Profiler

What is inside

XE Profiler is a wrapper for the Extended Events feature. This makes it easy to pop the hood and look around. Scripting out the Standard session will generate this session definition.

CREATE EVENT SESSION [QuickSessionStandard]
ON SERVER
ADD EVENT sqlserver.attention
( ACTION
(
package0.event_sequence
,sqlserver.client_app_name
,sqlserver.client_pid
,sqlserver.database_id
,sqlserver.nt_username
,sqlserver.query_hash
,sqlserver.server_principal_name
,sqlserver.session_id
)
WHERE ( [package0].[equal_boolean]([sqlserver].[is_system], ( 0 )))
)
,ADD EVENT sqlserver.existing_connection
( SET collect_options_text = ( 1 )
ACTION
(
package0.event_sequence
,sqlserver.client_app_name
,sqlserver.client_pid
,sqlserver.nt_username
,sqlserver.server_principal_name
,sqlserver.session_id
)
)
,ADD EVENT sqlserver.login
( SET collect_options_text = ( 1 )
ACTION
(
package0.event_sequence
,sqlserver.client_app_name
,sqlserver.client_pid
,sqlserver.nt_username
,sqlserver.server_principal_name
,sqlserver.session_id
)
)
,ADD EVENT sqlserver.logout
( ACTION
(
package0.event_sequence
,sqlserver.client_app_name
,sqlserver.client_pid
,sqlserver.nt_username
,sqlserver.server_principal_name
,sqlserver.session_id
)
)
,ADD EVENT sqlserver.rpc_completed
( ACTION
(
package0.event_sequence
,sqlserver.client_app_name
,sqlserver.client_pid
,sqlserver.database_id
,sqlserver.nt_username
,sqlserver.query_hash
,sqlserver.server_principal_name
,sqlserver.session_id
)
WHERE ( [package0].[equal_boolean]([sqlserver].[is_system], ( 0 )))
)
,ADD EVENT sqlserver.sql_batch_completed
( ACTION
(
package0.event_sequence
,sqlserver.client_app_name
,sqlserver.client_pid
,sqlserver.database_id
,sqlserver.nt_username
,sqlserver.query_hash
,sqlserver.server_principal_name
,sqlserver.session_id
)
WHERE ( [package0].[equal_boolean]([sqlserver].[is_system], ( 0 )))
)
,ADD EVENT sqlserver.sql_batch_starting
( ACTION
(
package0.event_sequence
,sqlserver.client_app_name
,sqlserver.client_pid
,sqlserver.database_id
,sqlserver.nt_username
,sqlserver.query_hash
,sqlserver.server_principal_name
,sqlserver.session_id
)
WHERE ( [package0].[equal_boolean]([sqlserver].[is_system], ( 0 )))
)
WITH
(
MAX_MEMORY = 8192KB
,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
,MAX_DISPATCH_LATENCY = 5 SECONDS
,MAX_EVENT_SIZE = 0KB
,MEMORY_PARTITION_MODE = PER_CPU
,TRACK_CAUSALITY = ON
,STARTUP_STATE = OFF
);
GO

The Standard session collects, connection, login/logout, and SQL batch events with a bit of filtering to omit system events. All-in-all this is not a very exhaustive XE session, which is a very good thing. I feel that this session will work well for the majority of profiler use cases but is still very light weight. It is important to minimize the observer effect on your servers.

The TSQL quick session is even lighter.

CREATE EVENT SESSION [QuickSessionTSQL]
ON SERVER
ADD EVENT sqlserver.existing_connection
( ACTION ( package0.event_sequence, sqlserver.session_id ))
,ADD EVENT sqlserver.login
( SET collect_options_text = ( 1 )
ACTION ( package0.event_sequence, sqlserver.session_id )
)
,ADD EVENT sqlserver.logout
( ACTION ( package0.event_sequence, sqlserver.session_id ))
,ADD EVENT sqlserver.rpc_starting
( ACTION ( package0.event_sequence, sqlserver.session_id )
WHERE ( [package0].[equal_boolean]([sqlserver].[is_system], ( 0 )))
)
,ADD EVENT sqlserver.sql_batch_starting
( ACTION ( package0.event_sequence, sqlserver.session_id )
WHERE ( [package0].[equal_boolean]([sqlserver].[is_system], ( 0 )))
)
WITH
(
MAX_MEMORY = 8192KB
,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
,MAX_DISPATCH_LATENCY = 5 SECONDS
,MAX_EVENT_SIZE = 0KB
,MEMORY_PARTITION_MODE = NONE
,TRACK_CAUSALITY = ON
,STARTUP_STATE = OFF
);
GO

Customization

The quick sessions, after created for the first time on an instance, can be modified via T-SQL or the XE Session wizard in SSMS. The sessions do not start-up when the instance does, double clicking the quick session from XE Profiler will do that. However, the sessions are not recreated, if they already exist. This means that you can modify the session for long-term use and maintain the convenience of the double-click start-up and stream capabilities of XE Profiler.

Aside from the sessions themselves and the live data view columns / filters, there does not seem to be much more we can play around with. I considered custom queries but the live data view uses an undocumented in-memory streaming target. The T-SQL to query that target exists but is undocumented and I am not familiar with it. You can, however, use the  QueryableXEventData class from the Microsoft.SqlServer.XEvent.Linq DLL but I cannot think of a good use case for it.

Take aways

  • XEvent Profiler just made using Extended Events easier.
  • Learn Extended Events.
  • Stop using SQL Profiler.

Leave a Reply