Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Do you want the stats to persist if the instance is restarted? –  Jon Seigel Sep 11 '13 at 15:44
    
@Jon Wouldn't be persistance trivial? If I can get the counter from someplace then I can save it into a log table. –  Rafael Cichocki Sep 11 '13 at 15:59
1  
Does sys.dm_exec_procedure_stats keep track of CLR procedures? If so, you could use that, just be aware that the counter will not persist through service restarts. So, if you need it to persist, you could poll that view every so often and log it, but you may have a hard time consolidating deltas between restarts. For example, at 10:00, the counter is 10, and at 11:00, the counter is 15. Did SQL Server restart in between? It could have been called exactly 5 more times, or 15 or more times in that hour. –  Aaron Bertrand Sep 11 '13 at 16:22
    
Do you need an exact count, or an approximate count? –  Jon Seigel Sep 11 '13 at 19:23
    
@Aaron: I checked: yes it does track CLR procedures. –  Jon Seigel Sep 11 '13 at 19:27
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
    
Is there a way to identify the last restart? Say, a timer that says how long the instance has been running. That way I could save my counts with a datetime, and see if since the last count a restart has occurred. It seems that sys.dm_exec_procedurestats is the way to go for me. –  Rafael Cichocki Sep 12 '13 at 8:24
    
@RafaelCichocki yes, SELECT MIN(login_time) FROM sys.dm_exec_sessions; (courtesy Paul Randal). But just to be pedantic, you could still have missed some executions that happened between the last poll and the service stop, and also there could have been multiple restarts between your last poll and the current start. –  Aaron Bertrand Sep 12 '13 at 13:06
    
I'm new to Data handling, is it common practice to regularly restart a DB instance? Anyways, my DB isn't affected by that so often, so I think the statistical inaccuracy will be acceptable. Thanks for the the directions to the meta-data tables! –  Rafael Cichocki Sep 12 '13 at 14:14
1  
It's probably more common than you think. At my old job (SQL Server 2005, mind you) we had to failover our primary production cluster every other weekend to alleviate unchecked memory pressure (turned out to be a bug in the Temp Tables for Destruction code). If you search SO or here for "restart sql server" you will find that many questions are along the lines of "why do I have to occasionally restart SQL Server to get good performance?" Also keep in mind that other things can clear this DMV even if the service isn't restarted. Will add info to answer. –  Aaron Bertrand Sep 12 '13 at 14:32
    
If (a) the number of calls is relatively consistent over time, and (b) you know the last time the info was polled, and (c) you know the time the instance came back up, you could interpolate the number of calls between the last poll and the instance restart. That's still approximate, mind you, but might be good enough. Or maybe ignoring those calls is still going to be good enough. –  Jon Seigel Sep 12 '13 at 14:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.