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?

Answers


A restart of SQL Server will clear these dynamic management views as well


Need Your Help


About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.