Auditing use of stored procs

Question

Auditing use of stored procs

matthew-659838

SSC Rookie

Points: 32
More actions
August 6, 2008 at 4:49 am

#196507

Hi,
What's the easiest way to have an ongoing audit of the execution of stored procs in a particular database? Ideally I'd like to end up with a table that has no more than 1 row per stored proc and with the last execution date/time and a count of executions, perhaps (very roughly) like this:
SPName varchar(100),
LastRunOn datetime
RunCount int
thanks in advance,
Matt

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

JuncTheMayl Hall of Fame Points: 3912 More actions · Answer 1

are you looking to do this on ALL stored procs or just certain ones? if all, then you'd probably be best off running a profiler trace against the server looking only for stored procs, then having it dump the data to a table that you can massage. if it's only certain ones, you can write a logging routine right into the proc.

you can also query various system tables/dmvs, but you'll only get results for that point in time.

Anders Pedersen SSChampion Points: 11410 More actions · Answer 2

I did this last year on the system I took over managing using a server side trace writing to a file, then loaded the files into a table so I could query the data easily. Works well, and with writing to a file that is not on a partition used by SQL I wouldn't run out of hard drive space because of it.

I grabbed some extra data while I was at it, such as run time, so I could use it for two purposes, to see what stored procedures had been used, and to get some performance indication, i.e. give me a list of the procedures that had the highest cummulative run time to see if they needed optimizing.

matthew-659838 SSC Rookie Points: 32 More actions · Answer 3

Hi, thanks for the replies. The purpose is to see which stored procs are still in use and perhaps get an indication of how busy they are, so we would be auditing all of them. I was hoping not to have to run the profiler if possible but it looks like that's the only option.

JuncTheMayl Hall of Fame Points: 3912 More actions · Answer 4

if you use profiler, filter it heavily so it impacts the server as little as possible (and takes up as little space as possible). and if you can, run it from a different machine.