SQL SERVER – Finding the Last Used Stored Procedure

In the realm of SQL Server, memory management plays a crucial role in optimizing database performance. Monitoring and tracking the usage of stored procedures is essential for identifying performance bottlenecks and improving overall efficiency. In this blog post, we will delve into a SQL Server query that allows you to retrieve the last used stored procedure directly from the memory cache. By leveraging this information, you can gain insights into the most frequently executed stored procedures and make informed decisions regarding performance tuning and optimization strategies.

The Challenge: While determining the last execution time of a stored procedure from the cache, rather than the disk, provides more accurate and up-to-date information, it can be a complex task. However, SQL Server provides dynamic management views (DMVs) that allow us to extract valuable data from the memory cache, enabling us to track the last used stored procedure effectively.

The Solution: To find the last used stored procedure from the SQL Server memory cache, we can utilize the “sys.dm_exec_query_stats” DMV along with the “sys.dm_exec_cached_plans” DMV. These DMVs provide insights into the execution statistics and cached query plans stored in memory, respectively.

SQL Code Example: Let’s take a look at the SQL code example that retrieves the last used stored procedure from the memory 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;

Explanation:

1. We use the “sys.dm_exec_query_stats” DMV to retrieve execution statistics, including the creation time and last execution time of stored procedures.
2. The “sys.dm_exec_sql_text” DMV is joined using the “CROSS APPLY” operator to fetch the SQL text associated with the query handle.
3. The “DB_NAME()” function is used to retrieve the name of the database where the stored procedure resides.
4. The “OBJECT_NAME()” function retrieves the name of the stored procedure based on its object ID and database ID.
5. The “creation_time” column represents the timestamp of the stored procedure’s creation in the memory cache.
6. The “last_execution_time” column represents the timestamp of the last execution of the stored procedure from the memory cache.
7. The results are ordered in descending order based on the creation time using the “ORDER BY” clause.

Conclusion

Tracking the last used stored procedures directly from the SQL Server memory cache allows for accurate and up-to-date insights into their execution patterns. By utilizing dynamic management views, such as “sys.dm_exec_query_stats” and “sys.dm_exec_sql_text,” you can efficiently extract information regarding the creation time and last execution time of stored procedures from the memory cache.

Understanding the usage patterns of stored procedures from the memory cache empowers database administrators and developers to make informed decisions about performance tuning, optimization strategies, and memory management. By analyzing the most frequently executed stored procedures, you can allocate resources more effectively and enhance overall database performance, resulting in improved application responsiveness and user satisfaction.