CODEWITHSUNDEEP
sql-serverstored-proceduressql-server-2008-r2
Tim
Tim

Reputation: 109

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?

Upvotes: 2

Views: 934

Answers (1)

SQLMenace
SQLMenace

Reputation: 135121

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

Upvotes: 3

Related Questions