How to load Extended Events results from memory directly to table, in a script?

  • 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

     

  • 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
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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;

     

  • Have a look at dbatools Start-DbaXESmartTarget https://docs.dbatools.io/Start-DbaXESmartTarget

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply