February 19, 2013 at 6:17 pm
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?
Jared
CE - Microsoft
February 20, 2013 at 6:50 am
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?
Jared
CE - Microsoft
February 20, 2013 at 12:43 pm
Bump!
Jared
CE - Microsoft
February 20, 2013 at 12:54 pm
How many SQL statements are there in the stored procedure?
February 20, 2013 at 12:58 pm
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).
Jared
CE - Microsoft
February 20, 2013 at 1:02 pm
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.
February 20, 2013 at 1:20 pm
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)
February 20, 2013 at 1:31 pm
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...
Jared
CE - Microsoft
February 20, 2013 at 2:10 pm
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply