Extended events

  • as1981

    SSCrazy

    Points: 2733

    I'm trying to teach myself ExtendedEvents (trying to move from profiler).

    I've attached a configuration and the output. I've got 'statement' selected in event fields and 'sql text' selected in global fields but the output doesn't show the sql statement that has been run and I'm not sure why?

     

    Attachments:
    You must be logged in to view attached files.
  • Grant Fritchey

    SSC Guru

    Points: 396560

    Toss the global field. It's not needed here at all and the global fields are also called Actions because they can act like triggers. I minimize their use as much as possible (although I will use them, especially database_id, great filter to add if it's not a part of the package of a given event).

    You're running a stored procedure, but the statement, or statements, within the procedure is not showing up? Is there anything in particular about the stored procedure is it straight up standard stuff? I think you may see blanks for some DDL statements. I'd have to go and run some tests to be sure about that. Overall, this stuff just works, so I'd guess something is at play here.

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

  • as1981

    SSCrazy

    Points: 2733

    Thanks for your help.

    I am trying to get the sql statement to be one of the result columns. I use profiler to see sql statements and their cpu and io levels to identify anything that I can optimise. I was attempting to teach myself how to use Extended Events to do the same but failing.

    It's purely a teaching exercise on a test system, sorry if I gave the impression otherwise or posted in the wrong section. Apologises if I missed an existing tutorial on this when I searched.

  • Grant Fritchey

    SSC Guru

    Points: 396560

    So what are you trying to capture? Is it individual statements from a stored procedure? You're using the sp_statement_completed event. The "sp" at the start stands for stored procedure. If you're trying to capture individual statements from a batch, you want sql_statement_completed. These are both very granular events. Generally, I use sql_batch_completed and rpc_completed as the two events I use to watch T-SQL batch operations and stored procedures (or prepared statements). They capture overall behavior of the batch or procedure, not the individual statement. None of these need to add the global field you added. They will all, by default, capture the appropriate calling command. They should show you what you're looking for, including execution time (in microseconds, not milliseconds, worth noting), I/O, CPU and quite a bit more.

    You're not doing anything wrong and I sure don't think I've rebuked you in some way, so don't worry, let's just talk about what you're trying to get done.

    If you want to see a bunch of examples, I'd suggest going to my blog.

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

  • as1981

    SSCrazy

    Points: 2733

    Thanks for the re-assurance. I didn't feel you rebuked me, my comments came from my own caution, not your comments.

    As you realised I was using the wrong event.

    Whilst I corrected that I found something else I had done wrong. On the 'data storage' tab I selected 'event_counter' as the target. I changed it to event_file and I'm nearer to my intended output. I have event name and timestamp in the result file but not the sql statement . If I figure it out myself I'll post in case someone else gets the same issue.

    Thanks for the blog link, I'll have a look.

    Edit: The answer to the missing column is in the linked blog here https://www.scarydba.com/2019/06/03/which-query-used-the-most-cpu-implementing-extended-events

    It case it helps others - I think the mistake I made is thinking that selecting the column, as shown in the attached, would mean it is would be added to the view of the result file automatically.

    • This reply was modified 10 months, 3 weeks ago by  as1981.
    Attachments:
    You must be logged in to view attached files.
  • Grant Fritchey

    SSC Guru

    Points: 396560

    Happy to hear that things are working. Come back with questions any time.

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

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

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