MS SQL Server - count sproc executions

Author

count sproc executions

sam.m.gardiner

2006-10-24, 6:37 pm

is there a dynamic view or function that I can call to find the number
of times a sproc or function has executed?

I am looking at a report generated from the summary page in sql server
2005 management studio that says a function has executed 12,000,000
(twelve million) times in the last 24 hours (i noted that doing dbcc
freesystemcache('ALL
') reset the count). This seems incredible and I'd
like find some other source for this information. I can imagine that
the function is being called 1,000,000 (one million) times as it's
being used rather stupidly as a sub-query on another proc that is being
called 10,000 (ten thousand) times per day.

fyi, I'm running sql server 2005 sp1 on a 1gb transaction processing
database.

Paul Ibison

2006-10-24, 6:37 pm

Please try this in the correct database:

SELECT usecounts, object_name(objectid
) as Procname

FROM sys. dm_exec_cached_plans
cp CROSS APPLY
sys. dm_exec_query_plan(c
p.plan_handle)

where objtype = 'proc'

Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

sam.m.gardiner

2006-10-24, 6:37 pm

thanks for this paul, exactly what I need.

unfortunately when I run this I get the error

Incorrect syntax near '.'.

but I can't seem to fix it.
If I select from dm_exec_cached_plans
I get some results, and if I take
a random plan_handle and then run..

<code>
select * from
sys. dm_exec_query_plan(0
x050004000AA92912B8E
1003B000000000000000
000000000)
</code>

this also works, but you very useful looking join statement fails :-(

sam.m.gardiner

2006-10-24, 6:37 pm

ah I now see the problem, database compatibility needs to be set to 90.

thanks for this paul, excellent.

Paul Ibison

2006-10-24, 6:37 pm

Just a quick caveat - this is based on the sql server cache so please don't
use the figures as absolute ones. For persistant logging, you'll need to
write to an audit table from within the proc.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Sponsored Links