Can I tell if a stored procedure has executed against a particular database?

Question

As a result of a release a database is behaving "unusually". I suspect a stored procedure was missed during a recent release, however my DBA is telling me 100% it was executed.

Is there any way to definitively prove it was or wasn't?

Answer 1

(emphasis added)

Is there any way to definitively prove it was/wasn't

No, there is no way to prove in any definite way that it has been executed, outside of coding the stored procedure to do something like insert a record into a log table showing that it had been executed. In your particular case, this would also require a time machine in order to put that into place, and I do not believe that those are even possible to create in the first place :-(.

There are DMVs which can track executions, but they get cleared out for various reasons:

• service restart
• DBCC FREESYSTEMCACHE('ALL')
• memory pressure
• maybe others

Of course, if you do see it in any DMV, that does prove that it was executed, but you better take a screen shot because it might be gone the next time you run that query to show someone that it is there ;-). However, not finding it in a DMV does not prove that it was never executed.

So, along with the query suggested by @TheGameiswar, you could also try the following:

SELECT qplan.[objectid], qplan.[dbid], OBJECT_NAME(qplan.[objectid], qplan.[dbid])
FROM sys.dm_exec_cached_plans cplan
CROSS APPLY sys.dm_exec_query_plan(cplan.[plan_handle]) qplan
WHERE qplan.[objectid] IS NOT NULL;

Answer 2

Starting from SQL Server 2008,use can sys.dm_exec_procedure_stats which gives you the status on when was the stored procedure last executed

select db_name(database_id) as dbname,last_execution_time 
from sys.dm_exec_procedure_stats