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;
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
)