locked
Extended events RRS feed

  • Question

  • Hi All,

    I have a question related to extended event ring buffer target.
    In SQL Server, how many ring buffer(s) will be there?
    For example, in below examples, I am creating 2 extended events whose target is a ring buffer. Is it two ring buffers will be created with 500 MB size or else only one 500MB buffer will be created and will be reused for both the extended event traces??

    CREATE EVENT SESSION azure_monitor
    ON DATABASE
    ADD EVENT sqlserver.error_reported(
    ACTION (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.session_id, sqlserver.sql_text,
            sqlserver.username))
    ADD TARGET package0.ring_buffer
    (SET max_memory = 500, max_events_limit = 10 )
    ;
    GO

    CREATE EVENT SESSION MonitorExpensiveQuery ON SERVER
    ADD EVENT sqlserver.sql_statement_completed
    (
    ACTION
    (
    sqlserver.database_id,sqlserver.session_id,sqlserver.username,sqlserver.client_hostname,sqlserver.sql_text,
    )
    --Specify predicates to filter out your events
    WHERE sqlserver.sql_statement_completed.cpu > 1000
    OR sqlserver.sql_statement_completed.duration > 10000
    )
    --Specify the target where event data will be written with ADD TARGET clause
    ADD TARGET package0.ring_buffer
    (SET max_memory = 500, max_events_limit = 10 )
    ;
    GO

    Thanks,

    Sam
    Thursday, July 26, 2018 9:39 AM

Answers

  • Hi Sam,

    Honestly, I'm not quite familiar with Ring Buffer Target.

    But according to Ring Buffer Target, the ring buffer target briefly holds event data in memory. And the first mode is strict first-in first-out (FIFO), where the oldest event is discarded when all the memory allocated to the target is used. 

    So per my understanding, there's no fixed number of Ring Buffer Target in SQL Server. It depends on the extended events you created. 

    And as in your scenario, you have created two events. So I think there will be two ring buffer, but only 500 MB memory will be used.

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Xi Jin Thursday, August 2, 2018 8:51 AM
    • Marked as answer by Olaf HelperMVP Saturday, August 11, 2018 4:43 AM
    Friday, July 27, 2018 4:24 AM