Using Extended Events as a Buffer

Assume you have some kind of process outside the SQL Server which is supposed to record some kind of database activity. There are many ways to do it, most of which require constant and/or stable connection to the database. But what if that’s not good enough? Let’s consider our options.

First of all, I must give some credit to Erik (the) Darling, who implemented a brilliant solution which uses a similar idea to what I had in mind: sp_HumanEvents.

Erik’s basic idea is to replace the likes of sp_who, sp_who2 or sp_WhoIsActive, by way of utilizing extended events, and provide a more “complete” picture of what’s currently happening in the database… Or at least… During the few seconds while you’re running the script.

But that could also still be a problem, if what you need is a truly “complete” view of what is happening in the database…

More specifically, the likes of sp_who[2], sp_WhoIsActive, and sp_HumanEvents, all require a stable connection to the database, and they only tell you what’s happening now – while you’re running the script.

But what if you need more than that? What if you need to know not just what’s happening while you’re running a script to sample the database, but also – everything that has happened since the last time that you’ve run the script?

You could do something like set up a job that runs these scripts periodically and saves the results to tables. It’s even one of the built in functionalities in sp_HumanEvents, for example. But there are still two problems here:

  • The data you collect is limited to what is captured while the monitor job is executing. But in-between the schedules – you’re still blind.
  • You must save it to local tables on the target server, incurring additional impact on your target.
  • These jobs and tables are “foot prints” that you leave on your target server, and it may not always be possible or feasible to create them.

Whatever we do, the bulk of our impact should be outside of our monitored target, and yet… We must somehow maintain continuity in our collected data.

What about Profiler?

What if, we had a way to monitor what’s happening on a SQL server, without us actually being constantly connected to it, and still incur a minimal impact on the target server itself? What if we could have something that “samples” a SQL server target for monitoring data from some kind of “buffer” which we can then clear out, and let it refill again with new monitoring data until the next time that we sample the server?

Something like the client side trace (stored procedure sp_trace_getdata which is also used by the Profiler client to stream data) comes to mind as a possible solution. SentryOne SQL Sentry, for example, uses that method to implement monitoring of T-SQL commands and other events. Theoretically, it could work very well as a sort of “buffer” that holds collected events until the next time that our process connects to the database. However, its retention depth is not configurable, and it’s kind of outdated technology.

Extended Events as the answer

Extended events provides a solution similar to client side trace. It basically can capture all events that a trace can capture (and more), and it also supports a wider choice of target types. And that, is where its true power lies.

It just so happens that Extended Events has a target type called “ring buffer“, and it gives us exactly what we need.

First of all, we can configure the ring buffer with a maximum size and a maximum number of events to collect. This guarantees that we have full control over our impact on the target server.

For example, let’s say that we’d like to take up no more than 16 MB of RAM with our monitoring session. It could be good enough, or not. It depends on the workload stress on your target, and how much RAM you can spare. Regardless, you should only filter for the events that you really need, in order to minimize your observer overhead.

Secondly, this ring buffer can also be completely and immediately deleted once we drop the extended events session. Unlike the alternative file target which does not delete its files if you drop the session. They just stay there… Taking up valuable disk space. (The same holds true for the server-side trace, but not for the client-side trace.)

It appears that, in the extended events ring buffer target, we have the perfect candidate to implement what we need.

And now, for a true story

The idea for this mechanism was born thanks to one client that I had, with multiple SQL servers hosted on Azure. Those were Azure SQL Databases, in fact. But there was no option of using third party monitoring tools, nor was the Azure SQL Analytics solution good enough for what they needed. They needed an alternative, and this is where I came in.

The implementation was as simple as possible:

  • Check if our extended events session exists. If it does, return the data it collected so far, and then… Drop the XE session.
  • Recreate the extended events session.

And that’s about it. At least, as far as it concerns the monitored SQL server target. As for the monitoring data that we’ve collected, we would of course need to save it somewhere. This particular client wanted it to be saved in Azure Data Explorer (aka Kusto), but it doesn’t really matter. You can save it wherever you want.

Azure Data Factory provided us with the “pure cloud” solution that my client was looking for. It has the capability to run the relevant T-SQL command (although it had to be a stored procedure), save the data in Kusto, and automatically run the process every few minutes based on a schedule.

You, however, could use whatever method you’d like… An SSIS package… Or a scheduled job on another server using a linked server connection… Or a Powershell script run by a scheduled task… Or some kind of application service… It really doesn’t matter.

Give me the scripts already!

I prepared two example scripts that utilize this methodology:

One example creates an extended events session that monitors for long running SQL commands:

Author: Eitan Blumin | https://www.eitanblumin.com
Date: 2020-02-26
Last Update: 2020-04-07
Description: Collect T-SQL Events using an Extended Events Buffer
SET NOCOUNT ON;
DECLARE
@SourceLinkedServer SYSNAME
, @MinimumDurationMilliSeconds BIGINT
, @CaptureAllTimeoutsOrAborts BIT
, @BufferMaxMemoryMB INT
, @BufferMaxEventsCount INT
, @BufferXESessionName SYSNAME
, @PreferObjectName BIT
DECLARE @ProgramsToIgnore AS TABLE(appname SYSNAME);
DECLARE @ProceduresToIgnore AS TABLE(procname SYSNAME);
/******************** CONFIGURATION ********************/
SET @SourceLinkedServer = NULL Optionally place a linked server name here. Set as NULL to monitor the local server.
SET @MinimumDurationMilliSeconds = 3000 Set minimum threshold of event duration to capture
SET @CaptureAllTimeoutsOrAborts = 1 If set to 1, will also capture timeout and SQL error events
SET @BufferMaxMemoryMB = 16
SET @BufferMaxEventsCount = 50000
SET @BufferXESessionName = 'CaptureTSQLEvents'
SET @PreferObjectName = 1 If set to 1, will prefer to output a procedure's object name rather than the SQL batch text
INSERT INTO @ProgramsToIgnore
SELECT 'Microsoft SQL Server Management Studio – Transact-SQL IntelliSense'
UNION ALL SELECT 'Microsoft SQL Server Management Studio – Query'
UNION ALL SELECT 'Microsoft SQL Server Management Studio'
UNION ALL SELECT 'SQLServerCEIP'
UNION ALL SELECT 'check_mssql_health'
UNION ALL SELECT 'SQL Server Performance Investigator'
INSERT INTO @ProceduresToIgnore
SELECT 'sp_reset_connection'
UNION ALL SELECT 'sp_executesql'
/******************** /CONFIGURATION ********************/
DECLARE @CMD NVARCHAR(MAX), @Filters NVARCHAR(MAX), @ProcFilters NVARCHAR(MAX), @Executor NVARCHAR(1000)
DECLARE @IsAzureSQLDB BIT
SET @Executor = ISNULL(QUOTENAME(@SourceLinkedServer) + N'…', N'') + N'sp_executesql'
SET @CMD = N'SET @IsAzureSQLDB = CASE WHEN SERVERPROPERTY(''Edition'') = ''SQL Azure'' THEN 1 ELSE 0 END'
EXEC @Executor @CMD, N'@IsAzureSQLDB BIT OUTPUT', @IsAzureSQLDB OUTPUT;
SET @Filters = N'([package0].[equal_boolean]([sqlserver].[is_system],(0)))
AND (' + CASE WHEN @CaptureAllTimeoutsOrAborts = 1 THEN N'result = 2 OR ' ELSE N'' END + N'duration >= ' + CONVERT(nvarchar, @MinimumDurationMilliSeconds * 1000) + N')'
SELECT @Filters = @Filters + CHAR(10) + N'AND (sqlserver.client_app_name <> ''' + REPLACE(appname, N'''', N'''''') + N''')'
FROM @ProgramsToIgnore
SELECT @ProcFilters = ISNULL(@ProcFilters, N'') + CHAR(10) + N'AND (object_name <> ''' + REPLACE(procname, N'''', N'''''') + N''')'
FROM @ProceduresToIgnore
IF @IsAzureSQLDB = 1 AND @SourceLinkedServer IS NULL
BEGIN
SET @CMD = N'— Retrieve buffer contents
IF OBJECT_ID(''tempdb..#events'') IS NOT NULL DROP TABLE #events
CREATE TABLE #events (event_xml XML);
INSERT INTO #events
SELECT X.query(''.'')
FROM (SELECT xdata = CAST(xet.target_data AS xml)
FROM sys.dm_xe_database_session_targets AS xet
JOIN sys.dm_xe_database_sessions AS xe ON (xe.address = xet.event_session_address)
WHERE xe.name = ''' + @BufferXESessionName + N'''
AND target_name= ''ring_buffer''
) AS a
CROSS APPLY xdata.nodes (N''//event'') AS session_events (X)
— Unfurl raw data
SELECT
[server_name] = @@SERVERNAME,
[database_name] = session_events.event_xml.value (N''(event/action[@name="database_name"]/value)[1]'' , N''SYSNAME'') ,
event_name = session_events.event_xml.value (N''(event/@name)[1]'' , N''NVARCHAR(1000)'') ,
event_timestamp_utc = session_events.event_xml.value (N''(event/@timestamp)[1]'' , N''DATETIME2(7)'') ,
session_id = session_events.event_xml.value (N''(event/action[@name="session_id"]/value)[1]'' , N''BIGINT'') ,
cpu_time = session_events.event_xml.value (N''(event/data[@name="cpu_time"]/value)[1]'' , N''BIGINT'') ,
duration = session_events.event_xml.value (N''(event/data[@name="duration"]/value)[1]'' , N''BIGINT'') ,
physical_reads = session_events.event_xml.value (N''(event/data[@name="physical_reads"]/value)[1]'' , N''BIGINT'') ,
logical_reads = session_events.event_xml.value (N''(event/data[@name="logical_reads"]/value)[1]'' , N''BIGINT'') ,
writes = session_events.event_xml.value (N''(event/data[@name="writes"]/value)[1]'' , N''BIGINT'') ,
row_count = session_events.event_xml.value (N''(event/data[@name="row_count"]/value)[1]'' , N''BIGINT'') ,
result = session_events.event_xml.value (N''(event/data[@name="result"]/value)[1]'' , N''INT'') ,
result_desc = session_events.event_xml.value (N''(event/data[@name="result"]/text)[1]'' , N''VARCHAR(15)'') ,
client_app_name = session_events.event_xml.value (N''(event/action[@name="client_app_name"]/value)[1]'' , N''NVARCHAR(1000)'') ,
client_host_name = session_events.event_xml.value (N''(event/action[@name="client_hostname"]/value)[1]'' , N''NVARCHAR(1000)'') ,
client_process_id = session_events.event_xml.value (N''(event/action[@name="client_pid"]/value)[1]'' , N''BIGINT'') ,
username = session_events.event_xml.value (N''(event/action[@name="username"]/value)[1]'' , N''SYSNAME'') ,
plan_handle = session_events.event_xml.value (N''(event/action[@name="plan_handle"]/value)[1]'' , N''VARBINARY(MAX)'') ,
query_plan_hash = session_events.event_xml.value (N''(event/action[@name="query_plan_hash"]/value)[1]'' , N''VARBINARY(MAX)'') ,
sql_text = COALESCE(' + CASE WHEN @PreferObjectName = 1 THEN
N'
session_events.event_xml.value (N''(event/data[@name="object_name"]/value)[1]'' , N''NVARCHAR(MAX)''),
session_events.event_xml.value (N''(event/action[@name="sql_text"]/value)[1]'' , N''NVARCHAR(MAX)''),
session_events.event_xml.value (N''(event/data[@name="statement"]/value)[1]'' , N''NVARCHAR(MAX)'')'
ELSE
N'
session_events.event_xml.value (N''(event/action[@name="sql_text"]/value)[1]'' , N''NVARCHAR(MAX)''),
session_events.event_xml.value (N''(event/data[@name="statement"]/value)[1]'' , N''NVARCHAR(MAX)''),
session_events.event_xml.value (N''(event/data[@name="object_name"]/value)[1]'' , N''NVARCHAR(MAX)'')'
END + N')
FROM #events AS session_events;
'
END
ELSE
BEGIN
SET @CMD = N'— Retrieve buffer contents
SELECT CAST(xet.target_data AS varbinary(max))
FROM sys.dm_xe_' + CASE WHEN @IsAzureSQLDB = 1 THEN N'database_' ELSE N'' END + N'session_targets AS xet
JOIN sys.dm_xe_' + CASE WHEN @IsAzureSQLDB = 1 THEN N'database_' ELSE N'' END + N'sessions AS xe ON (xe.address = xet.event_session_address)
WHERE xe.name = ''' + @BufferXESessionName + N'''
AND target_name= ''ring_buffer''
'
END
SET @CMD = @CMD + N'
— Recreate session to flush buffer
IF EXISTS (SELECT * FROM sys.' + CASE WHEN @IsAzureSQLDB = 1 THEN N'database' ELSE N'server' END + N'_event_sessions WHERE name = ''' + @BufferXESessionName + N''')
BEGIN
DROP EVENT SESSION ' + QUOTENAME(@BufferXESessionName) + N' ON ' + CASE WHEN @IsAzureSQLDB = 1 THEN N'DATABASE' ELSE N'SERVER' END + N';
END
'
SET @CMD = @CMD + N'
— Create the event session
CREATE EVENT SESSION ' + QUOTENAME(@BufferXESessionName) + N' ON ' + CASE WHEN @IsAzureSQLDB = 1 THEN N'DATABASE' ELSE N'SERVER' END + N'
ADD EVENT sqlserver.rpc_completed(
ACTION(
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.client_pid,
sqlserver.database_name,
sqlserver.username,
sqlserver.session_id,
sqlserver.sql_text,
sqlserver.plan_handle,
sqlserver.query_plan_hash
)
WHERE ' + @Filters + ISNULL(@ProcFilters, N'') + N'
)
,ADD EVENT sqlserver.sql_batch_completed(
SET collect_batch_text = 1
ACTION(
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.client_pid,
sqlserver.database_name,
sqlserver.username,
sqlserver.session_id,
sqlserver.sql_text,
sqlserver.plan_handle,
sqlserver.query_plan_hash
)
WHERE ' + @Filters + N'
)
ADD TARGET package0.ring_buffer(SET max_events_limit=(' + CONVERT(nvarchar, @BufferMaxEventsCount) + N'),max_memory=(' + CONVERT(nvarchar, @BufferMaxMemoryMB*1024) + N'))
WITH (MAX_MEMORY=' + CONVERT(nvarchar, @BufferMaxMemoryMB) + N' MB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
;
— Start the event session
IF NOT EXISTS (SELECT * FROM sys.dm_xe' + CASE WHEN @IsAzureSQLDB = 1 THEN N'_database' ELSE N'' END + N'_sessions WHERE name = ''' + @BufferXESessionName + N''')
BEGIN
ALTER EVENT SESSION ' + QUOTENAME(@BufferXESessionName) + N' ON ' + CASE WHEN @IsAzureSQLDB = 1 THEN N'DATABASE' ELSE N'SERVER' END + N' STATE = START;
END'
IF @IsAzureSQLDB = 1 AND @SourceLinkedServer IS NULL
BEGIN
SELECT @CMD AS GeneratedScript
RAISERROR(N'Azure SQL Databases are not supported for dynamic execution. Please take the generated script in attached results instead and run it directly.'
, 11, 0);
END
ELSE
BEGIN
IF OBJECT_ID('tempdb..#xe') IS NOT NULL DROP TABLE #xe;
CREATE TABLE #xe (xdata VARBINARY(MAX));
BEGIN TRY
Query the event session data
INSERT INTO #xe
EXEC @Executor @CMD
END TRY
BEGIN CATCH
PRINT CONCAT(N'Error ', ERROR_NUMBER(), N', Line ', ERROR_LINE(), N': ', ERROR_MESSAGE());
END CATCH
IF OBJECT_ID('tempdb..#events') IS NOT NULL DROP TABLE #events
CREATE TABLE #events (event_xml XML);
INSERT INTO #events
SELECT X.query('.')
FROM (SELECT xdata = CONVERT(xml, xdata) FROM #xe) AS a
CROSS APPLY xdata.nodes (N'//event') AS session_events (X)
Unfurl raw data
SELECT
[server_name] = @@SERVERNAME,
[database_name] = session_events.event_xml.value (N'(event/action[@name="database_name"]/value)[1]' , N'SYSNAME') ,
event_name = session_events.event_xml.value (N'(event/@name)[1]' , N'NVARCHAR(1000)') ,
event_timestamp_utc = session_events.event_xml.value (N'(event/@timestamp)[1]' , N'DATETIME2(7)') ,
session_id = session_events.event_xml.value (N'(event/action[@name="session_id"]/value)[1]' , N'BIGINT') ,
cpu_time = session_events.event_xml.value (N'(event/data[@name="cpu_time"]/value)[1]' , N'BIGINT') ,
duration = session_events.event_xml.value (N'(event/data[@name="duration"]/value)[1]' , N'BIGINT') ,
physical_reads = session_events.event_xml.value (N'(event/data[@name="physical_reads"]/value)[1]' , N'BIGINT') ,
logical_reads = session_events.event_xml.value (N'(event/data[@name="logical_reads"]/value)[1]' , N'BIGINT') ,
writes = session_events.event_xml.value (N'(event/data[@name="writes"]/value)[1]' , N'BIGINT') ,
row_count = session_events.event_xml.value (N'(event/data[@name="row_count"]/value)[1]' , N'BIGINT') ,
result = session_events.event_xml.value (N'(event/data[@name="result"]/value)[1]' , N'INT') ,
result_desc = session_events.event_xml.value (N'(event/data[@name="result"]/text)[1]' , N'VARCHAR(15)') ,
client_app_name = session_events.event_xml.value (N'(event/action[@name="client_app_name"]/value)[1]' , N'NVARCHAR(1000)') ,
client_host_name = session_events.event_xml.value (N'(event/action[@name="client_hostname"]/value)[1]' , N'NVARCHAR(1000)') ,
client_process_id = session_events.event_xml.value (N'(event/action[@name="client_pid"]/value)[1]' , N'BIGINT') ,
username = session_events.event_xml.value (N'(event/action[@name="username"]/value)[1]' , N'SYSNAME') ,
plan_handle = session_events.event_xml.value (N'(event/action[@name="plan_handle"]/value)[1]' , N'VARBINARY(MAX)') ,
query_plan_hash = session_events.event_xml.value (N'(event/action[@name="query_plan_hash"]/value)[1]' , N'VARBINARY(MAX)') ,
sql_text = CASE WHEN @PreferObjectName = 1 THEN
COALESCE(
session_events.event_xml.value (N'(event/data[@name="object_name"]/value)[1]' , N'NVARCHAR(MAX)'),
session_events.event_xml.value (N'(event/action[@name="sql_text"]/value)[1]' , N'NVARCHAR(MAX)'),
session_events.event_xml.value (N'(event/data[@name="statement"]/value)[1]' , N'NVARCHAR(MAX)')
)
ELSE
COALESCE(
session_events.event_xml.value (N'(event/action[@name="sql_text"]/value)[1]' , N'NVARCHAR(MAX)'),
session_events.event_xml.value (N'(event/data[@name="statement"]/value)[1]' , N'NVARCHAR(MAX)'),
session_events.event_xml.value (N'(event/data[@name="object_name"]/value)[1]' , N'NVARCHAR(MAX)')
)
END
FROM #events AS session_events;
DROP TABLE #events;
END
CaptureTSQLEvents_XE_Buffer.sql

The other example collects SQL error events:

Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Date: 2020-05-31
Last Update: 2020-07-15
Description: Collect T-SQL Error Events using an Extended Events Buffer
The script automatically detects whether you're in an Azure SQL DB, or a regular SQL Server instance.
SET NOCOUNT ON;
DECLARE
@SourceLinkedServer SYSNAME
, @MinimumSeverity INT
, @BufferMaxMemoryMB INT
, @BufferMaxEventsCount INT
, @BufferXESessionName SYSNAME
, @UseFileTarget BIT
, @FileTargetMaxFiles INT
, @FileTargetMaxFileSizeMB INT
, @FileTargetPath NVARCHAR(4000)
, @FlushBuffer BIT
, @PrintOnly BIT
DECLARE @ProgramsToIgnore AS TABLE(appname SYSNAME);
/********************* CONFIGURATION *********************/
SET @BufferXESessionName = 'CaptureTSQLErrors' Name of the extended events session
SET @SourceLinkedServer = NULL Optionally place a linked server name here. Set as NULL to monitor the local server.
SET @MinimumSeverity = 11 Set minimum threshold of error severity to capture.
SET @PrintOnly = 0 Set to 1 to get the command in text as output, instead of running it
SET @UseFileTarget = 1 Set to 1 to use file target, otherwise, the ring buffer will be used
/*************** Ring Buffer Configuration ***************/
SET @FlushBuffer = 1 Set to 1 to flush the ring buffer between executions by recreating the XE session. Otherwise, keep data.
SET @BufferMaxMemoryMB = 16 Max memory in MB for the ring buffer
SET @BufferMaxEventsCount = 50000 Max number of events to hold between flushes
/*************** File Target Configuration ***************/
SET @FileTargetPath = NULL Set the file target path (required in Azure SQL DB to use Blob Storage URL). Leave as NULL to use default SQL Server LOG folder.
SET @FileTargetMaxFiles = 5 Max number of rollover file target files
SET @FileTargetMaxFileSizeMB = 20 Max size in MB of each file target file
/****************** Programs to Exclude ******************/
INSERT INTO @ProgramsToIgnore
SELECT 'Microsoft SQL Server Management Studio – Transact-SQL IntelliSense'
UNION ALL SELECT 'Microsoft SQL Server Management Studio – Query'
UNION ALL SELECT 'Microsoft SQL Server Management Studio'
UNION ALL SELECT 'SQLServerCEIP'
UNION ALL SELECT 'check_mssql_health'
UNION ALL SELECT 'SQL Server Performance Investigator'
/********************* /CONFIGURATION *********************/
DECLARE @CMD NVARCHAR(MAX), @Filters NVARCHAR(MAX), @ProcFilters NVARCHAR(MAX), @Executor NVARCHAR(1000)
DECLARE @IsAzureSQLDB BIT, @IsNestedTransaction BIT
SET @Executor = ISNULL(QUOTENAME(@SourceLinkedServer) + N'', N'') + N'sp_executesql'
SET @CMD = N'SET @IsAzureSQLDB = CASE WHEN SERVERPROPERTY(''Edition'') = ''SQL Azure'' THEN 1 ELSE 0 END;'
EXEC @Executor @CMD, N'@IsAzureSQLDB BIT OUTPUT', @IsAzureSQLDB OUTPUT;
IF @IsAzureSQLDB = 1 AND @UseFileTarget = 1 AND (@FileTargetPath IS NULL OR @FileTargetPath NOT LIKE 'https://%')
BEGIN
RAISERROR(N'When using File Target in Azure SQL DB, you must specify a valid Azure Blog Storage URL in @FileTargetPath.',11,1);
RAISERROR('Switching over to ring buffer target.',0,1) WITH NOWAIT;
SET @UseFileTarget = 0;
END
Add xel file postfix if missing
IF @FileTargetPath IS NOT NULL AND @UseFileTarget = 1
BEGIN
IF RIGHT(@FileTargetPath, 1) IN ('/','\') SET @FileTargetPath = @FileTargetPath + @BufferXESessionName + '.xel'
IF RIGHT(@FileTargetPath, 4) <> '.xel' SET @FileTargetPath = @FileTargetPath + '.xel'
END
IF @UseFileTarget = 1 AND @FlushBuffer = 1
PRINT N'WARNING: Using File Target. Buffer will NOT be flushed!'
ELSE
SET @UseFileTarget = ISNULL(@UseFileTarget, 0);
SET @Filters = CONCAT(N'([package0].[equal_boolean]([sqlserver].[is_system],(0)))
AND [severity]>=(' ,@MinimumSeverity, N') AND [sqlserver].[sql_text]<>N''''
')
SELECT @Filters = @Filters + CHAR(10) + N'AND (sqlserver.client_app_name <> ' + QUOTENAME(appname, N'''') + N')'
FROM @ProgramsToIgnore
SetUpCommand:
IF @IsNestedTransaction = 1 OR (@IsAzureSQLDB = 1 AND @SourceLinkedServer IS NULL)
BEGIN
SET @PrintOnly = 1
RAISERROR(N'This database version is not supported for dynamic execution. Please take the generated script in attached results instead and run it directly.'
, 10, 0);
END
IF @PrintOnly = 1
BEGIN
SET @CMD = N' Retrieve buffer contents
IF OBJECT_ID(''tempdb..#events'') IS NOT NULL DROP TABLE #events
CREATE TABLE #events (event_xml XML);
INSERT INTO #events
' + CASE WHEN @UseFileTarget = 1 THEN
N'SELECT xdata = CAST(event_data AS xml)
FROM (
select [TargetFileName] = REPLACE(c.column_value, ''.xel'', ''*.xel'')
from sys.dm_xe_' + CASE WHEN @IsAzureSQLDB = 1 THEN N'database_' ELSE N'' END + N'sessions AS s
join sys.dm_xe_' + CASE WHEN @IsAzureSQLDB = 1 THEN N'database_' ELSE N'' END + N'session_object_columns AS c ON s.address = c.event_session_address
where column_name = ''filename'' and s.name = ' + QUOTENAME(@BufferXESessionName, N'''') + N'
) AS FileTarget CROSS APPLY sys.fn_xe_file_target_read_file (FileTarget.TargetFileName,null,null, null)'
ELSE
N'SELECT X.query(''.'')
FROM (SELECT xdata = CAST(xet.target_data AS xml)
FROM sys.dm_xe_' + CASE WHEN @IsAzureSQLDB = 1 THEN N'database_' ELSE N'' END + N'session_targets AS xet
JOIN sys.dm_xe_' + CASE WHEN @IsAzureSQLDB = 1 THEN N'database_' ELSE N'' END + N'sessions AS xe ON xe.address = xet.event_session_address
WHERE xe.name = ' + QUOTENAME(@BufferXESessionName, N'''') + N'
AND target_name= ''ring_buffer''
) AS a
CROSS APPLY xdata.nodes (N''//event'') AS session_events (X)'
END + N'
Unfurl raw data
SELECT
[server_name] = @@SERVERNAME,
[database_name] = session_events.event_xml.value (N''(event/action[@name="database_name"]/value)[1]'' , N''SYSNAME'') ,
event_name = session_events.event_xml.value (N''(event/@name)[1]'' , N''NVARCHAR(1000)'') ,
event_timestamp_utc = session_events.event_xml.value (N''(event/@timestamp)[1]'' , N''DATETIME2(7)'') ,
session_id = session_events.event_xml.value (N''(event/action[@name="session_id"]/value)[1]'' , N''INT'') ,
error_number = session_events.event_xml.value (N''(event/data[@name="error_number"]/value)[1]'' , N''INT'') ,
severity = session_events.event_xml.value (N''(event/data[@name="severity"]/value)[1]'' , N''INT'') ,
state = session_events.event_xml.value (N''(event/data[@name="state"]/value)[1]'' , N''INT'') ,
category = session_events.event_xml.value (N''(event/data[@name="category"]/value)[1]'' , N''INT'') ,
category_desc = session_events.event_xml.value (N''(event/data[@name="category"]/text)[1]'' , N''NVARCHAR(MAX)'') ,
message = session_events.event_xml.value (N''(event/data[@name="message"]/value)[1]'' , N''NVARCHAR(MAX)'') ,
client_app_name = session_events.event_xml.value (N''(event/action[@name="client_app_name"]/value)[1]'' , N''NVARCHAR(1000)'') ,
client_host_name = session_events.event_xml.value (N''(event/action[@name="client_hostname"]/value)[1]'' , N''NVARCHAR(1000)'') ,
client_process_id = session_events.event_xml.value (N''(event/action[@name="client_pid"]/value)[1]'' , N''BIGINT'') ,
username = session_events.event_xml.value (N''(event/action[@name="username"]/value)[1]'' , N''SYSNAME'') ,
sql_text = session_events.event_xml.value (N''(event/action[@name="sql_text"]/value)[1]'' , N''NVARCHAR(MAX)'')
,event_xml
FROM #events AS session_events;
'
END
ELSE
BEGIN
SET @CMD = N' Retrieve buffer contents
' + CASE WHEN @UseFileTarget = 1 THEN
N'SELECT xdata = CAST(event_data AS varbinary(max))
FROM (
select [TargetFileName] = REPLACE(c.column_value, ''.xel'', ''*.xel'')
from sys.dm_xe_' + CASE WHEN @IsAzureSQLDB = 1 THEN N'database_' ELSE N'' END + N'sessions AS s
join sys.dm_xe_' + CASE WHEN @IsAzureSQLDB = 1 THEN N'database_' ELSE N'' END + N'session_object_columns AS c ON s.address = c.event_session_address
where column_name = ''filename'' and s.name = ' + QUOTENAME(@BufferXESessionName, N'''') + N'
) AS FileTarget CROSS APPLY sys.fn_xe_file_target_read_file (FileTarget.TargetFileName,null,null, null)'
ELSE
N'SELECT xdata = CAST(xet.target_data AS varbinary(max))
FROM sys.dm_xe_' + CASE WHEN @IsAzureSQLDB = 1 THEN N'database_' ELSE N'' END + N'session_targets AS xet
JOIN sys.dm_xe_' + CASE WHEN @IsAzureSQLDB = 1 THEN N'database_' ELSE N'' END + N'sessions AS xe ON xe.address = xet.event_session_address
WHERE xe.name = ' + QUOTENAME(@BufferXESessionName, N'''') + N'
AND target_name= ''ring_buffer'''
END
END
IF @FlushBuffer = 1
BEGIN
SET @CMD = @CMD + N'
Recreate session to flush buffer
IF EXISTS (SELECT * FROM sys.' + CASE WHEN @IsAzureSQLDB = 1 THEN N'database' ELSE N'server' END + N'_event_sessions WHERE name = ''' + @BufferXESessionName + N''')
BEGIN
DROP EVENT SESSION ' + QUOTENAME(@BufferXESessionName) + N' ON ' + CASE WHEN @IsAzureSQLDB = 1 THEN N'DATABASE' ELSE N'SERVER' END + N';
END
'
END
SET @CMD = @CMD + N'
IF NOT EXISTS (SELECT * FROM sys.' + CASE WHEN @IsAzureSQLDB = 1 THEN N'database' ELSE N'server' END + N'_event_sessions WHERE name = ''' + @BufferXESessionName + N''')
BEGIN
Create the event session
CREATE EVENT SESSION ' + QUOTENAME(@BufferXESessionName) + N' ON ' + CASE WHEN @IsAzureSQLDB = 1 THEN N'DATABASE' ELSE N'SERVER' END + N'
ADD EVENT sqlserver.error_reported(
ACTION(
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.client_pid,
sqlserver.username,
sqlserver.database_name,
sqlserver.nt_username,
sqlserver.session_id,
sqlserver.sql_text
)
WHERE ' + @Filters + ISNULL(@ProcFilters, N'') + N'
)
' + CASE WHEN @UseFileTarget = 1 THEN
CONCAT(
N'ADD TARGET package0.event_file(SET filename=N', QUOTENAME(ISNULL(@FileTargetPath, @BufferXESessionName + '.xel'), N'''')
, N',max_file_size=(', @FileTargetMaxFileSizeMB , N'),max_rollover_files=(', @FileTargetMaxFiles, N'))'
)
ELSE
CONCAT(
N'ADD TARGET package0.ring_buffer(SET max_events_limit=(', @BufferMaxEventsCount, N'),max_memory=(', (@BufferMaxMemoryMB*1024), N'))'
)
END + N'
WITH (MAX_MEMORY=' + CONVERT(nvarchar, @BufferMaxMemoryMB) + N' MB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
;
END
Start the event session
IF NOT EXISTS (SELECT * FROM sys.dm_xe' + CASE WHEN @IsAzureSQLDB = 1 THEN N'_database' ELSE N'' END + N'_sessions WHERE name = ''' + @BufferXESessionName + N''')
BEGIN
ALTER EVENT SESSION ' + QUOTENAME(@BufferXESessionName) + N' ON ' + CASE WHEN @IsAzureSQLDB = 1 THEN N'DATABASE' ELSE N'SERVER' END + N' STATE = START;
END'
IF @PrintOnly = 1
BEGIN
SELECT @CMD AS GeneratedScript
END
ELSE
BEGIN
IF OBJECT_ID('tempdb..#xe') IS NOT NULL DROP TABLE #xe;
CREATE TABLE #xe (xdata VARBINARY(MAX));
BEGIN TRY
Query the event session data
INSERT INTO #xe
EXEC @Executor @CMD
END TRY
BEGIN CATCH
DECLARE @ERRNum INT = ERROR_NUMBER()
PRINT CONCAT(N'Error ', @ERRNum, N', Line ', ERROR_LINE(), N': ', ERROR_MESSAGE());
Error 574: statement cannot be used inside a user transaction.
IF @ERRNum = 574
BEGIN
SET @PrintOnly = 1;
SET @IsNestedTransaction = 1;
GOTO SetUpCommand;
END
END CATCH
IF OBJECT_ID('tempdb..#events') IS NOT NULL DROP TABLE #events
CREATE TABLE #events (event_xml XML);
IF @UseFileTarget = 1
INSERT INTO #events
SELECT xdata = CONVERT(xml, xdata) FROM #xe
ELSE
INSERT INTO #events
SELECT X.query('.')
FROM (SELECT xdata = CONVERT(xml, xdata) FROM #xe) AS a
CROSS APPLY xdata.nodes (N'//event') AS session_events (X)
Unfurl raw data
SELECT
[server_name] = @@SERVERNAME,
[database_name] = session_events.event_xml.value (N'(event/action[@name="database_name"]/value)[1]' , N'SYSNAME') ,
event_name = session_events.event_xml.value (N'(event/@name)[1]' , N'NVARCHAR(1000)') ,
event_timestamp_utc = session_events.event_xml.value (N'(event/@timestamp)[1]' , N'DATETIME2(7)') ,
session_id = session_events.event_xml.value (N'(event/action[@name="session_id"]/value)[1]' , N'INT') ,
error_number = session_events.event_xml.value (N'(event/data[@name="error_number"]/value)[1]' , N'INT') ,
severity = session_events.event_xml.value (N'(event/data[@name="severity"]/value)[1]' , N'INT') ,
state = session_events.event_xml.value (N'(event/data[@name="state"]/value)[1]' , N'INT') ,
category = session_events.event_xml.value (N'(event/data[@name="category"]/value)[1]' , N'INT') ,
category_desc = session_events.event_xml.value (N'(event/data[@name="category"]/text)[1]' , N'NVARCHAR(MAX)') ,
message = session_events.event_xml.value (N'(event/data[@name="message"]/value)[1]' , N'NVARCHAR(MAX)') ,
client_app_name = session_events.event_xml.value (N'(event/action[@name="client_app_name"]/value)[1]' , N'NVARCHAR(1000)') ,
client_host_name = session_events.event_xml.value (N'(event/action[@name="client_hostname"]/value)[1]' , N'NVARCHAR(1000)') ,
client_process_id = session_events.event_xml.value (N'(event/action[@name="client_pid"]/value)[1]' , N'BIGINT') ,
username = session_events.event_xml.value (N'(event/action[@name="username"]/value)[1]' , N'SYSNAME') ,
sql_text = session_events.event_xml.value (N'(event/action[@name="sql_text"]/value)[1]' , N'NVARCHAR(MAX)')
,event_xml
FROM #events AS session_events;
DROP TABLE #events;
END
CaptureTSQLErrors_XE_buffer.sql

Both scripts have various configurations that you can control using several variables at the top, such as minimum duration, minimum severity, what to ignore, how big the ring buffer should be, and more.

Once you run the script, your configurations are plugged in and a suitable script is generated for you. In some SQL versions you won’t have a choice but to copy the output script and use that in your actual monitoring solution. Other SQL versions will let you run the script immediately using the dynamically generated commands.

Either way, you’ll have what you need.

The scripts are provided as-is without guarantee. You can use them as a starting point to implement your own flavor of monitoring, or you can use them as they are if it suits you well enough.

Enjoy and good luck!

Cover image by geraldoswald62 from Pixabay.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.