Stored Procedure Cache Clearing and Execution Time
I'm trying to determine the last time that a stored procedure was executed (or called). Based on my research, we can't really determine this (correct me here if I'm wrong), but the below code will catch the last execution time of a stored procedure that's cached:
SELECT SO.name, SD.last_execution_time FROM sys.dm_exec_procedure_stats SD INNER JOIN sys.objects SO ON SO.object_id = SD.object_id WHERE SO.name = 'usp_InsertOurProcName'
Note: if any of the above is wrong - I'd appreciate being corrected as I'm using this to minimize research time when seeing if an application is correctly calling a procedure and if that's the problem or something else (basically for purposes of solving-by-elimination).
Assuming the above statement is correct, outside of the cache being cleared manually, are there any automatic processes internal to SQL Server that would clear the cache?
A restart of SQL Server will clear these dynamic management views as well