Redaguoti

Bendrinti naudojant

Quickstart: Extended Events

  • Straipsnis

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

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.

Tip

For more information about Extended Events in Azure SQL Database, including code samples, see Extended Events in Azure SQL Database and Azure SQL Managed Instance.

Prerequisites

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 CREATE ANY EVENT SESSION (introduced in SQL Server 2022), or ALTER ANY EVENT SESSION server permission.
  • Additionally, when using SSMS and for viewing sessions that are created, the login requires the permission VIEW SERVER PERFORMANCE STATE.

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 the next 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:

  • General
  • Events
  • Data Storage
  • Advanced

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.

  1. Connect to a database engine instance. Extended Events are supported starting with SQL Server 2014 (12.x), in Azure SQL Database, and Azure SQL Managed Instance.

  2. 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.

  3. 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.

    Tip

    In these tutorial steps, don't hit OK until you've advanced through all four pages: General, Events, Data Storage, and Advanced.

  4. 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.

  5. Select the Events page.

  6. In the Event library area, in the dropdown list, choose Event names only.

    • Type 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 sql_statement_completed.
    • Select the right arrow button > to move the event to the Selected events box.

  7. Staying on the Events page, select the Configure button. This opens the Event configuration options box for the selected events.

    Screenshot of New Session > Events > Select from the events library. sql_statement_completed is selected. The configure button is the next action.

  8. Select the Filter (Predicate) tab. Next, select the new filter line that says Click here to add a clause. In this tutorial, will configure this filter (also known as a predicate) to capture all SELECT statements with a HAVING clause.

  9. In the Field dropdown list, choose sqlserver.sql_text.

    • For Operator, choose like_i_sql_unicode_string. Here, i in the name of operator means case-insensitive.
    • For Value, type %SELECT%HAVING%. Here, percent signs (%) are wildcards standing for any character string.

    Note

    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.

    Screenshot of New Session > Events > Configure > Filter (Predicate) > Field.

  10. Select the Data Storage page.

  11. In the Targets area, select the new Target Type line that says Click here to add a target. In this tutorial, we'll write our captured extended events data to an event file. This means the event data is stored in a file that we can open and view later. Starting with SQL Server 2019 (15.x), event data can also be written to be stored in Azure Storage, the default in Azure SQL.

    • In the Type dropdown list, choose event_file.

  12. 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 C:\temp\YourSession_Target.xel.

    Screenshot of New Session > Data Storage > Targets > Type > event_file.

  13. Select the Advanced page. By default configuration, this .xel target of the extended event session should have minimal or no impact on server performance, but these settings on the Advanced page can be used to increase or decrease the resources and latency.

    Screenshot of New Session > Advanced.

  14. Select the OK button at the bottom to create this event session.

  15. Back in the SSMS Object Explorer, open or refresh the Sessions folder, and see the new node for the YourSession extended events session. The session isn't started yet. In this tutorial, we'll 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 SSMS, 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's the generated CREATE EVENT SESSION T-SQL statement for YourSession:

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');
GO

Note

In Azure SQL Database, use ON DATABASE instead of ON SERVER.

Conditional DROP of the event session

Before the 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:

  1. In Object Explorer, right-click your event session node, and then select Start Session.
  2. While connected to the same server (or the same database in Azure SQL Database) where you created the event session, run the following SELECT...HAVING statement a couple of times. Consider changing the value in the HAVING clause for each run, toggling between 2 and 3. This enables you to see the differences in the results.
  3. Right-click your session node, and select Stop Session.
  4. 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
INNER 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's the example output from the preceding SELECT...HAVING.

/* 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.

Note

The 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 following example, the numeric part is _0_131085363367310000.

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 event_data_XML column.

  • Copy the XML string from a cell in the event_data column. 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 following XML is edited 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 SELECT statement.

<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
        INNER 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 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, for example, package0.event_counter. 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.

Term Description
event session A construct centered around one or more events, plus supporting items like actions are targets. The CREATE EVENT SESSION statement creates each event session. You can ALTER an event session to start and stop it at will.

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.
event A specific occurrence in the system that is watched for by an active event session.

For example, the sql_statement_completed event represents the moment that any given T-SQL statement completes. The event can report its duration and other data.
target An item that receives the output data from a captured event. The target displays the data to you.

Examples include the event_file target used earlier in this quick start, and the ring_buffer target that keeps the most recent events in memory.
Any kind of target can be used for any event session. For details, see Targets for Extended Events.
action 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.
predicate, or 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 sql_statement_completed event occurrences where the T-SQL statement contained the string HAVING.
package 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 started and completed events.
Fields appropriate for the events are also in the package with the events. Most targets are in package0 and are used with events from many other packages.

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:

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:

  • AlwaysOn_health
  • system_health
  • telemetry_events

PowerShell provider

You can manage Extended Events by using the SQL Server PowerShell provider. For more information, see Use the PowerShell Provider for Extended Events.

System views

The system views for Extended Events include:

  • Catalog views: for information about event sessions 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 SESSION clauses
    • 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
  • CONTROL SERVER

The following 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 all lower permissions like
-- '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 (
    'CREATE ANY EVENT SESSION',
    'ALTER ANY EVENT SESSION',
    'VIEW SERVER PERFORMANCE STATE',
    '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';

HAS_PERMS_BY_NAME function

The following 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 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;

Related content