{"id":182,"date":"2017-10-24T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/5-extended-events-sessions-sql-server-instance-cannot-live-without"},"modified":"2026-03-18T21:53:06","modified_gmt":"2026-03-18T21:53:06","slug":"5-extended-events-sessions-sql-server-instance-cannot-live-without","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2017\/10\/24\/5-extended-events-sessions-sql-server-instance-cannot-live-without\/","title":{"rendered":"5 Extended Events Sessions Your SQL Server Instance Cannot Live Without – SQL Hammer"},"content":{"rendered":"

SQL Server Extend Events<\/a>.aspx) is a general event-handling system. It correlates data from SQL Server and, to a limited degree, the operating system. Extended Events are an optimized replacement for SQL Trace<\/a>. In most cases, Extended Events are much lighter weight and they are always the preferred method for trace events. In addition, there are thousands more Extended Events than SQL Trace events. The list of Extended Events is growing with every major version of SQL Server.<\/p>\n

\"\"\"\"<\/a><\/p>\n

Proactive placement<\/h2>\n

Extended Events are the all-around smart choice. They take a little bit of time to get used to, however. With thousands of new events and data points, it can be difficult to create an event session in a pinch. That is why it is important to have event sessions pre-scripted or pre-implemented on your SQL Server instances. A little bit of up-front work can save you a lot of time when you need information on the spot. Having them pre-scripted also prevents you from jumping back to Profiler<\/a>, which has a much heavier footprint on your server.<\/p>\n

When I create Extended Event sessions, I tend to use the SQL Server Management Studio wizard to find the events and actions (additional fields) that I want. Then, I will script it out and save it for later.<\/p>\n

Below are five Extended Events sessions that I have found particularly useful and recommend you add to your toolbox.<\/p>\n

Most of these extended event sessions are configured with the ring buffer target because the intention of these sessions is for ad hoc performance analysis. Each could easily be converted to an event file target for historical tracking, alerting, or trend analysis. Read here<\/a> for more information on targets.<\/em><\/p>\n

1. Long running queries<\/h2>\n

Session<\/h3>\n

This event session tracks long running ad hoc queries and stored procedures. It returns useful fields for identifying what executed the query, such as, the client_app_name and client_hostname. This session uses the ring buffer<\/a>.aspx) which is an in-memory target designed for short-term use.<\/p>\n

CREATE EVENT SESSION [LongRunningQueries] ON SERVER\nADD EVENT sqlserver.sp_statement_completed\n(\nACTION\n(\npackage0.collect_system_time\n,sqlserver.client_app_name\n,sqlserver.client_hostname\n,sqlserver.database_name\n,sqlserver.plan_handle\n,sqlserver.query_hash\n,sqlserver.session_id\n)\nWHERE duration > 5000000 -- longer than 5 seconds\n),\nADD EVENT sqlserver.sql_statement_completed\n(\nACTION\n(\npackage0.collect_system_time\n,sqlserver.client_app_name\n,sqlserver.client_hostname\n,sqlserver.database_name\n,sqlserver.plan_handle\n,sqlserver.query_hash\n,sqlserver.session_id\n)\nWHERE duration > 5000000 -- longer than 5 seconds\n)\nADD TARGET package0.ring_buffer(SET max_events_limit=(0 \/unlimited<\/em>\/),max_memory=(1048576 \/1 GB<\/em>\/))\nWITH (STARTUP_STATE=OFF,MAX_DISPATCH_LATENCY = 5SECONDS)<\/code><\/pre>\n
Query<\/h3>\n
Querying the extended events data requires a little bit of work with XML. Jes Borland describes it well, here<\/a>. Below is the query written for this session specifically. Commented out, near the bottom, I included a couple of useful filters and sort orders.<\/p>\n
--\/ Comment this part out after you run it once, unless you want to refresh the temp table.\n\n
IF OBJECT_ID('tempdb..#capture_waits_data') IS NOT NULL\nDROP TABLE #capture_waits_data<\/p>\n\n
SELECT CAST(target_data as xml) AS targetdata\nINTO #capture_waits_data\nFROM sys.dm_xe_session_targets xet\nJOIN sys.dm_xe_sessions xes\nON xes.address = xet.event_session_address\nWHERE xes.name = 'LongRunningQueries'\nAND xet.target_name = 'ring_buffer';<\/p>\n\n
--<\/em>\/<\/p>\n\n
\/<\/strong><\/em><\/strong><\/strong><\/em><\/strong><\/strong><\/em><\/strong><\/strong><\/em><\/strong><\/strong><\/em><\/strong><\/strong><\/em>\/<\/p>\n\n
SELECT xed.event_data.value('(@timestamp)[1]', 'datetime2') AS datetime_utc,\nCONVERT(datetime2,SWITCHOFFSET(CONVERT(datetimeoffset,xed.event_data.value('(@timestamp)[1]', 'datetime2')),DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS datetime_local,\nxed.event_data.value('(@name)[1]', 'varchar(50)') AS event_type,\nxed.event_data.value('(data[@name=\"statement\"]\/value)[1]', 'varchar(max)') AS statement,\nxed.event_data.value('(data[@name=\"duration\"]\/value)[1]', 'bigint')\/1000 AS duration_ms,\nxed.event_data.value('(data[@name=\"cpu_time\"]\/value)[1]', 'bigint')\/1000 AS cpu_time_ms,\nxed.event_data.value('(data[@name=\"physical_reads\"]\/value)[1]', 'bigint') AS physical_reads,\nxed.event_data.value('(data[@name=\"logical_reads\"]\/value)[1]', 'bigint') AS logical_reads,\nxed.event_data.value('(data[@name=\"writes\"]\/value)[1]', 'bigint') AS writes,\nxed.event_data.value('(data[@name=\"row_count\"]\/value)[1]', 'bigint') AS row_count,\nxed.event_data.value('(action[@name=\"database_name\"]\/value)[1]', 'varchar(255)') AS database_name,\nxed.event_data.value('(action[@name=\"client_hostname\"]\/value)[1]', 'varchar(255)') AS client_hostname,\nxed.event_data.value('(action[@name=\"client_app_name\"]\/value)[1]', 'varchar(255)') AS client_app_name\nFROM #capture_waits_data\nCROSS APPLY targetdata.nodes('\/\/RingBufferTarget\/event') AS xed (event_data)\nWHERE 1=1\n\/ refine your search further than the XE session's filter\nAND xed.event_data.value('(data[@name=\"statement\"]\/value)[1]', 'varchar(max)') = 'EXEC spDemoSproc'\n--<\/em>\/\n\/ find queries within a time range\nAND xed.event_data.value('(@timestamp)[1]', 'datetime2') > CAST('20170925 09:57 AM' AS datetime2) AT TIME ZONE 'Eastern Standard Time'\n--<\/em>\/\n\/ Find highest resource consumption\nORDER BY\nxed.event_data.value('(data[@name=\"duration\"]\/value)[1]', 'bigint') DESC\n--xed.event_data.value('(data[@name=\"cpu_time\"]\/value)[1]', 'bigint') DESC\n--xed.event_data.value('(data[@name=\"physical_reads\"]\/value)[1]', 'bigint') DESC\n--xed.event_data.value('(data[@name=\"logical_reads\"]\/value)[1]', 'bigint') DESC\n--xed.event_data.value('(data[@name=\"writes\"]\/value)[1]', 'bigint') DESC\n--xed.event_data.value('(data[@name=\"row_count\"]\/value)[1]', 'bigint') DESC\n--<\/em>\/<\/code><\/pre>\n<\/p>\n
\"\"\"\"<\/a><\/p>\n
2. Query timeouts<\/h2>\n
Session<\/h3>\n
This next query is designed to track connections which are interrupted or aborted, often caused by execution timeouts. Unlike the last session, this one uses the pair matching<\/a>.aspx) target. The pair matching target is also an in-memory target but correlates matched pairs and reports on unmatched events. This works by detecting a sqlserver.sql_statement_started event without a matching sqlserver.sql_statement_completed event.<\/p>\n
CREATE EVENT SESSION [QueryTimeouts] ON SERVER\nADD EVENT sqlserver.sql_statement_completed\n(\nACTION\n(\nsqlserver.session_id\n,sqlserver.query_hash\n,sqlserver.tsql_stack\n)\n),\nADD EVENT sqlserver.sql_statement_starting\n(\nACTION\n(\nsqlserver.session_id\n,sqlserver.query_hash\n,sqlserver.tsql_stack\n)\n)\nADD TARGET package0.pair_matching\n(\nSET\nbegin_event = 'sqlserver.sql_statement_starting',\nbegin_matching_actions = 'sqlserver.session_id, sqlserver.tsql_stack',\nend_event = 'sqlserver.sql_statement_completed',\nend_matching_actions = 'sqlserver.session_id, sqlserver.tsql_stack',\nrespond_to_memory_pressure = 0\n)\nWITH (MAX_DISPATCH_LATENCY=5 SECONDS, TRACK_CAUSALITY=ON, STARTUP_STATE=OFF)<\/code><\/pre>\n
Query<\/h3>\n
The below query aggregates the matched count which were successfully completed queries, the unmatched counts which represent the timeouts, and then it will display the detailed information for the queries that timed out.<\/p>\n
-- Create XML variable to hold Target Data\nDECLARE @target_data XML\nSELECT @target_data =\nCAST(target_data AS XML)\nFROM sys.dm_xe_sessions AS s\nJOIN sys.dm_xe_session_targets AS t\nON t.event_session_address = s.address\nWHERE s.name = 'QueryTimeouts'\nAND t.target_name = 'pair_matching'\n\n
-- Query XML variable to get Target Execution information\nSELECT\n@target_data.value('(PairingTarget\/@orphanCount)[1]', 'int') AS orphanCount,\n@target_data.value('(PairingTarget\/@matchedCount)[1]', 'int') AS matchedCount,\nCOALESCE(@target_data.value('(PairingTarget\/@memoryPressureDroppedCount)[1]', 'int'),0) AS memoryPressureDroppedCount<\/p>\n\n
-- Query the XML variable to get the Target Data\nSELECT\nn.value('(event\/action[@name=\"session_id\"]\/value)[1]', 'int') as session_id,\nn.value('(event\/@name)[1]', 'varchar(50)') AS event_name,\nn.value('(event\/data[@name=\"statement\"]\/value)[1]', 'varchar(8000)') as statement,\nNULLIF(n.value('(event\/action[@name=\"query_hash\"]\/value)[1]', 'numeric(20)'),0) as query_hash_numeric,\nn.value('(event\/@timestamp)[1]', 'datetime2') AS datetime_utc,\nDATEADD(hh,DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),n.value('(event\/@timestamp)[1]', 'datetime2')) AS datetime_local,\nn.value('(event\/action[@name=\"tsql_stack\"]\/text)[1]', 'varchar(max)') as tsql_stack\nFROM\n(\nSELECT td.query('.') as n\nFROM @target_data.nodes('PairingTarget\/event') AS q(td)\n) as tab\n--Excluding this currently running query.\nWHERE n.value('(event\/action[@name=\"session_id\"]\/value)[1]', 'int') <> @@SPID\nORDER BY session_id<\/code><\/pre>\n<\/p>\n
\"\"\"\"<\/a><\/p>\n
3. One bad query<\/h2>\n
Session<\/h3>\n
This session is used to track a specific query or stored procedure. It is great for capturing actual runtime data about a query that you know is a problem. It implements a wild card search on the sql_text by populating the @TextSearch variable with a snippet from your query.<\/p>\n
DECLARE @TextSearch NVARCHAR(3998) = N'query text to search'\n\n
EXEC(N'\nCREATE EVENT SESSION [Query_' + @TextSearch + '] ON SERVER\nADD EVENT sqlserver.sp_statement_completed\n(\nACTION\n(\npackage0.collect_system_time,\nsqlserver.client_app_name,\nsqlserver.client_hostname,\nsqlserver.database_name\n)\nWHERE ([sqlserver].like_i_sql_unicode_string<\/a>)\n),\nADD EVENT sqlserver.sql_statement_completed\n(\nACTION\n(\npackage0.collect_system_time,\nsqlserver.client_app_name,\nsqlserver.client_hostname,\nsqlserver.database_name\n)\nWHERE ([sqlserver].like_i_sql_unicode_string<\/a>)\n)\nADD TARGET package0.ring_buffer(SET max_events_limit=(0 \/unlimited<\/em>\/),max_memory=(1048576 \/1 GB<\/em>\/))\nWITH (STARTUP_STATE=OFF,MAX_DISPATCH_LATENCY = 5SECONDS)\n')<\/code><\/pre>\n<\/p>\n
Query<\/h3>\n
--\/ Comment this part out after you run it once, unless you want to refresh the temp table.\n\n
DECLARE @SessionName NVARCHAR(4000) = 'Query_demo'<\/p>\n\n
IF OBJECT_ID('tempdb..#capture_waits_data') IS NOT NULL\nDROP TABLE #capture_waits_data<\/p>\n\n
SELECT CAST(target_data as xml) AS targetdata\nINTO #capture_waits_data\nFROM sys.dm_xe_session_targets xet\nJOIN sys.dm_xe_sessions xes\nON xes.address = xet.event_session_address\nWHERE xes.name = @SessionName\nAND xet.target_name = 'ring_buffer';<\/p>\n\n
--<\/em>\/<\/p>\n\n
\/<\/strong><\/strong><\/em><\/strong><\/strong><\/em><\/strong><\/strong><\/em><\/strong><\/strong><\/em><\/strong><\/strong><\/em><\/strong><\/strong><\/em>\/<\/p>\n\n
SELECT xed.event_data.value('(@timestamp)[1]', 'datetime2') AS datetime_utc,\nDATEADD(hh,DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),xed.event_data.value('(@timestamp)[1]', 'datetime2')) AS datetime_local,\nxed.event_data.value('(@name)[1]', 'varchar(50)') AS event_type,\nxed.event_data.value('(data[@name=\"statement\"]\/value)[1]', 'varchar(max)') AS statement,\nxed.event_data.value('(data[@name=\"duration\"]\/value)[1]', 'bigint')\/1000 AS duration_ms,\nxed.event_data.value('(data[@name=\"cpu_time\"]\/value)[1]', 'bigint')\/1000 AS cpu_time_ms,\nxed.event_data.value('(data[@name=\"physical_reads\"]\/value)[1]', 'bigint') AS physical_reads,\nxed.event_data.value('(data[@name=\"logical_reads\"]\/value)[1]', 'bigint') AS logical_reads,\nxed.event_data.value('(data[@name=\"writes\"]\/value)[1]', 'bigint') AS writes,\nxed.event_data.value('(data[@name=\"row_count\"]\/value)[1]', 'bigint') AS row_count,\nxed.event_data.value('(action[@name=\"database_name\"]\/value)[1]', 'varchar(255)') AS database_name,\nxed.event_data.value('(action[@name=\"client_hostname\"]\/value)[1]', 'varchar(255)') AS client_hostname,\nxed.event_data.value('(action[@name=\"client_app_name\"]\/value)[1]', 'varchar(255)') AS client_app_name\nFROM #capture_waits_data\nCROSS APPLY targetdata.nodes('\/\/RingBufferTarget\/event') AS xed (event_data)\nWHERE 1=1\n\/ refine your search further than the XE session's filter\nAND xed.event_data.value('(data[@name=\"statement\"]\/value)[1]', 'varchar(max)') = 'EXEC spDemoSproc'\n--<\/em>\/\n\/ find queries within a time range\nAND xed.event_data.value('(@timestamp)[1]', 'datetime2') > CAST('20170925 09:57 AM' AS datetime2) AT TIME ZONE 'Eastern Standard Time'\n--<\/em>\/\n\/ Find highest resource consumption\nORDER BY\nxed.event_data.value('(data[@name=\"duration\"]\/value)[1]', 'bigint') DESC\n--xed.event_data.value('(data[@name=\"cpu_time\"]\/value)[1]', 'bigint') DESC\n--xed.event_data.value('(data[@name=\"physical_reads\"]\/value)[1]', 'bigint') DESC\n--xed.event_data.value('(data[@name=\"logical_reads\"]\/value)[1]', 'bigint') DESC\n--xed.event_data.value('(data[@name=\"writes\"]\/value)[1]', 'bigint') DESC\n--xed.event_data.value('(data[@name=\"row_count\"]\/value)[1]', 'bigint') DESC\n--<\/em>\/<\/code><\/pre>\n<\/p>\n
\"\"\"\"<\/a><\/p>\n
4. Why are there excessive recompilations?<\/h2>\n
Session<\/h3>\n
This session is useful for detecting excessive recompilations which cause queries to slow and CPU usage to spike. It uses the histogram<\/a> target to capture the reasons for recompiles and how many of each reason occurred.<\/p>\n
CREATE EVENT SESSION RecompileReasons ON SERVER\nADD EVENT sqlserver.sql_statement_recompile\nADD TARGET package0.histogram\n(\nSET filtering_event_name=N'sqlserver.sql_statement_recompile',\nsource=N'recompile_cause',\nsource_type=(0)\n)\nWITH (STARTUP_STATE=OFF, MAX_DISPATCH_LATENCY = 5SECONDS)<\/code><\/pre>\n
Query<\/h3>\n
SELECT sv.subclass_name as recompile_cause,\nshredded.recompile_count\nFROM sys.dm_xe_session_targets AS xet\nJOIN sys.dm_xe_sessions AS xes ON (xe.address = xet.event_session_address)\nCROSS APPLY ( SELECT CAST(xet.target_data as xml) ) as target_data_xml ([xml])\nCROSS APPLY target_data_xml.[xml].nodes('\/HistogramTarget\/Slot') AS nodes (slot_data)\nCROSS APPLY\n(\nSELECT nodes.slot_data.value('(value)[1]', 'int') AS recompile_cause,\nnodes.slot_data.value('(@count)[1]', 'int') AS recompile_count\n) as shredded\nJOIN sys.trace_subclass_values AS sv ON shredded.recompile_cause = sv.subclass_value\nWHERE xes.name = 'RecompileReasons'\nAND sv.trace_event_id = 37 -- SP:Recompile<\/code><\/pre>\n
\"\"\"\"<\/a><\/p>\n
5. Memory grants<\/h2>\n
Session<\/h3>\n
This session captures queries where the memory grant is larger than the filter predicate, 2 MB in this example. It will output the granted memory, the actual used memory, degree of parallelism, and a few other useful data points.<\/p>\n
CREATE EVENT SESSION [MemoryUsage] ON SERVER\nADD EVENT sqlserver.query_memory_grant_usage\n(\nACTION\n(\nsqlserver.query_hash\n,sqlserver.query_plan_hash\n,sqlserver.sql_text\n)\nWHERE granted_memory_kb > 2048 --2 MB\n)\nADD TARGET package0.ring_buffer(SET max_events_limit=(0 \/unlimited<\/em>\/),max_memory=(1048576 \/1 GB<\/em>\/))\nWITH (STARTUP_STATE=OFF,MAX_DISPATCH_LATENCY = 5SECONDS)<\/code><\/pre>\n
Query<\/h3>\n
--\/ Comment this part out after you run it once, unless you want to refresh the temp table.\n\n
IF OBJECT_ID('tempdb..#capture_waits_data') IS NOT NULL\nDROP TABLE #capture_waits_data<\/p>\n\n
SELECT CAST(target_data as xml) AS targetdata\nINTO #capture_waits_data\nFROM sys.dm_xe_session_targets xet\nJOIN sys.dm_xe_sessions xes\nON xes.address = xet.event_session_address\nWHERE xes.name = 'MemoryUsage'\nAND xet.target_name = 'ring_buffer';<\/p>\n\n
--<\/em>\/<\/p>\n\n
\/<\/strong><\/em><\/strong><\/strong><\/em><\/strong><\/strong><\/em><\/strong><\/strong><\/em><\/strong><\/strong><\/em><\/strong><\/strong><\/em>*\/<\/p>\n\n
SELECT xed.event_data.value('(@timestamp)[1]', 'datetime2') AS datetime_utc,\nDATEADD(hh,DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),xed.event_data.value('(@timestamp)[1]', 'datetime2')) AS datetime_local,\nxed.event_data.value('(@name)[1]', 'varchar(50)') AS event_type,\nxed.event_data.value('(data[@name=\"ideal_memory_kb\"]\/value)[1]', 'bigint') AS ideal_memory_kb,\nxed.event_data.value('(data[@name=\"granted_memory_kb\"]\/value)[1]', 'bigint') AS granted_memory_kb,\nxed.event_data.value('(data[@name=\"used_memory_kb\"]\/value)[1]', 'bigint') AS used_memory_kb,\nxed.event_data.value('(data[@name=\"usage_percent\"]\/value)[1]', 'int') AS usage_percent,\nxed.event_data.value('(data[@name=\"dop\"]\/value)[1]', 'int') AS dop,\nxed.event_data.value('(data[@name=\"granted_percent\"]\/value)[1]', 'int') AS granted_percent,\nxed.event_data.value('(action[@name=\"sql_text\"]\/value)[1]', 'varchar(max)') AS sql_text,\nxed.event_data.value('(action[@name=\"query_plan_hash\"]\/value)[1]', 'numeric(20)') AS query_plan_hash,\nxed.event_data.value('(action[@name=\"query_hash\"]\/value)[1]', 'numeric(20)') AS query_hash\nFROM #capture_waits_data\nCROSS APPLY targetdata.nodes('\/\/RingBufferTarget\/event') AS xed (event_data)\nWHERE 1=1\n--\/<\/em> Search for large memory grants.\nAND xed.event_data.value('(data[@name=\"used_memory_kb\"]\/value)[1]', 'bigint') > 5120 -- 5MB\n--\/\n--\/<\/em> Search for grants too large for the actual used\nAND xed.event_data.value('(data[@name=\"usage_percent\"]\/value)[1]', 'bigint') < 50\n--*\/<\/code><\/pre>\n<\/p>\n
\"\"\"\"<\/a><\/p>\n
Scripts<\/h2>\n
Click here<\/a> to download the full set of scripts.<\/p>\n","protected":false},"excerpt":{"rendered":"
SQL Server Extend Events.aspx) is a general event-handling system. It correlates data from SQL Server and, to a limited degree, the operating system. Extended Events are an optimized replacement for SQL Trace. In most cases, Extended Events are much lighter weight and they are always the preferred method for trace events. In addition, there are […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-182","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/182","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=182"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/182\/revisions"}],"predecessor-version":[{"id":444,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/182\/revisions\/444"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=182"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=182"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=182"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}