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 DESC
If 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)