I did a DuckDuckGo.com search for the like_i_sql_unicode_string
since there appears to be no documentation that describes it included with SQL Server, and came across this Connect item, https://connect.microsoft.com/SQLServer/feedback/details/699053/sql-batch-completed-event-returns-0-for-row-count#details - it contains a sample extended event in the "steps to reproduce" section of the Connect item. In the sample extended event is this text, which wraps the search terms in question in the standard SQL Server wildcard, %
.
WHERE ([sqlserver].[like_i_sql_unicode_string]([batch_text],N'%sys.objects%')))
I modified my extended event definition like:
DROP EVENT SESSION FindCaller ON SERVER;
CREATE EVENT SESSION [FindCaller] ON SERVER
ADD EVENT sqlserver.sql_batch_starting
(
ACTION
(
sqlserver.client_app_name
, sqlserver.client_hostname
, sqlserver.database_name
, sqlserver.nt_username
, sqlserver.session_id
, sqlserver.sql_text
)
WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ObjectInQuestion%'))
)
WITH
(
MAX_MEMORY=4096 KB
, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS
, MAX_DISPATCH_LATENCY=30 SECONDS
, MAX_EVENT_SIZE=0 KB
, MEMORY_PARTITION_MODE=NONE
, TRACK_CAUSALITY=OFF
, STARTUP_STATE=OFF
);
Now, when I 'Watch Live Data' I see the culprit statements, along with the actual SQL text being executed, the name of the application, the client machine name, etc. Quite useful.
The take-home for me is the Extended Event GUI needs better documentation!
Looks like the issue is because my stored procedures and ad hoc queries might be running more than one sql statement ... thus creating multiple hits. Really what I need is batch complete. Alas, it does not exist in SQL Server 2008. Probably gonna have to resort to a server trace.
Best Answer
To answer my own question - when a stored procedure execution is aborted then this is logged as an event, in which case I don't see the need to capture both
module_start
andmodule_end
events for my purpose.