How to Find the Last Execution Time of a SQL Stored Procedure

Issue
When troubleshooting application and database issues, gathering some information regarding the stored procedures
could be very helpful in leading to the resolution or to identifying the cause of problems.

Product
Struxureware Power Monitoring
Power Monitoring Expert
ION Enterprise
ION EEM

Environment
Microsoft SQL Server

Cause
Database maintenance and troubleshooting possible application and database issues.

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

Execute the SQL query below, replacing YourSpName with the name of the stored procedure you are interested in, to find the last time a the procedure was executed.

SELECT st.text as SQL,qs.creation_time,qs.last_execution_time,qp.dbid,qp.objectid
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle,DEFAULT,DEFAULT)AS qp
WHERE st.text like '%YourSpName%'

Note: contents of the DMVs will clear while restarting the SQL service, so the contents wont always be accurate.

** see attachment containing the SQL script text

Released for: Schneider Electric Canada