What I enjoy the most is working with different clients on Comprehensive Database Performance Health Check. Every client is different and what I learn from every organization is different. Recently, I had a very interesting experience with a client who had a database with over thousands of stored procedures. My clients were not sure which SPs are the recent SP and which SP is no longer used. Today we will learn about the recent execution of the stored procedure.
It is very common when people are working on the SQL Server Performance Tuning project to create multiple stored procedures to test variations of the stored procedure to check which works optimally. However, it is equally important to remove the stored procedures which are no longer used. Well, if you are wondering if they impact performance, the answer is dormant stored procedures do not have a direct impact on the SQL Server performance (or not that I know of it).
Here is the quick video where I am reading the data from the SQL Server Cache and list when was the stored procedure last executed as well as how many times it has been executed. Please remember that this data is from the SQL Server cache, hence, if your cache is reset in the recent time or if you have restarted SQL Server services or due to any reason, the procedure cache is removed, it will give not detail that information.
Here is the script which is demonstrated in the video to list the recent execution of the stored procedure.
SELECT
SCHEMA_NAME(sysobject.schema_id) SchemaName,
OBJECT_NAME(stats.object_id) SPName,
cached_time, last_execution_time,
execution_count,
total_elapsed_time/execution_count
AS avg_elapsed_time
FROM
sys.dm_exec_procedure_stats stats
INNER JOIN sys.objects sysobject
ON sysobject.object_id = stats.object_id
WHERE
sysobject.type = 'P'
ORDER BY
stats.last_execution_time DESCIf you have any idea which you would like me to build the SQL in the Sixty Seconds, please leave a comment and I will do my best to build the same. You may subscribe to my YouTube Channel here.
Reference: Pinal Dave (https://blog.sqlauthority.com)
12 Comments. Leave new
Hi Dave. I get your ‘SQL in sixty seconds’ every day and each time a reference is made to a video and a script, but they are never included in the email; they are missing ! Please explain and rectify.
Some email removes the embedding video and also javascript is not supported for it.
Minor change in script to include procedures which never executed after sql server restarted.
SELECT SCHEMA_NAME(pro.schema_id) SchemaName,
pro.name SPName,
cached_time,
last_execution_time,
execution_count,
total_elapsed_time / execution_count AS avg_elapsed_time
FROM sys.procedures pro
LEFT JOIN sys.dm_exec_procedure_stats stats
ON stats.object_id = pro.object_id
LEFT JOIN sys.objects sysobject
ON sysobject.object_id = stats.object_id
ORDER BY stats.last_execution_time DESC;
Great work Sanjay
Very interesting post Pinal and thanks Sanjay for the update. Very helpful! I’m currently trying to enhance the query to read also the query plan for the procedure and will post the statement later
Here the enhance query of Sanjay:
SET NOCOUNT ON;
SELECT
SCHEMA_NAME(P.[schema_id]) AS SchemaName
,OBJECT_NAME(P.[object_id]) AS ProcedureName
,PS.cached_time
,PS.last_execution_time
,PS.execution_count
,(PS.total_elapsed_time / PS.execution_count) / 1000 AS AVG_Elapsed_Milliseconds
,QP.query_plan
FROM
sys.procedures AS P
LEFT JOIN sys.dm_exec_procedure_stats AS PS ON P.[object_id] = PS.[object_id]
LEFT JOIN sys.objects AS B ON PS.[object_id] = B.[object_id]
LEFT JOIN sys.dm_exec_cached_plans AS CP ON PS.plan_handle = CP.plan_handle
OUTER APPLY sys.dm_exec_query_plan(PS.plan_handle) AS QP
WHERE
P.is_ms_shipped = 0
ORDER BY
PS.last_execution_time DESC, SCHEMA_NAME(P.[schema_id]), OBJECT_NAME(P.[object_id]);
SET NOCOUNT OFF;
Thank you Dominic, nice enhancement.
This is helpful. Thank you.
I was seeing some very strange results on a server hosting multiple databases. Adding a filter on stats.database_ID for the current database cleared things up.
INNER JOIN sys.objects sysobject ON sysobject.object_id = stats.object_id AND stats.database_id = DB_ID()
Had to add a clause on the join to sys.objects for a server hosting multiple databases:
INNER JOIN sys.objects sysobject ON sysobject.object_id = stats.object_id AND stats.database_id = DB_ID()
That’s really useful
What is the difference between sys.dm_exec_procedure_stats and sys.query_store_quer? The results using these two DMVs are quite different in my database