Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extended Events for counting execution of Stored Proc Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2013 6:17 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:07 PM
Points: 2,691, Visits: 3,371
With my limited knowledge of extended events, I wrote the following:
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='ExecCount_SPName')
DROP EVENT SESSION ExecCount_SPName ON SERVER;
CREATE EVENT SESSION ExecCount_SPName
ON SERVER
ADD EVENT sqlserver.sp_statement_starting
( WHERE (source_database_id = 12
AND object_id = 176807727))
ADD TARGET package0.synchronous_event_counter
GO

ALTER EVENT SESSION ExecCount_SPName
ON SERVER
STATE=START
GO

and to query my results I run this:
SELECT tab.name,
n.value('../@name[1]', 'varchar(50)') as PackageName,
n.value('@name[1]', 'varchar(50)') as EventName,
n.value('@count[1]', 'int') as Occurrence
FROM
(
SELECT s.name, CAST(target_data AS XML) as target_data
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t
ON t.event_session_address = s.address
WHERE t.target_name = 'synchronous_event_counter'
AND s.name 'ExecCount_SPName'
) as tab
CROSS APPLY target_data.nodes('CounterTarget/Packages/Package/Event') as q(n)

My concern is that my results show a higher number than actual executions of the SP. Can somebody explain this event a little more?


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1421907
Posted Wednesday, February 20, 2013 6:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:07 PM
Points: 2,691, Visits: 3,371
I recreated the session and added a ring buffer target as well. When the stored proc was executed once, I found 21 rows of data in the results (events) all with different offsets. Does anyone know what this is actually counting?

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1422058
Posted Wednesday, February 20, 2013 12:43 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:07 PM
Points: 2,691, Visits: 3,371
Bump!

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1422253
Posted Wednesday, February 20, 2013 12:54 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 23,004, Visits: 31,496
How many SQL statements are there in the stored procedure?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1422258
Posted Wednesday, February 20, 2013 12:58 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:07 PM
Points: 2,691, Visits: 3,371
Lynn Pettis (2/20/2013)
How many SQL statements are there in the stored procedure?
Several. This is a stored proc that should be a large Method in C# Do you think, or know, that that is what is causing this? If so, any thoughts on limiting it easily; i.e. WHERE offset = 0 or something (I tried that specific one, it didn't work).


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1422261
Posted Wednesday, February 20, 2013 1:02 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 23,004, Visits: 31,496
Not familiar with the extended events. All I can think of is the sp_statement_starting is conting the start of each SQL statment in the procedure each time one is executed.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1422265
Posted Wednesday, February 20, 2013 1:20 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:34 AM
Points: 1,343, Visits: 2,518
I have come across this as well and it seems that the sp_statement_started and sp_statement_completed do not return individual statements within a stored proc. As Lynn suggested, it is in fact returning one row for each statement within the proc but sp_statement_started and sp_statement_completed is only returning the sql from the outside proc that stated the batch. I do believe this has changed in 2012 but haven't revisited.

here is a post from Jonathan Kehayias (http://www.sqlskills.com/blogs/jonathan/understanding-the-sql_text-action-in-extended-events/) kind of describing the same thing for the sql_text action.

here is my post from last year asking about the same thing(http://www.sqlservercentral.com/Forums/Topic1294710-391-1.aspx#bm1295378)


Bob
-----------------------------------------------------------------------------
How to post to get the best help
Post #1422278
Posted Wednesday, February 20, 2013 1:31 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:07 PM
Points: 2,691, Visits: 3,371
That's helpful, but it refers to SQL_statement_completed, not sp_statement_completed. Is it possible that sp_statement_completed refers to each statement within the stored proc? I'm still a bit confused...

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1422285
Posted Wednesday, February 20, 2013 2:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 12:13 PM
Points: 37, Visits: 1,629
The text for the sp_statement_starting event specifically says "Occurs when a statement inside a stored procedure has started.", so I do believe it is counting the SQL statements inside your stored procedure.

I created a very basic stored procedure to test:
CREATE PROCEDURE SelectTableA
AS
BEGIN
SELECT * FROM TableA;
END
GO

If I modify your code to create the session using my database ID and object_ID, then every time I execute SelectTableA, the occurrence count goes up once. If I add another statement in there, it increments by two every time.

According to this link by Johnathan Kehayias (http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/20/an-xevent-a-day-20-of-31-mapping-extended-events-to-sql-trace.aspx), you should be using module_start to capture the SP:Starting events (although, it seems strange that SP:Recompile would use sp_statement_starting, but that's probably my ignorance).

I did test it by changing to sqlserver.module_start and it does appear to do what you're looking to do, but I didn't do a lot of testing with it.
Post #1422297
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse