Post reply

Automatically output Extended Events data to table

  • March 1, 2022 at 10:37 am

    #3991024

    Hi there,

    I was wondering if/how I would be able to output the results of an Extended Events session to a database table, either in real time or on a regular schedule. I have seen it suggested that the ring buffer could be used as a target, then that could be queried regularly and copied into a table. Any ideas whether there are any better options, or does that sound the most feasible?

    Ideally I'd like to record events at least every 5 minutes, or real time if possible. I would anticipate there would probably be no more than a few hundred events at most in a 5 minute period.

    Thanks

  • March 1, 2022 at 2:24 pm

    #3991102

    Have a look how's it done in dbatools https://dbatools.io/xevents/

  • March 1, 2022 at 2:25 pm

    #3991103

    I shy away from the ring buffer as an output mechanism. It puts quite the load on the monitored machine. Instead, I'd suggest using file output. You could then use fn_xe_file_target_read_file to pull the information into a table on a scheduled basis. Here's an example of using that query.

    "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

  • March 1, 2022 at 5:04 pm

    #3991233

    I've found that a lot of people are using XE to do a lot of things that SQL Server already does better than you could do it with XE.  With that, I'll ask, what are you trying to monitor and why???

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 1, 2022 at 5:08 pm

    #3991235

    Brilliant, thank you both - very useful links!

  • March 1, 2022 at 5:13 pm

    #3991243

    You are probably right Jeff, the actual use case is to log specific query executions to a table...but now you've got me thinking of other potential ways...🤔

    It's partly for performance monitoring, and partly to keep a log of the specific requests coming in. We have a service which dynamically builds queries from our UI, so we are keen to keep track of what kind of requests we're receiving and how efficiently they are being executed.

    • This reply was modified 2 years, 9 months ago by  mg_dba.

  • March 1, 2022 at 5:58 pm

    #3991276

    I think this is a good use case for XE.  If you are on SQL 2016+ Query Store may be already doing what you want though, so you could look at that.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • March 1, 2022 at 6:22 pm

    #3991281

    Yeah, Jack's right. Query Store is a good way to go. It does aggregate the data you get (by the hour by default), so if you need detailed info, XE is still your buddy. But in general, QS does a fantastic job.

     

    "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

  • March 2, 2022 at 11:36 am

    #3991614

    Thanks all, Jeff's reply had me thinking about Query Store as we're on SQL 2019. Potentially both worth looking into though, cheers!

  • March 2, 2022 at 2:13 pm

    #3991685

    Oh, and 2019 introduces a bunch of good knobs for tweaking QS behaviors. You really can reduce the overhead quite a bit (QS is amazing, wonderful and cool, but yeah, introduces overhead, not all system loads will work well with it, testing is your buddy).

    "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

  • March 2, 2022 at 5:25 pm

    #3991858

    mg_dba wrote:

    You are probably right Jeff, the actual use case is to log specific query executions to a table...but now you've got me thinking of other potential ways...🤔

    It's partly for performance monitoring, and partly to keep a log of the specific requests coming in. We have a service which dynamically builds queries from our UI, so we are keen to keep track of what kind of requests we're receiving and how efficiently they are being executed.

    On that note, I'll agree that this is a good use case for XE.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 10 (of 10 total)

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