How to record the frequency and time of the last use of a stored procedure?

Question

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

I am going to add calls to the beginning of each 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 each sproc is not productive.

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

Or is there a better way?

Answer 1

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

• , ,
• ,

Answer 2

sys.dm_exec_query_stats.

- : http://blog.sqlauthority.com/2008/03/22/sql-server-2005-find-highest-most-used-stored-procedure/

Answer 3

thread , sys.syscacheobjects, ?!?

Answer 4

, , , , , proc ( ).

SP, SP , . , SP .

Answer 5

, .

Dynamic Management Views (DMV) SQL Server, , / , , DMV , .

: SQL Server DMV

, DMV (sys.dm_exec_query_stats ..), , - SQL Server .

SQL Server: sys.dm_exec_query_stats