In this article I am going to share a brief Extended Events script which I have used several times to track a particular query and understand its impact on the server.

While the query store is getting popular and I often see it enabled on the systems, however there still are many production systems out there without the query store turned on.


The extended event script below captures rpc_completed and sp_statement_completed events.
rpc_completed event can be used to capture remote procedure calls coming from the application which could either call a stored proc or a batch of one or more statements.
sp_statement_completed should help track the individual statements within a stored proc.

You can also use the sql_statement_completed event to track a statement which is not a part of a stored proc.

I hope this helps someone out there!

-- First I got the query hash for the target query from sys.dm_exec_query_stats
-- You can also get the query hash from the query store in case you have it enabled

-- Convert the query hash to BIGINT
SELECT CAST(0xA6E42BFB8F5BE195 AS BIGINT);
-- -6420958809281535595

-- Create the XE session 
CREATE EVENT SESSION [DBA_PerfCapture_TargetQuery1] ON SERVER 
ADD 
  EVENT sqlserver.rpc_completed(
    ACTION(
      sqlserver.database_id, sqlserver.database_name, 
      sqlserver.server_principal_name, 
      sqlserver.session_id
    ) 
    WHERE 
      (
        [sqlserver].[database_id] =(11) 
        AND [package0].[equal_boolean](
          [sqlserver].[is_system], 
          (0)
        ) 
        AND [sqlserver].[like_i_sql_unicode_string](
          [sqlserver].[sql_text], N'%SELECT TestTable1.cola%'
        )
      )
  ), 
ADD 
  EVENT sqlserver.sp_statement_completed(
    ACTION(
      package0.collect_system_time, sqlserver.client_app_name, 
      sqlserver.client_hostname, sqlserver.database_name
    ) 
    WHERE 
      (
        [sqlserver].[database_id] =(11) 
        AND [sqlserver].[query_hash_signed] =(-6420958809281535595.)
      )
  ) 
ADD 
  TARGET package0.event_file(
    SET 
      filename = N'D:\XELogs\DBA_PerfCapture_DBA_PerfCapture_TargetQuery1.xel', 
      max_file_size =(100), 
      max_rollover_files =(20)
  ) 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 = ON, STARTUP_STATE = OFF
  ) 
GO