CODEWITHSUNDEEP
sqlsql-servert-sqlsqlclr
Yagi Gogi
Yagi Gogi

Reputation: 35

How to check how many times CLR function was called from stored procedure without changing procedure's code?

I have a stored procedure with some CLR functions inside it. I need to check how many times these functions are being called during one stored procedure execution.

I have found this select at StackOverflow, but it doesn't show CLR functions:

SELECT TOP 25
DB_NAME(fs.database_id) DatabaseName,
OBJECT_NAME(object_id, database_id) FunctionName,
fs.cached_time, fs.last_execution_time, fs.total_elapsed_time,
fs.total_worker_time, fs.total_logical_reads, fs.total_physical_reads,
fs.total_elapsed_time/fs.execution_count AS [avg_elapsed_time],
fs.last_elapsed_time, fs.execution_count
FROM sys.dm_exec_function_stats AS fs
ORDER BY [total_worker_time] DESC;

Upvotes: 2

Views: 82

Answers (1)

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

I would go for creating an extended event session to track executions of CLR :

 CREATE EVENT SESSION [Track_CLR_Executions] ON SERVER 
ADD 
  EVENT sqlserver.module_start(
    ACTION(
      sqlserver.sql_text, sqlserver.database_id, 
      sqlserver.client_hostname, sqlserver.client_app_name
    ) 
    WHERE 
      (
        sqlserver.like_i_sql_unicode_string(
          sqlserver.sql_text, N '%CLRFunctionName%'
        )
      )
  ) 
ADD 
  TARGET package0.event_file(
    SET 
      filename = N 'Track_CLR_Executions'
  ) 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
  )

Upvotes: 1

Related Questions