August 22, 2019 at 10:32 am
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?
August 22, 2019 at 11:29 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 22, 2019 at 1:17 pm
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.
August 22, 2019 at 1:52 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 22, 2019 at 2:25 pm
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.
August 22, 2019 at 6:06 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply