patternsqlMinor
What can I add to an Extended Event to return correct row_counts?
Viewed 0 times
canwhatreturnrow_countsextendedcorrectaddevent
Problem
I'm running an Extended Event session in SQL Server 2017 that's only recording
When looking at some of the results, I noticed that there were a few procedures on the list that had tens of millions of executions, but had never once had a
To get a better look at the parameters the procedures had been called with, I started up a Profiler session watching for just those procedure calls. The particular procedure I was checking was being called extremely frequently, and I got a good number of example calls rather quickly, and turned off the Profiler. There were a couple things noticed from this:
Below is the definition of the Extended Event that I'm using:
```
Create Event Session [Test Event] On Server
Add Event sqlserver.module_end
(
Action (sqlserver.database_name, sqlserver.session_id)
Where
(
sqlserver.database_name = N'TestDatabase'
And object_type = 'P'
)
)
Add Target package0.ring_buffer (Set max_events_limit = 10000, max_memory = 51200)
With
(
Max_Memory = 4096KB,
Event_Retention_Mode = Allow_Single_E
module_end events for a specific database. The reason for this trace is two-fold:- I'm wanting to see the frequency (or lack-thereof) for which a stored procedure has been called.
- I'm trying to get some specifics for each procedure call (e.g. who called it, from where, how long it took, how many records were returned, etc.).
When looking at some of the results, I noticed that there were a few procedures on the list that had tens of millions of executions, but had never once had a
row_count greater than 0. There were plenty of other recorded events with row_count seemingly properly populated, so I had initially considered that the ones that had never returned a result had been miscalled by the applications referencing them. However, this turned out not to be the case.To get a better look at the parameters the procedures had been called with, I started up a Profiler session watching for just those procedure calls. The particular procedure I was checking was being called extremely frequently, and I got a good number of example calls rather quickly, and turned off the Profiler. There were a couple things noticed from this:
- The calls had legitimate parameters.
- Having made no changes to the Extended Event session, the recorded procedure calls during the time that the Profiler was active magically started recording the
row_countcorrectly as1instead of0.
Below is the definition of the Extended Event that I'm using:
```
Create Event Session [Test Event] On Server
Add Event sqlserver.module_end
(
Action (sqlserver.database_name, sqlserver.session_id)
Where
(
sqlserver.database_name = N'TestDatabase'
And object_type = 'P'
)
)
Add Target package0.ring_buffer (Set max_events_limit = 10000, max_memory = 51200)
With
(
Max_Memory = 4096KB,
Event_Retention_Mode = Allow_Single_E
Solution
One case I know of is when the procedure is called via the Service Broker Activation.
I was able to reproduce (please note, I've added an extra statement to your procedure so that it would return more rows - a select from values (1) (2) (3)).
Notice that the last column is_system is True for executing the Activation procedure. This column is not tracked by default; you have to add it via Global Fields (Actions)
I was able to reproduce (please note, I've added an extra statement to your procedure so that it would return more rows - a select from values (1) (2) (3)).
Notice that the last column is_system is True for executing the Activation procedure. This column is not tracked by default; you have to add it via Global Fields (Actions)
Context
StackExchange Database Administrators Q#313925, answer score: 3
Revisions (0)
No revisions yet.