Recent Execution of Stored Procedure – SQL in Sixty Seconds #118

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)