SQL SERVER – Last Used Stored Procedure

“Is there any way to know the last used stored procedure in SQL Server?” Well, I was asked this during Comprehensive Database Performance Health Check. Let us explore the answer today.

Here is the script which will give you the Last Used Stored Procedure from SQL Server Memory Cache. You must remember that this information is from the cache and that means it is only as good as the last restart of your SQL Server services or as accurate as your data is in the cache.

SELECT DB_NAME(qt.[dbid]) AS [DatabaseName],
OBJECT_NAME(qt.[objectid], qt.[dbid]) AS [SP Name],
qs.execution_count AS [Execution Count],
qs.max_logical_reads,
qs.max_logical_writes,
qs.creation_time,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS [Age in Cache]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE LEN(OBJECT_NAME(qt.[objectid], qt.[dbid]))> 0
ORDER BY DB_NAME(qt.[dbid]) DESC,
OBJECT_NAME(qt.[objectid], qt.[dbid]) DESC;

The script above will give you details about all the stored procedures in your SQL Server. If you want, you can add additional where conditions to filter based on the database.

Here are the six-part blog post series I have written based on my last 10 years of experience helping with the Comprehensive Database Performance Health Check. I strongly recommend you to read them as they walk you through my business model.

• Consulting 101 – Why Do I Never Take Control of Computers Remotely?
• Consulting 102 – Why Do I Give 100% Guarantee of My Services?
• Consulting 103 – Why Do I Assure SQL Server Performance Optimization in 4 Hours?
• Consulting 104 – Why Do I Give All of the Performance-Tuning Scripts to My Customers?
• Consulting 105 – Why Don’t I Want My Customers to Return Because of the Same Problem?
• Consulting Wrap Up – What Next and How to Get Started

Please do not hesitate to reach out to me on LinkedIn.

Reference: Pinal Dave (http://blog.SQLAuthority.com)