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.

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

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

Menu
Exit mobile version