Quickstart: Extended Events
Extended Events is a lightweight performance monitoring feature that enables users to collect data to monitor and troubleshoot problems. By using Extended Events, you can see details of the database engine internal operations that are relevant for performance monitoring and troubleshooting purposes. To learn more about extended events, see Extended Events overview.
This article aims to help SQL administrators, engineers, and developers who are new to Extended Events, and who want to start using it and see event data in just a few minutes.
Extended Events is also known as XEvents, and sometimes just XE.
After reading this article, you can:
- See how to create an event session in SQL Server Management Studio (SSMS), with example screenshots
- Correlate screenshots to equivalent Transact-SQL statements
- Understand in detail the terms and concepts behind the SSMS user interface and XEvents T-SQL statements
- Learn how to test your event session
- Understand session results, including:
- Available options for result storage
- Processed versus raw results
- Tools for viewing the results in different ways and on different time scales
- See how you can search for and discover all the available events
- Understand the relationships among Extended Events system views
For more information about Extended Events in Azure SQL Database, including code samples, see Extended Events in SQL Database.
To get started, you need to:
- Download SQL Server Management Studio (SSMS). We recommend using a recent version of SSMS with the latest improvements and fixes.
- Ensure that your account has the
ALTER ANY EVENT SESSIONserver permission.
Details about security and permissions related to Extended Events are available at the end of this article in the Appendix.
Extended Events in SSMS
SSMS provides a fully functional user interface (UI) for Extended Events. Many scenarios can be accomplished using this UI, without having to use T-SQL or dynamic management views (DMVs).
In this section you can see the UI steps to create an Extended Events session, and to see the data it reports. After going through the steps hands-on or reviewing them in this article, you can read about the concepts involved in the steps for a deeper understanding.
Create an event session in SSMS
When you create an Extended Events session, you tell the system:
- Which events you're interested in
- How you want the system to report the data to you
The demonstration opens the New Session dialog, shows how to use its four pages, named:
- Data Storage
The text and supporting screenshots can be slightly different in your version of SSMS, but should still be relevant for the explanation of basic concepts.
Connect to a database engine instance. Extended Events are supported starting with SQL Server 2014, in Azure SQL Database, and Azure SQL Managed Instance.
In Object Explorer, select Management > Extended Events. In Azure SQL Database, event sessions are database-scoped, so the Extended Events option is found under each database, not under Management. Right-click on the Sessions folder and select New Session. The New Session dialog is preferable to New Session Wizard, although the two are similar.
In the upper-left, select the General page. Then type
YourSession, or any name you like, into the Session name text box. Don't select OK yet, because you still need to enter some details on other pages.
In the upper-left, select the Events page.
In the Event library area, in the dropdown list, choose Event names only.
sql_statement_into the text box. This filters the list to show only events with
sql_statement_in the name.
- Scroll and select the event named
- Select the right arrow button
>to move the event to the Selected events box.
Staying on the Events page, select the Configure button in the upper-right. This opens the Event configuration options box for the selected events.
Select the Filter (Predicate) tab. Next, select Select here to add a clause. We configure this filter (also known as predicate) to capture all
SELECTstatements that have a
In the Field dropdown list, choose
- For Operator, choose
iin the name of operator means case-insensitive.
- For Value, type
%SELECT%HAVING%. Here, percent signs are wildcards standing for any character string.
In the two-part name of the field, sqlserver is the package name and sql_text is the field name. The event we chose earlier, sql_statement_completed, must be in the same package as the field we choose.
- For Operator, choose
In the upper-left, select the Data Storage page.
In the Targets area, select Select here to add a target.
- In the Type dropdown list, choose
event_file. This means the event data is stored in a file that we can open and view later. In Azure SQL Database and Azure SQL Managed Instance, event data is stored in Azure Storage blobs.
Starting with SQL Server 2019, you can use Azure Blob Storage in an
event_filetarget in SQL Server.
- In the Type dropdown list, choose
In the Properties area, type in the full path and file name into the File name on server text box. You can also use the Browse button. The file name extension must be
xel. In our example, we used
In the upper-left, select the Advanced page. Reduce Maximum dispatch latency to 3 seconds.
Select the OK button at the bottom to create this event session.
Back in Object Explorer, open or refresh the Sessions folder, and see the new node for
YourSession. The session isn't started yet. You start it later.
Edit an event session in SSMS
In the SSMS Object Explorer, you can edit your event session by right-clicking its node, and then selecting Properties. The same multi-page dialog is displayed.
Create an event session using T-SQL
In the SSMS Extended Events UI, you can generate a T-SQL script to create your event session as follows:
- Right-click on the event session node, then select Script Session as > CREATE to > Clipboard.
- Paste into any text editor.
Here is the generated
CREATE EVENT SESSION T-SQL statement for
CREATE EVENT SESSION [YourSession] ON SERVER -- For SQL Server and Azure SQL Managed Instance -- ON DATABASE -- For Azure SQL Database ADD EVENT sqlserver.sql_statement_completed ( ACTION(sqlserver.sql_text) WHERE ( [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text], N'%SELECT%HAVING%') ) ) ADD TARGET package0.event_file (SET filename = N'C:\Temp\YourSession_Target.xel', max_file_size = (2), max_rollover_files = (2) ) WITH ( MAX_MEMORY = 2048 KB, EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 3 SECONDS, MAX_EVENT_SIZE = 0 KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF ); GO
Conditional DROP of the event session
CREATE EVENT SESSION statement, you can conditionally execute a
DROP EVENT SESSION statement, in case a session with the same name already exists. This deletes the existing session. Without this, attempting to create a session with the same name causes an error.
IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'YourSession') BEGIN DROP EVENT SESSION YourSession ON SERVER; END GO
Start and stop the event session using T-SQL
When you create an event session, the default is for it to not start running automatically. You can start or stop your event session anytime by using the following
ALTER EVENT SESSION T-SQL statement.
ALTER EVENT SESSION [YourSession] ON SERVER STATE = START; -- STOP;
You have the option of configuring the event session to automatically start when the database engine instance is started. See the
STARTUP STATE = ON keyword in
CREATE EVENT SESSION.
SSMS UI offers a corresponding checkbox, Start the event session at server startup, on the New Session > General page.
Test an event session
Test your event session with these steps:
- In Object Explorer, right-click your event session node, and then select Start Session.
- While connected to the same server (or the same database in Azure SQL Database) where you created the event session, run the following
SELECT...HAVINGstatement a couple of times. Consider changing the value in the
HAVINGclause for each run, toggling between 2 and 3. This enables you to see the differences in the results.
- Right-click your session node, and select Stop Session.
- Read the next subsection about how to SELECT and view the results.
SELECT c.name, Count(*) AS [Count-Per-Column-Repeated-Name] FROM sys.syscolumns AS c JOIN sys.sysobjects AS o ON o.id = c.id WHERE o.type = 'V' AND c.name like '%event%' GROUP BY c.name HAVING Count(*) >= 3 --2 -- Try both values during session. ORDER BY c.name;
For completeness, here is the example output from the preceding
/* Approximate output, 6 rows, all HAVING Count >= 3: name Count-Per-Column-Repeated-Name --------------------- ------------------------------ event_group_type 4 event_group_type_desc 4 event_session_address 5 event_session_id 5 is_trigger_event 4 trace_event_id 3 */
View event session data as XML
In a query window in SSMS, run the following
SELECT statement to see the event data captured by your session. Each row represents one event occurrence. The
CAST(... AS xml) changes the data type of the column from nvarchar to xml. This lets you select the column value, to open it in a new window for easier reading.
event_file target always inserts a numeric part in the
xel file name. Before you can run the following query, you must copy the actual full name of the
xel file that includes this numeric part, and paste it into the
SELECT statement. In the example below, the numeric part is
SELECT object_name, file_name, file_offset, event_data, 'CLICK_NEXT_CELL_TO_BROWSE_XML RESULTS!' AS [CLICK_NEXT_CELL_TO_BROWSE_XML_RESULTS], CAST(event_data AS xml) AS [event_data_XML] -- TODO: In the SSMS results grid, click this XML cell FROM sys.fn_xe_file_target_read_file( 'C:\Temp\YourSession_Target_0_131085363367310000.xel', null, null, null );
This query provides two ways to view the full results of any given event row:
Run the SELECT in SSMS, and then select a cell in the
Copy the XML string from a cell in the
event_datacolumn. Paste into any text editor like Notepad, and save the file with extension
xml. Then open the file in a browser or an editor capable of displaying XML data.
Event data in XML
Next we see part of the results, which are in XML format. The XML here is edited here for brevity.
<data name="row_count"> displays a value of
6, which matches our six result rows displayed earlier. And we can see the whole
<event name="sql_statement_completed" package="sqlserver" timestamp="2016-05-24T04:06:08.997Z"> <data name="duration"> <value>111021</value> </data> <data name="cpu_time"> <value>109000</value> </data> <data name="physical_reads"> <value>0</value> </data> <data name="last_row_count"> <value>6</value> </data> <data name="offset"> <value>0</value> </data> <data name="offset_end"> <value>584</value> </data> <data name="statement"> <value>SELECT c.name, Count(*) AS [Count-Per-Column-Repeated-Name] FROM sys.syscolumns AS c JOIN sys.sysobjects AS o ON o.id = c.id WHERE o.type = 'V' AND c.name like '%event%' GROUP BY c.name HAVING Count(*) >= 3 --2 -- Try both values during session. ORDER BY c.name</value> </data> </event>
Display event session data in SSMS
There are several advanced features in the SSMS UI you can use to view the data that is captured by an event session. For more information, see View event data in SQL Server Management Studio.
You start with context menu options labeled View Target Data and Watch Live Data.
View target data
In the SSMS Object Explorer, you can right-click the target node that is under your event session node. In the context menu, select View Target Data. SSMS displays the data.
The display isn't updated as new events occur in a session. But you can select View Target Data again.
Watch live data
In the SSMS Object Explorer, you can right-click your event session node. In the context menu, select Watch Live Data. SSMS displays incoming data as it continues to arrive in real time.
Terms and concepts in Extended Events
The following table lists the terms used for Extended Events, and describes their meanings.
||A construct centered around one or more events, plus supporting items like actions are targets. The
An event session is sometimes referred to as just a session, when the context clarifies it means event session.
Further details about event sessions are described in: Extended Events sessions.
||A specific occurrence in the system that is watched for by an active event session.
For example, the
||An item that receives the output data from a captured event. The target displays the data to you.
Examples include the
Any kind of target can be used for any event session. For details, see Targets for Extended Events.
||A field known to the event. Data from the field is sent to the target. The action field is closely related to the predicate filter.|
||A test of data in an event field, used so that only an interesting subset of event occurrences are sent to the target.
For example, a filter could include only those
||A name qualifier attached to each item in a set of items that centers around a core of events.
For example, a package can have events about T-SQL text. One event could be about all the T-SQL in a batch. Meanwhile another narrower event is about individual T-SQL statements. Further, for any one T-SQL statement, there are
Fields appropriate for the events are also in the package with the events. Most targets are in
Extended Event scenarios and usage details
There are numerous scenarios for using Extended Events to monitor and troubleshoot the database engine and query workloads. The following articles provide examples using lock-related scenarios:
- Find the Objects That Have the Most Locks Taken on Them
- This scenario uses the histogram target, which processes the raw event data before displaying it to you in a summarized (bucketized) form.
- Determine Which Queries Are Holding Locks
- This scenario uses the pair_matching target, where the pair of events is
- This scenario uses the pair_matching target, where the pair of events is
How to discover events available in packages
The following query returns a row for each available event whose name contains the three character string
sql. You can edit the
LIKE clause to search for different event names. The result set also identifies the package that contains the event.
SELECT -- Find an event you want. p.name AS [Package-Name], o.object_type, o.name AS [Object-Name], o.description AS [Object-Descr], p.guid AS [Package-Guid] FROM sys.dm_xe_packages AS p INNER JOIN sys.dm_xe_objects AS o ON p.guid = o.package_guid WHERE o.object_type = 'event' --'action' --'target' AND p.name LIKE '%' AND o.name LIKE '%sql%' ORDER BY p.name, o.object_type, o.name;
The following result example shows the returned row, pivoted here into the format of
column name = value. The data is from the
sql_statement_completed event that was used in the preceding example steps. The description of the object (an event, in this example) serves as a documentation string.
Package-Name = sqlserver object_type = event Object-Name = sql_statement_completed Object-Descr = Occurs when a Transact-SQL statement has completed. Package-Guid = 655FD93F-3364-40D5-B2BA-330F7FFB6491
Find events using SSMS UI
Another option for finding events by name is to use the New Session > Events > Event library dialog that is shown in a preceding screenshot. You can type a partial event name and find all matching event names.
SQL Trace event classes
A description of using extended events with SQL Trace event classes and columns is available at: View the Extended Events Equivalents to SQL Trace Event Classes.
Event Tracing for Windows (ETW)
Descriptions of using extended events with Event Tracing for Windows (ETW) are available at:
System event sessions
In SQL Server and Azure SQL Managed Instance, several system event sessions are created by default and configured to start when the database engine is started. Like most event sessions, they consume a small amount of resources and don't materially affect workload performance. Microsoft recommends that these sessions remain enabled and running. The health sessions, particularly the system_health session, are often useful for monitoring and troubleshooting.
You can see these event sessions in the SSMS Object Explorer under Management > Extended Events > Sessions. For example, in SQL Server, these system event sessions are:
You can manage Extended Events by using the SQL Server PowerShell provider. For more information, see Use the PowerShell Provider for Extended Events.
The system views for Extended Events include:
- Catalog views: for information about event sessions that have been defined by
CREATE EVENT SESSION.
- Dynamic management views (DMVs): for information about active (started) event sessions.
SELECTs and JOINs From System Views for Extended Events in SQL Server provides information about:
- How to join the views
- Several useful queries based on these views
- The correlation between:
- View columns
CREATE EVENT SESSIONclauses
- The SSMS UI
Appendix: Queries to find Extended Event permission holders
The permissions mentioned in this article are:
ALTER ANY EVENT SESSION
VIEW SERVER STATE
SELECT...UNION ALL statement returns rows that show who has the necessary permissions for creating event sessions and querying the system catalog views for Extended Events.
-- Ascertain who has the permissions listed in the ON clause. -- 'CONTROL SERVER' permission includes the permissions -- 'ALTER ANY EVENT SESSION' and 'VIEW SERVER STATE'. SELECT 'Owner-is-Principal' AS [Type-That-Owns-Permission], NULL AS [Role-Name], prin.name AS [Owner-Name], PERM.permission_name COLLATE Latin1_General_CI_AS_KS_WS AS [Permission-Name] FROM sys.server_permissions AS PERM INNER JOIN sys.server_principals AS prin ON prin.principal_id = PERM.grantee_principal_id WHERE PERM.permission_name IN ( 'ALTER ANY EVENT SESSION', 'VIEW SERVER STATE', 'CONTROL SERVER' ) UNION ALL -- Plus check for members of the 'sysadmin' fixed server role, -- because 'sysadmin' includes the 'CONTROL SERVER' permission. SELECT 'Owner-is-Role', prin.name, -- [Role-Name] CAST((IsNull(pri2.name, N'No members')) AS NVARCHAR(128)), NULL FROM sys.server_role_members AS rolm RIGHT JOIN sys.server_principals AS prin ON prin.principal_id = rolm.role_principal_id LEFT JOIN sys.server_principals AS pri2 ON rolm.member_principal_id = pri2.principal_id WHERE prin.name = 'sysadmin';
SELECT statement reports your permissions. It relies on the built-in function HAS_PERMS_BY_NAME.
Further, if you have the authority to temporarily impersonate other logins, you can uncomment the EXECUTE AS LOGIN and
REVERT statements, to see if other logins hold the
ALTER ANY EVENT SESSION permission.
--EXECUTE AS LOGIN = 'LoginNameHere'; SELECT HAS_PERMS_BY_NAME(NULL, NULL, 'ALTER ANY EVENT SESSION'); --REVERT;