On many forums I have found a very basic yet important query - “How can I know when was my Stored Procedure last executed?” And today through this blog I will try to answer this question.
Actually speaking, without explicit logging or tracing, it is not possible to get this information for each and every Stored Procedure of our Database. However, we can get this detail along with many other relevant information for the stored procedure having it’s execution plan currently cached on the server by using - sys.dm_exec_procedure_stats It’s a system dynamic view that returns aggregate performance statistics for cached stored procedures.Please note that this view has been introduced from SQL Server 2008.
The important thing to note is that this view returns one row for each cached stored procedure plan, 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.
So, let’s see a way, using which at least we can find out the some important facts for the cached stored procedures -
The above script will return the name of all the cached stored procedure of the current database with their last execution time.
For more details on this dynamics view, please refer - sys.dm_exec_procedure_stats (Transact-SQL)
Actually speaking, without explicit logging or tracing, it is not possible to get this information for each and every Stored Procedure of our Database. However, we can get this detail along with many other relevant information for the stored procedure having it’s execution plan currently cached on the server by using - sys.dm_exec_procedure_stats It’s a system dynamic view that returns aggregate performance statistics for cached stored procedures.Please note that this view has been introduced from SQL Server 2008.
The important thing to note is that this view returns one row for each cached stored procedure plan, 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.
So, let’s see a way, using which at least we can find out the some important facts for the cached stored procedures -
USE DBName GO SELECT O.name, PS.last_execution_time FROM sys.dm_exec_procedure_stats PS INNER JOIN sys.objects O ON O.[object_id] = PS.[object_id] GOP.S. Please replace the DBName with the actual name of the Database.
The above script will return the name of all the cached stored procedure of the current database with their last execution time.
For more details on this dynamics view, please refer - sys.dm_exec_procedure_stats (Transact-SQL)
Hemantgiri,
ReplyDeleteThanks for your post on displaying the last execution date / time of a stored procedure.
I was looking for a way to generalize your approach to display the results for all cached stored procedures, and not just for those of a selected DB. I came up with the following revised query:
SELECT
CASE PS.database_id
WHEN 32767 THEN 'Resource DB'
ELSE DB_NAME(PS.database_id)
END AS [DB Name],
OBJECT_NAME(PS.object_id, PS.database_id) AS [SP Name],
PS.last_execution_time AS [Last Executed],
PS.execution_count AS [# runs]
FROM sys.dm_exec_procedure_stats PS
ORDER BY
DB_NAME(PS.database_id),
PS.last_execution_time DESC
The OBJECT_NAME function can find the name of an object by object ID and database ID (without requiring a JOIN clause), and removes the need for the JOIN clause to the sys.objects system view and the restriction of only returning results for the current DB.
I also added:
- # of stored procedure executions in the results (also provided by the sys.db_exec_procedure_stats DMV - other useful stats are included in this DMV)
- Use of the DB_NAME function to get the DB name of the stored procedure, with a CASE statement to include a DB name for the resource DB (DB ID 32767 - the resource DB name is not returned by the DB_NAME function).
Scott R.
Hi Scott,
ReplyDeleteThank you very much for sharing this generic approach.
Actually, the basic idea behind writing this blog was to make the readers aware about the new view - sys.dm_exec_procedure_stats also keeping in mind the very frequent query to know about the "Last Execution DateTime of any SP".
Secondly, while performance tuning, I believe its a general practice to take one DB at a time. And hence, considered targeting that.
However, please do keep following my post and sharing your valuable inputs.
Thanks!
Oh, be careful now! All that glitters is NOT gold! All of the “stats” dm views and functions have a problem for this type of thing. They only work against what is in cache and the lifetime of what is in cache can be measure in minutes. If you were to use such a thing to determine which SPs are candidates for being dropped, you could be in for a world of hurt when you delete SPs that were used just minutes ago.
ReplyDeleteThe following excerpts are from Books Online for the given dm views…
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
sys.dm_exec_query_stats
The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.
nice sir..
ReplyDelete