How do I log the frequency and last-used time for a stored procedure?

Question

I want to know how often a set of stored procedures run, and the last time they were used.

I am thinking of adding calls to the top of every stored procedure in the database to insert/update a table, with the following schema:

SprocName    ExecCount   LastExec
----------------------------------
GetCompany   434         2009-03-02
ExportDist   2           2008-01-05

Obviously, adding code to every sproc isn't exactly productive.

Is there a built in feature of SQL Server 2005 that can help?

Or is there a better way?

Answer 1

This isn't an answer but our DBAs have this same info available w/o logging statements inside sprocs. I don't know offhand how they do it but at least it is possible..

Answer 2

Hi,

There's an MSDN blog here that talks about various options. For SQL 2005, this boils down to:

• Do a server side tracing, be aware that this is not a light weight option to do
• Change your stored procedures to include the logging of the execution

Answer 3

I found a thread on a forum that talks about using sys.syscacheobjects to find out?!?

Answer 4

You may want to check out the contents of the table sys.dm_exec_query_stats.

Here's a blog post for something similar: http://blog.sqlauthority.com/2008/03/22/sql-server-2005-find-highest-most-used-stored-procedure/

Answer 5

I'm not answering with much experience, but I think that other options shown here like tracing would detriment performance, while adding a line at the top of each proc would be very lightweight during execution (even tough a lot of work depending on how many you have).

I would build one new SP that logs and takes as a parameter the name of the calling SP and having the logic where to insert or update. That way you only add one line to your other SP and pass their name as a parameter.

Answer 6

The most accurate method to achieve your objective is to use a custom logging solution that is built into your stored procedures.

You can use the SQL Server Dynamic Management Views(DMV's), as others have eluded to, to get a rough idea of the queries/stored procedures that are being executed on your server however the actual purpose for these DMV's is to provide an insight into performance tuning, not to provide an audit trail.

For example: How to identify the most costly SQL Server queries using DMV’s

The data provided by the DMV's in question (sys.dm_exec_query_stats etc.) only details the query plans that are currently stored within the SQL Server Plan Cache and so can only provide a limited perspective of server activity.

SQL Server Books Online: sys.dm_exec_query_stats