InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 15:04:59 GMT20http://www.sqlservercentral.com/Forums/Topic1421907-391-1.aspxThe 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:[code]CREATE PROCEDURE SelectTableAASBEGIN SELECT * FROM TableA;ENDGO[/code]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.Wed, 20 Feb 2013 14:10:40 GMTDequehttp://www.sqlservercentral.com/Forums/Topic1421907-391-1.aspxThat'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...Wed, 20 Feb 2013 13:31:37 GMTSQLKnowItAllhttp://www.sqlservercentral.com/Forums/Topic1421907-391-1.aspxI 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 ([url]http://www.sqlskills.com/blogs/jonathan/understanding-the-sql_text-action-in-extended-events/[/url]) kind of describing the same thing for the sql_text action.here is my post from last year asking about the same thing([url]http://www.sqlservercentral.com/Forums/Topic1294710-391-1.aspx#bm1295378[/url])Wed, 20 Feb 2013 13:20:26 GMTRobert klimeshttp://www.sqlservercentral.com/Forums/Topic1421907-391-1.aspxNot 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.Wed, 20 Feb 2013 13:02:13 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1421907-391-1.aspx[quote][b]Lynn Pettis (2/20/2013)[/b][hr]How many SQL statements are there in the stored procedure?[/quote] 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).Wed, 20 Feb 2013 12:58:36 GMTSQLKnowItAllhttp://www.sqlservercentral.com/Forums/Topic1421907-391-1.aspxHow many SQL statements are there in the stored procedure?Wed, 20 Feb 2013 12:54:06 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1421907-391-1.aspxBump!Wed, 20 Feb 2013 12:43:23 GMTSQLKnowItAllhttp://www.sqlservercentral.com/Forums/Topic1421907-391-1.aspxI 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?Wed, 20 Feb 2013 06:50:36 GMTSQLKnowItAllhttp://www.sqlservercentral.com/Forums/Topic1421907-391-1.aspxWith my limited knowledge of extended events, I wrote the following:[code="sql"]IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='ExecCount_SPName') DROP EVENT SESSION ExecCount_SPName ON SERVER;CREATE EVENT SESSION ExecCount_SPNameON SERVERADD EVENT sqlserver.sp_statement_starting( WHERE (source_database_id = 12 AND object_id = 176807727))ADD TARGET package0.synchronous_event_counterGOALTER EVENT SESSION ExecCount_SPNameON SERVERSTATE=STARTGO[/code] and to query my results I run this:[code="sql"]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 OccurrenceFROM(SELECT s.name, CAST(target_data AS XML) as target_dataFROM sys.dm_xe_sessions AS s JOIN sys.dm_xe_session_targets AS t ON t.event_session_address = s.addressWHERE t.target_name = 'synchronous_event_counter' AND s.name 'ExecCount_SPName') as tabCROSS APPLY target_data.nodes('CounterTarget/Packages/Package/Event') as q(n)[/code]My concern is that my results show a higher number than actual executions of the SP. Can somebody explain this event a little more? Tue, 19 Feb 2013 18:17:20 GMTSQLKnowItAll