How do you check when a stored procedure was last executed?

How do you check when a stored procedure was last executed?

7 Answers

1. Run a profiler trace with, say, the stored proc name.
2. Add a line each proc (create a tabel of course) ” INSERT dbo.SPCall (What, When) VALUES (OBJECT_NAME(@@PROCID), GETDATE() “
3. Extend 2 with duration too.

How would you optimize a query or Stored Procedure?

Improve stored procedure performance in SQL Server

1. Use SET NOCOUNT ON.
2. Use fully qualified procedure name.
3. sp_executesql instead of Execute for dynamic queries.
4. Using IF EXISTS AND SELECT.
5. Avoid naming user stored procedure as sp_procedurename.
6. Use set based queries wherever possible.
7. Keep transaction short and crisp.

Are there any stored procedures in SQL Server?

One of our developers after reviewing the SQL Server database objects of an old database found that the database has too many stored procedures to reasonably manage them all. He thinks that most of the procedures are not in use, but he wants to get more information about the stored procedure usage before we delete them.

How to track the creation of a procedure in SQL Server?

Please provide code samples that enumerate the stored procedures in a SQL Server database as well as the T-SQL code that defines them. Also, present code samples showing how I can track the creation and last modified dates for stored procedures as well as when they last ran.

When does SQL Server remove procedure execution statistics?

Execution statistics on a very busy SQL Server could be removed from the DMV before the job runs and therefore you miss data. The row with procedure execution statistics will be removed after the stored procedure is removed from the SQL Server cache (read more here ).

What happens to the lifetime of a stored procedure?

sys.dm_exec_procedure_stats Returns aggregate performance statistics for cached stored procedures. The view contains one row per stored procedure, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view.