Audit on Stored Procedures 1

[bowl3y]

Does SQL 2005 maintain any form of an Audit on how often OR when stored procedures are Executed?

My Dataware house currently has ALOT SP's and I am wanting to clean it up a little. And Not knowing which ones are system critical is a pain. I have tossed around the idea of adding a simple insert into a table on every SP and letting that keep track. However, if SQL 2005 has this built-in I didnt want to reinvent the wheel.

Thanks

 

[SQLDenis]

here take a look at this

usecounts has the number of times the proc was executed

SELECT cp.cacheobjtype, usecounts,object_name(qp.objectid)
 FROM sys.dm_exec_cached_plans cp 
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
 WHERE objtype ='Proc'

if you need to check for a specific proc add

and qp.objectid = OBJECT_ID('ufnGetAccountingStartDate')

change ufnGetAccountingStartDate to your proc name

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[bowl3y]

This is Perfect!

You are the MAN!!!

Quick question though, does this grab the usuage of all SP's or just the ones that have been executed recently enough to have a exection plan still cached?


Bowley...

 

[SQLDenis]

It is cleared when the service is restarted

read more here

http://msdn2.microsoft.com/en-us/library/ms189747(SQL.90).aspx

and

http://msdn2.microsoft.com/en-us/library/ms187404(SQL.90).aspx

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[SQLSister]

Now remember the use count isn't the whole story. Annual reports might only get run once a year but you could be introuble for deleting them. I would use this info only to identify which procs need to be investigated for deletion not to automatically delete it.

Questions about posting. See faq183-874

 

[bowl3y]

Noted....Thanks




Bowley...

 

[SQLDenis]

this is what I usually do:
if I am not sure I rename the proc, i prefix it with __ ( 2 underscores) so that they are on top when sorted alphabetically, then after a while I just drop it (I have it in my subversion repository anyway in case I need I back 2 years later)



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions