XEvent profiler

04 Mar 2021 19914 views 0 minutes to read Contributors

XEvent Profiler

Although SQL Server Extended Events feature was introduced the first time in SQL Server 2008, as a lightweight performance monitoring feature. XEvent Profiler was launched with SQL Server Management Studio 17.3 which provides a quick and customizable live view of the Extended Events. One of the main advantages of XEvent Profiler is that it is directly integrated into SSMS and is built on top of the Extended Events technology, where SQL Profiler was not integrated with SSMS and had its own interface.

XEvent Profiler is an SSMS feature, not a SQL Server feature and is available when we are connect to SQL Server 2012 or higher versions however you need to use SSMS v17.3 to use it.

To access  XEvent profiler we need to connect to SQL instance via SSMS with minimum version 17.3.

On SSMS object explorer we can see XEvent profiler, as shown below.

As we can see above, XEvent Profiler is new and has two options:

• Standard – Displays all extended events
• TSQL – Displays the logged SQL statements

Once we double click on the Standard XEvent Profiler session, it launches a 'Quick Start Session'. This session configures an Extended Events session based on the template 'xe_Profile_Standard'. Similarly, if you click on TSQL XEvent profiler, it launches an XEvent session based on template 'xe_Profile_TSQL'.

Once a session is configured based one of the templates, we can find the predefined session templates by expanding the Management node to check all Sessions under the Extended Events node as shown below:

• QuickSessionStandard
• QuickSessionTSQL

All sessions are deployed as regular XEvent session templates.

QuickSessionStandard

QuickSessionStandard is created as a replacement for the ‘Standard’ template in Profiler. It contains generic Extended Events, so it can be a starting point. It captures all stored procedures and Transact-SQL batches that are being executed. We can use it to monitor general database server activity.

To script out the XEvent session, expand Management > Extended Events > Sessions and right click on the session and select Script Session as > CREATE To > New Query Editor Window.

CREATEEVENTSESSION [QuickSessionStandard] ONSERVER

ADDEVENTsqlserver.attention(

ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.server_principal_name,sqlserver.session_id)

WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),

ADDEVENTsqlserver.error_reported(

ACTION(package0.callstack,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)

WHERE ([severity]>=(20)OR([error_number]=(17803)OR [error_number]=(701)OR [error_number]=(802)OR [error_number]=(8645)OR [error_number]=(8651)OR [error_number]=(8657)OR [error_number]=(8902)OR [error_number]=(41354)OR [error_number]=(41355)OR [error_number]=(41367)OR [error_number]=(41384)OR [error_number]=(41336)OR [error_number]=(41309)OR [error_number]=(41312)OR [error_number]=(41313)))),

ADDEVENTsqlserver.existing_connection(SETcollect_options_text=(1)

ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_id)),

ADDEVENTsqlserver.login(SETcollect_options_text=(1)

ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_id)),

ADDEVENTsqlserver.logout(

ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_id)),

ADDEVENTsqlserver.rpc_completed(

ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.server_principal_name,sqlserver.session_id)

WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),

ADDEVENTsqlserver.sql_batch_completed(

ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.server_principal_name,sqlserver.session_id)

WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),

ADDEVENTsqlserver.sql_batch_starting(

ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.server_principal_name,sqlserver.session_id)

WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0))))

WITH (MAX_MEMORY=16384 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)

GO

The QuickSessionStandard script is shown below.

As we can see the Standard session collects all stored procedures and T-SQL batches running to monitor the general database activities of the SQL Server instance, by collecting XEvents such as login, logout, rpc_completed, sql_batch_completed and sql_batch_starting.

QuickSessionTSQL

QuickSessionTSQL is used to capture all submitted T-SQL statements by collection XEvents such as login, logout, rpc_starting, and sql_batch_starting. It is very lightweight XEvent session. It is created as replacement for the ‘TSQL’ template in Profiler.

Session Wizard

We can also use these templates from the New Session Wizard under the Extended Events node in SSMS. Just right click on the session and select New Session Wizard.

Working with XEvent Profiler

To use XEvent profiler, double-click on the TSQL XEvent Profiler template. For example, if I click on the QuickSessionStandard session, it quickly opens up the live data window.

This live data window displays all logged T-SQL statements currently running as per the standard session definition, with a detailed view of each selected event as shown below.

Similarly, if we launch TSQL XEvent Profiler, the default view looks as shown below.

By default a live data window shows the columns predefined, however, it is important to customize the view as per your requirements. It doesn't show all columns by default.

To get more columns, right-click on any column and select Choose Columns.

It displays a list of columns that can be selected.

Move the desired columns to the right from the Available Columns to the Selected Columns and click OK.

Suppose I want database_id in my output, I move them to the right from Available Columns.

Filters

The live data wizard also provides options to Filter the results. Right click on a column and select Filter by this Value.

In this window, we can select a Filter based on the time range along with other filter values. Select the field from the drop-down and enter the search criteria. We can also put multiple conditions and put logical operation AND/OR to filter values.

 

Bookmarks

We can setup Bookmarks on statements which we want to review later using the “Toggle Bookmark” option from the Toolbar or Context menu. We can navigate between Bookmarks using the “Previous Bookmark” and “Next Bookmark” options. We can clear the Bookmarks using the “Clear All Bookmarks” option.