September 20, 2022 at 3:28 pm
Hi all,
We created Extended Events sessions that dump results into a file. Then, with a scheduled job we load it into a database table. We want to eliminate the part dumping to a file, and load it directly from buffer ring (which is memory) to a table. We don't use GUI built-in into SSMS, as it has large impact on a server.
Is it possible to do? And if yes, then how?
Thanks
September 21, 2022 at 6:35 am
You can output to the buffer as a target. However, that's not recommended. It puts more of a load on the system than output to a file. I prefer a file.
If you do choose to output to the buffer, then, you'll need to set up a scheduled query to pull information from the buffer to input to your table. How often that runs really depends on the events captured, how long they can stay in memory, memory pressure, all sorts of stuff. I've never tried to do this because, output to the buffer is not how I would set up Extended Events.
Here's one, of many, posts backing up the idea that the ring_buffer is a bad approach to dealing with Extended Events by Jonathan Kehayias (person who got me started on Extended Events).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 21, 2022 at 5:45 pm
If you need to extract the data from the XE output files and put it into tables, you can use the sys.fn_xe_file_target_read_file() function.
As an example, here is a procedure I created early last year to do exactly that. It runs at a scheduled interval in an agent job.
USE DatabaseName
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER PROCEDURE dbo.xyz_procedure_stats_capture
/* =================================================================================================================================
??/??/???? JL, Created: Captures XE information from the "xyz_procedure_end*.xel" file and parses it into relivant tables.
================================================================================================================================= */
/*
EXEC dbo.xyz_procedure_stats_capture
*/
AS
BEGIN
SET XACT_ABORT, NOCOUNT ON;
BEGIN TRY
------------------------------------------------------------------------------------------------------------------------
DECLARE
@capture_dt datetime2(3) = SYSDATETIME(),
@uct_offset int = DATEDIFF(HOUR, SYSUTCDATETIME(), SYSDATETIME()),
@file_name nvarchar(260) = NULL,
@file_offset bigint = NULL;
SELECT TOP (1)
@file_name = xch.last_file_name,
@file_offset = xch.last_file_offset
FROM
DatabaseName.dbo.xyz_procedure_end_xe_capture_history xch
ORDER BY
xch.capture_dt DESC;
------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#xyz_procedure_stats', 'U') IS NOT NULL
BEGIN DROP TABLE #xyz_procedure_stats; END;
CREATE TABLE #xyz_procedure_stats (
file_name nvarchar(260) NOT NULL,
file_offset bigint NOT NULL,
utc_timestamp datetime2(3) NOT NULL,
database_id int NOT NULL,
object_id int NOT NULL,
duration bigint NOT NULL,
row_count bigint NOT NULL
);
INSERT #xyz_procedure_stats (file_name, file_offset, utc_timestamp, database_id, object_id, duration, row_count)
SELECT
ed.file_name,
ed.file_offset,
utc_timestamp= v.value('(@timestamp)[1]', 'datetime2(3)'),
database_id= v.value('(data[@name = "source_database_id"]/value)[1]', 'int'),
object_id= v.value('(data[@name = "object_id"]/value)[1]', 'int'),
duration= v.value('(data[@name = "duration"]/value)[1]', 'bigint'),
row_count= v.value('(data[@name = "row_count"]/value)[1]', 'bigint')
FROM
sys.fn_xe_file_target_read_file('D:\XE_Logs\xyz_procedure_end\xyz_procedure_end*.xel', NULL, @file_name, @file_offset) ed
CROSS APPLY ( VALUES (CONVERT(xml, ed.event_data)) ) x (event_data)
CROSS APPLY x.event_data.nodes('event') xd(v);
------------------------------------------------------------------------------------------------------------------------
BEGIN TRANSACTION; -- both inserts need to fail or succeede together.
INSERT DatabaseName.dbo.xyz_procedure_stats (timestamp, database_id, object_id, duration_mcs, row_count)
SELECT
timestamp = DATEADD(HOUR, @uct_offset, eps.utc_timestamp),
eps.database_id,
eps.object_id,
eps.duration,
eps.row_count
FROM
#xyz_procedure_stats eps
ORDER BY
eps.utc_timestamp,
eps.object_id;
INSERT DatabaseName.dbo.xyz_procedure_end_xe_capture_history (capture_dt, last_file_name, last_file_offset)
SELECT TOP (1)
capture_dt = @capture_dt,
eps.file_name,
eps.file_offset
FROM
#xyz_procedure_stats eps
ORDER BY
eps.file_name DESC,
eps.file_offset DESC;
COMMIT TRANSACTION;
------------------------------------------------------------------------------------------------------------------------
END TRY
BEGIN CATCH
THROW;
END CATCH;
END;
September 22, 2022 at 1:18 pm
Have a look at dbatools Start-DbaXESmartTarget https://docs.dbatools.io/Start-DbaXESmartTarget
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply