Sql-server – Counting the number of calls of a stored procedure

sql-clrsql-server-2008-r2stored-procedurestrigger

I have a CLR sp in SQL Server 2008 R2, and I want to count how often it is called, in order to create a statistic on data quality. (The sp allows manual correction of data).

How can I let a counter go up everytime the CLR sp is called? Do I necessarily have to change the sp itself?

Suggestions are appreciated.

Best Answer

There is no such thing as a stored procedure trigger.

If you want an absolutely accurate count, then yes, change your CLR procedure (or whatever wrapper(s) it is called from) to write an entry to a log somewhere, every time.

If "close enough" is "good enough" then you can periodically poll the DMV sys.dm_exec_procedure_stats which will tell you the execution_count at any point in time. This count is typically accumulated since the last service restart (I'm not sure if certain other activities, such as specific RECONFIGURE actions, reset this DMV as it does for some others). So, you would have to poll it periodically, and factor in any service restarts that have happened in the meantime.

Here is a simply query demonstrating how to poll execution counts at any given point in time:

USE your_database;
GO

SELECT 
  s.name, p.name, p.type_desc, 
  ps.execution_count, 
  ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
INNER JOIN sys.procedures AS p
ON ps.[object_id] = p.[object_id]
INNER JOIN sys.schemas AS s
ON p.[schema_id] = s.[schema_id]
WHERE ps.database_id = DB_ID();

Here is another bit of code that can clear out this DMV without a restart:

USE [master];
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'optimize for ad hoc workloads', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

There's a complete (at least AFAIK) list here:

http://mattsql.wordpress.com/2012/06/25/dont-believe-everything-you-read-reconfigure-flushes-the-plan-cache/

How you consolidate what happens when there has been a service restart (or any of these other DMV-clearing activities) is probably a completely different question.

Related Question