{"id":92420,"date":"2021-09-14T17:08:02","date_gmt":"2021-09-14T17:08:02","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=92420"},"modified":"2026-05-08T15:14:17","modified_gmt":"2026-05-08T15:14:17","slug":"extended-events-data-collection","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/extended-events-data-collection\/","title":{"rendered":"SQL Server Extended Events: Creating Sessions, Events, Actions, Filters, and Targets"},"content":{"rendered":"SQL Server Extended Events (often shortened to XEvents) is the modern diagnostic and monitoring framework that replaces SQL Trace and SQL Server Profiler. It’s lower-overhead, more flexible, and the only one Microsoft actively develops since SQL Server 2008. <\/b>\n\nEvery Extended Events session is built from the same five components: events (what to capture – deadlocks, long queries, blocking, memory-grants, hundreds of others), actions (additional data to collect per event – session ID, client app name, stack), filters (predicates to limit what’s recorded so overhead stays manageable), targets (where the data goes – ring buffer in memory, event_file on disk, histogram, pair matching), and options (buffer memory, dispatch latency, event retention). <\/b>\n\nThis article walks through each building block in order, with the T-SQL syntax for creating and starting a session, and finishes with the XML-shred query pattern for reading the collected data back out of an event_file target. Use it as either a step-by-step introduction or a reference when you know which piece you need to build next.<\/b>\n\nExtended Events are an excellent way to collect data about a SQL Server that provides a vast array of events that can be used for performance monitoring, troubleshooting, or auditing a server. In this article, I\u2019ll explain the building blocks of Extended Events data collection.\n\nWhile using Extended Events is not overly complex, building a reliable system to collect, parse, and store events over time without any data loss can be challenging.\n\nThis article walks through the steps to create, configure, and implement Extended Events in SQL Server, providing the prerequisite code and concepts to build an automated collection process.\n

The basics of collecting and reading extended events data<\/h2>\nThe basic steps to use Extended Events are as follows:\n
    \n \t
  1. Create an Extended Events session\n
      \n \t
    1. Add events to the Extended Events session<\/li>\n \t
    2. Add actions to the Extended Events session<\/li>\n \t
    3. Add filters<\/li>\n \t
    4. Add a target for event data<\/li>\n \t
    5. Specify options<\/li>\n<\/ol>\n<\/li>\n \t
    6. Start the Extended Events session<\/li>\n \t
    7. Periodically read data from the Extended Events session<\/li>\n<\/ol>\nExtended Events may be worked with via the SQL Server Management Studio GUI or using T-SQL. For this article, T-SQL will be used exclusively as it allows for code to be more easily shared and manipulated and is easier to copy and use than a sequence of images or videos.\n\nOnce data is read from Extended Events, it needs to be parsed and placed into a more permanent location for analytics, monitoring, and\/or posterity.\n\nThe following is a brief review of creating and managing Extended Events sessions, followed by a more detailed process that automates collecting, extracting, and managing Extended Events processes and data. This is separated into distinct sections as each will become a modular script within the Extended Events solution crafted in this article.\n

      Creating an Extended Events session<\/h2>\nCreating a new Extended Events session via T-SQL involves deciding up-front how to configure and run it. Typically, filters, events, and options will be tweaked afterwards as the resulting data is reviewed and a better idea of how it should look is envisioned.\n\nThe following is the most bare-bones way to create an Extended Events session:\n
      CREATE EVENT SESSION Sql_Server_Query_Metrics ON SERVER\n\nADD EVENT sqlserver.rpc_completed;<\/pre>\nThis script creates an event session, names it, and adds a single event. All other Extended Event options will use their default settings The session\u2019s existence can be confirmed via sys.server_event_sessions<\/em>:\n
      SELECT\n*\nFROM sys.server_event_sessions\nWHERE server_event_sessions.name = 'Sql_Server_Query_Metrics';<\/pre>\nThe results return a row with some additional details:\n\n\"Image\n\nNote the value of 0 provided by startup_state<\/em>, which indicates that the Extended Events session exists but is stopped.\n\nThe view sys.dm_xe_sessions can also be used to check for the existence of active Extended Events sessions:\n
      SELECT\n*\nFROM sys.dm_xe_sessions\nWHERE dm_xe_sessions.name = 'Sql_Server_Query_Metrics';<\/pre>\nThis time, no results are returned:\n\n\"Image\n\nThe lack of rows returned is another way to confirm that this session is not started.\n\nCreating an Extended Events session with no additional parameters is not a good idea. It is good practice to provide as much detail as possible about the target, events, actions, filters, and options.\n
      Add events to the Extended Events session<\/h2>\nAn event is a response to a specific triggered occurrence in SQL Server. It could be a deadlock, a completed query, or a user login. All of the available events in SQL Server can be browsed via system views:\n
      SELECT\n*\nFROM sys.dm_xe_packages;<\/pre>\nThis query returns some basic information about each Extended Events package:\n\n\"Information\n\nThis query returns a list of all events. Note that the view dm_xe_objects<\/em> also contains types, targets, actions, and other entities, as given by the column object_type<\/em>.\n
      SELECT\ndm_xe_packages.name AS package_name,\ndm_xe_packages.description,\ndm_xe_objects.name AS object_name,\ndm_xe_objects.object_type,\ndm_xe_objects.description\nFROM sys.dm_xe_objects\nINNER JOIN sys.dm_xe_packages\nON dm_xe_packages.guid = dm_xe_objects.package_guid\nWHERE dm_xe_objects.object_type = 'event';<\/pre>\nThe results are extensive, but easy to filter:\n\n\"Image\n\nAt least one event is required for an Extended Events session, but multiple may be added like this:\n
      CREATE EVENT SESSION Sql_Server_Query_Metrics ON SERVER\nADD EVENT sqlserver.rpc_completed,\nADD EVENT sqlserver.sql_batch_completed;<\/pre>\nThis script allows for any number of events to be attached to an Extended Events session. Keep in mind that data for each event is written to the Extended Event target, regardless of whether it is needed or not. Therefore, it is worthwhile to only include needed events as extraneous events will slow down Extended Events, waste storage space, and slow down event processing.\n
      Add actions to the Extended Events session<\/h2>\nSeveral data points are automatically collected for each event type. Actions allow additional elements to be retrieved along with their respective event, thereby customizing events with data that does not need to be retrieved by default for all use-cases. Actions are added onto events like this:\n
      CREATE EVENT SESSION Sql_Server_Query_Metrics ON SERVER\nADD EVENT sqlserver.rpc_completed (\nACTION (\nsqlserver.client_app_name,\nsqlserver.client_hostname,\nsqlserver.database_name,\nsqlserver.session_id,\nsqlserver.username)),\nADD EVENT sqlserver.sql_batch_completed (\nACTION (\nsqlserver.client_app_name,\nsqlserver.client_hostname,\nsqlserver.database_name,\nsqlserver.session_id,\nsqlserver.username));<\/pre>\nFor the two events in this example, 5 additional fields are added to those events as actions and will be included in the result set. Only add actions that are needed as they take up space and computing resources to collect.\n
      Add filters to the Extended Events session<\/h2>\nFilters allow results to be limited to those required for the monitoring\/troubleshooting task at hand. They remove extraneous results and decrease the size of the extended event payload data, which will make consumption of that data faster and more efficient.\n\nFilters are added as WHERE clauses onto each event:\n
      CREATE EVENT SESSION Sql_Server_Query_Metrics ON SERVER\nADD EVENT sqlserver.rpc_completed (\nACTION (\nsqlserver.client_app_name,\nsqlserver.client_hostname,\nsqlserver.database_name,\nsqlserver.session_id,\nsqlserver.username)\nWHERE (sqlserver.client_app_name NOT LIKE 'SQLAgent%')),\nADD EVENT sqlserver.sql_batch_completed (\nACTION (\nsqlserver.client_app_name,\nsqlserver.client_hostname,\nsqlserver.database_name,\nsqlserver.session_id,\nsqlserver.username)\nWHERE (sqlserver.client_app_name NOT LIKE 'SQLAgent%'));<\/pre>\nThese WHERE clauses filter out all events that originate from SQL Server Agent by filtering on client_app_name<\/em>. There are likely multiple ways to filter out a given set of events for any given Extended Events session. Frequently used are filters against client_app_name<\/em>, database_name<\/em>, client_hostname<\/em>, or username<\/em>. These allow the source of events to be filtered up-front before data is written to a target.\n
      Add a target to the extended events session<\/h2>\nEvent data needs to be written somewhere, and that somewhere needs to have the resources to accept whatever volume of data will originate from a given Extended Events Session. A complete list of all target types is available here:\nTargets for Extended Events in SQL Server – SQL Server | Microsoft Docs<\/a>\n\nWhile that list is long, most users of Extended Events will use either the ring buffer or a file as the target for their event output. The rest are worth reviewing to gain knowledge and to understand what else is available but are typically used for less common tasks.\n\nThe ring buffer stores events in memory and will automatically clear itself out as a memory cap is reached or if a maximum event count is reached. The ring buffer is great for testing Extended Events and developing new code, but is not ideal for production workloads, especially larger ones.\n\nA ring buffer target can be specified like this:\n
      CREATE EVENT SESSION Sql_Server_Query_Metrics ON SERVER\nADD EVENT sqlserver.rpc_completed (\nACTION (\nsqlserver.client_app_name,\nsqlserver.client_hostname,\nsqlserver.database_name,\nsqlserver.session_id,\nsqlserver.username)\nWHERE (sqlserver.client_app_name NOT LIKE 'SQLAgent%')),\nADD EVENT sqlserver.sql_batch_completed (\nACTION (\nsqlserver.client_app_name,\nsqlserver.client_hostname,\nsqlserver.database_name,\nsqlserver.session_id,\nsqlserver.username)\nWHERE (sqlserver.client_app_name NOT LIKE 'SQLAgent%'))\nADD TARGET package0.ring_buffer (\nSET max_memory = 2000, -- 2000kb\nmax_events_limit = (500));<\/pre>\nNote that it is possible to specify multiple targets for an Extended Events session if there is a need to do so.\n\nThe most common target for event data is a file, which is stored in an XML format with a *.XEL file extension, by default. A file target allows for more customization of its size and usage and can accommodate far more data than the ring buffer. In addition, if the database server is restarted for any reason, the data present in a file will persist, whereas ring buffer data is stored in memory and will no longer be available.\n\nA file target can be added using similar code:\n
      CREATE EVENT SESSION Sql_Server_Query_Metrics ON SERVER\nADD EVENT sqlserver.rpc_completed (\nACTION (\nsqlserver.client_app_name,\nsqlserver.client_hostname,\nsqlserver.database_name,\nsqlserver.session_id,\nsqlserver.username)\nWHERE (sqlserver.client_app_name NOT LIKE 'SQLAgent%')),\nADD EVENT sqlserver.sql_batch_completed (\nACTION (\nsqlserver.client_app_name,\nsqlserver.client_hostname,\nsqlserver.database_name,\nsqlserver.session_id,\nsqlserver.username)\nWHERE (sqlserver.client_app_name NOT LIKE 'SQLAgent%'))\nADD TARGET package0.event_file\n(SET FILENAME = 'C:\\SQLBackup\\Sql_Server_Query_Metrics.xel',\nMAX_FILE_SIZE = 1000, -- 1000MB\nMAX_ROLLOVER_FILES = 3);<\/pre>\nThis command sets the target to a file on my local drive with a file size of 1000MB and up to 3 rollover files. Rollover files allow Extended Events files to be subdivided into smaller units, allowing for faster access and easier removal.\n
      Specify options for an Extended Events session<\/h2>\nThe last step in configuring an Extended Events session is to set options that determine how events are captured, stored, and maintained. The following is an example of a few options that are added to the growing demonstration from above:\n
      CREATE EVENT SESSION Sql_Server_Query_Metrics ON SERVER\nADD EVENT sqlserver.rpc_completed (\nACTION (\nsqlserver.client_app_name,\nsqlserver.client_hostname,\nsqlserver.database_name,\nsqlserver.session_id,\nsqlserver.username)\nWHERE (sqlserver.client_app_name NOT LIKE 'SQLAgent%')),\nADD EVENT sqlserver.sql_batch_completed (\nACTION (\nsqlserver.client_app_name,\nsqlserver.client_hostname,\nsqlserver.database_name,\nsqlserver.session_id,\nsqlserver.username)\nWHERE (sqlserver.client_app_name NOT LIKE 'SQLAgent%'))\nADD TARGET package0.event_file\n(SET FILENAME = 'C:\\SQLBackup\\Sql_Server_Query_Metrics.xel',\nMAX_FILE_SIZE = 1000, -- 1000MB\nMAX_ROLLOVER_FILES = 3)\nWITH ( EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,\nMAX_DISPATCH_LATENCY = 15 SECONDS,\nMAX_MEMORY = 1024MB,\nSTARTUP_STATE = OFF);<\/pre>\nThe MAX_MEMORY<\/em> setting determines the size of the memory buffer used to capture event data prior to writing it to the file target. The event retention mode allows for some data loss to occur in the event that the buffer fills up. This ensures that SQL Server\u2019s performance is not negatively impacted by a sudden flood of events that need to be written to the target faster than SQL Server can do so. As always, rule #1 of monitoring is that the monitoring system should impact the underlying system as little as possible!\n\nThe latency setting similarly provides some buffer time for events to make their way to the target from memory. Typically, some latency is tolerable, and it ensures that events are not being written from memory to the target constantly. A latency of 0 or INFINITE means that events will only be written to the target when the buffer is full or the event session is closed.\n\nLastly, the startup state determines if the event session is started immediately upon creation, or if it is created in a stopped state.\n\nThere are a number of other settings that can be adjusted, such as whether to track causality, how to configure the memory partition mode, or set the event size cap. The Extended Events session creation documentation has good coverage of each of these options in greater detail than is in scope for this article:\n\nCREATE EVENT SESSION (Transact-SQL) – SQL Server | Microsoft Docs<\/a>\n
      Start the Extended Events session<\/h2>\nAssuming STARTUP_STATE = OFF<\/em> when the event session is created, the session will need to be started. This can be done with the following T-SQL:\n
      ALTER EVENT SESSION Sql_Server_Query_Metrics ON SERVER\nSTATE = START;<\/pre>\nThe event session may be stopped at any time with a similar script:\n
      ALTER EVENT SESSION Sql_Server_Query_Metrics ON SERVER\nSTATE = STOP;<\/pre>\n
      Read data from the Extended Events session file<\/h2>\nThe work thus far relies on the ability to quickly read data from an Extended Events session. This is by far the most challenging part of using Extended Events. The target data is written as XML and effectively making use of it requires XML parsing. SQL Server provides a function that can be used to read directly from this XML data called sys.fn_xe_file_target_read_file<\/em>. This is how a query using this function will look:\n
      SELECT\ntimestamp_utc AS sample_time_utc,\nCAST(event_data AS VARCHAR(MAX)) AS event_data_xml\nFROM sys.fn_xe_file_target_read_file(\n'C:\\SQLBackup\\Sql_Server_Query_Metrics.xel', NULL, NULL, NULL);<\/pre>\nThe query returns nothing, though:\n\"Image\n\nThat was disappointing! The file name used for the file target will have additional numbers appended to it to allow it to differentiate files used for the event session. This is necessary if rollover files are used as multiple files cannot share the same name. Details about the file can be returned with a query against sys.server_event_session_fields<\/em>:\n
      SELECT\n*\nFROM sys.server_event_session_fields\nINNER JOIN sys.server_event_sessions\nON server_event_sessions.event_session_id = \nserver_event_session_fields.event_session_id\nWHERE server_event_sessions.name = 'Sql_Server_Query_Metrics';<\/pre>\nThis query returns any file parameters for the event session target and can be filtered by joining back to sys.server_event_sessions<\/em>:\n\n\"Image\n\nNote that the file name provided here does not match the physical file name but the name provided when the event session was created. The physical file looks like this:\n\n\"Image\n\nThe function fn_xe_file_target_read_file<\/em> can include wild cards, so there is no need to fish around for the exact physical file name:\n
      SELECT\ntimestamp_utc AS sample_time_utc,\nCAST(event_data AS VARCHAR(MAX)) AS event_data_xml\nFROM sys.fn_xe_file_target_read_file(\n'C:\\SQLBackup\\Sql_Server_Query_Metrics*.xel', NULL, NULL, NULL);<\/pre>\nThe results are as follows:\n\n\"Image\n\nRetrieved are the timestamp and a big glob of XML. It is generally faster to store this data as XML and parse it asynchronously at a later time or as part of a structured process. XML shredding takes time and potentially hefty computing resources, so waiting on it is not ideal.\n\nIt is important to note that for exceptionally high event volume, the fastest way to process the XML is to use PowerShell or a .NET application to directly read the XEL file. Doing that here is well out of scope for this article but is an exercise undertaken by others in the community and worth investigating if all else fails.\n
      Extended Events data collection<\/h2>\nIt is here that this work moves from building blocks into building. This article has provided an overview of creating, configuring, and pulling data from Extended Events sessions. To go further, permanent database objects will need to be created to manage and store the XML and the event data derived from it.\n\nExtended Events are highly customizable and versatile. Therefore, efforts to build a process that uses the content discussed thus far will benefit from flexibility. There are many, many ways to solve this challenge, and each database environment will benefit from slightly different approaches.\n\nThere is a great benefit in using T-SQL exclusively to implement and manage Extended Events. While the SQL Server Management Studio UI is convenient, it is not a scalable approach for large numbers of database servers. Therefore, I encourage the reader to pursue using T-SQL, regardless of the simplicity or complexity of event collection needs.\n\nThe next article<\/a> will combine the concepts presented here into a more permanent solution that can be tailored to a wide variety of SQL Server monitoring and data collection challenges.\n\nIf you liked this article, you might also like\u00a0Collecting SQL Server performance monitor data – Simple Talk (red-gate.com)<\/a>.<\/em>\n\n\n
      \n    
      FAQs<\/h2>\n\n            
      1. What are SQL Server Extended Events?<\/h3>\n        
      \n            
      SQL Server Extended Events (XEvents) is a lightweight, configurable event-capture framework introduced in SQL Server 2008 that replaced SQL Trace and SQL Server Profiler for diagnostic monitoring. Extended Events sessions capture specific events (query execution, deadlocks, wait types, memory allocations, errors) and route them to targets (in-memory ring buffer, on-disk event_file, histogram, pair_matching, event_counter) for analysis. The framework is designed to be low-overhead by default, extensible (new events are added in each SQL Server release), and scriptable (entire sessions can be created, managed, and queried via T-SQL). Extended Events is the only monitoring framework Microsoft actively develops – SQL Profiler remains functional but is deprecated for most use cases.<\/p>\n        <\/div>\n            
      2. How do I create a SQL Server Extended Events session?<\/h3>\n        
      \n            
      Use CREATE EVENT SESSION with clauses defining the events, actions, predicates, targets, and options. Minimal syntax: CREATE EVENT SESSION [MySession] ON SERVER ADD EVENT sqlserver.sql_statement_completed ADD TARGET package0.event_file (SET filename = ‘C:\\XE\\MySession.xel’) WITH (STARTUP_STATE = OFF). Then ALTER EVENT SESSION [MySession] ON SERVER STATE = START to begin capture. Add ACTION clauses inside ADD EVENT to capture additional context per event (session_id, client_app_name, sql_text, etc.). Add WHERE clauses inside ADD EVENT to filter what’s captured. Multiple targets can be added to a single session when you need different views of the same data (for example, a ring_buffer for live inspection plus an event_file for persistent history).<\/p>\n        <\/div>\n            
      3. How do I read data from an Extended Events event_file target?<\/h3>\n        
      \n            
      Query sys.fn_xe_file_target_read_file passing the file path pattern – this returns the raw XML event_data. Shred the XML with nodes() and value() to extract specific fields: SELECT event_data.value(‘(event\/@name)[1]’, ‘nvarchar(100)’) AS event_name, event_data.value(‘(event\/@timestamp)[1]’, ‘datetime2’) AS timestamp, event_data.value(‘(event\/data[@name=”duration”]\/value)[1]’, ‘bigint’) AS duration_microseconds FROM (SELECT CAST(event_data AS XML) AS event_data FROM sys.fn_xe_file_target_read_file(‘C:\\XE\\MySession*.xel’, NULL, NULL, NULL)) AS e. For large files this can be slow – the XML shred is the typical bottleneck in Extended Events analysis. For routine reporting, stage the shredded events into a table once and query the table.<\/p>\n        <\/div>\n            
      4. Are SQL Server Extended Events replacing SQL Profiler?<\/h3>\n        
      \n            
      Yes – Extended Events has replaced SQL Profiler as the recommended monitoring framework since SQL Server 2012. SQL Profiler and SQL Trace remain available for backwards compatibility but are deprecated and receive no new functionality. Extended Events offers equivalent or broader event coverage with substantially lower overhead, more flexible targets (including histograms and pair-matching for specialised analysis), and better integration with Azure SQL Database (where Profiler doesn’t work at all). Most tasks that used Profiler – capturing long queries, finding deadlocks, tracing a specific login’s activity – have a direct Extended Events equivalent. If you’re currently using Profiler for new monitoring work, migrate to Extended Events; existing Profiler traces can run until you’re ready.<\/p>\n        <\/div>\n    <\/section>\n","protected":false},"excerpt":{"rendered":"
      Build a SQL Server Extended Events session from the ground up – choosing events, attaching actions, applying filters, selecting targets (ring buffer, event file, histogram), configuring session options, and reading the collected data. Complete T-SQL walkthrough for the modern replacement for SQL Profiler.…<\/p>\n","protected":false},"author":329827,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527,53],"tags":[5679,5842],"coauthors":[101655],"class_list":["post-92420","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","category-featured","tag-extended-events","tag-sql-monitor"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92420","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/329827"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=92420"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92420\/revisions"}],"predecessor-version":[{"id":110429,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92420\/revisions\/110429"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=92420"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=92420"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=92420"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=92420"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}