How To Check Stored Procedure Execution History In SQL Server

by

Recently, I got the requirement to check one of the stored procedure execution times in my SQL server database. This article will help you check the stored procedure execution history in an SQL Server.

How To Check Stored Procedure Execution History In SQL Server

To check stored procedure execution history in SQL server, follow the below two simple approaches.

Approach-1: Using sys.dm_exec_query_stats

We can query the sys.dm_exec_query_stats to get the details of the execution history of any stored procedure. Let us execute the SQL query below. Here, the SelectAllProducts is the name of the stored procedure.

SELECT 
    qs.execution_count,
    qs.last_execution_time
FROM 
    sys.dm_exec_query_stats qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE 
    qt.text LIKE '%SelectAllProducts%' 
ORDER BY 
    qs.last_execution_time DESC;

After executing the above query, I got the stored procedure execution details as shown in the screenshot below.

check stored procedure execution history sql server

Check out SQL Server Stored Procedure Naming Convention and Best Practices

Approach-2: Using sys.dm_exec_procedure_stats

We can also query the sys.dm_exec_procedure_stats for this purpose.

SELECT 
    OBJECT_NAME(object_id) AS ProcedureName,
    execution_count,
    last_execution_time
FROM 
    sys.dm_exec_procedure_stats
ORDER BY 
    last_execution_time DESC;

After executing the above query, I got the expected output as shown below.

How To Check Stored Procedure Execution History In SQL Server

Conclusion

Tracking the stored procedure execution details in an SQL server is crucial when working as a developer or as an SQL DBA. You can execute any of the SQL queries mentioned in this article.

You may also like following the articles below.