How to find the last execution details of a stored procedure in SQL Server


In this article I will demonstrate how we can find the last execution details of a stored procedure in SQL Server 2012 or SQL Server 2008 R2.With DMV’s getting modified in these 2 editions of SQL Server, the amount of information we can obtain from the plan cache can be handy for investigation purpose.
When a stored procedure is created or recompiled, a plan for the same is created and cached in the plan cache. Whenever the same stored procedure is executed, the plan is recalled from the SQL memory for execution purpose. The details of an execution is stored internally in SQL Server which can be fetched via the dynamic management views.

A key element in this process is that the plan has to be in the plan cache for us to derive the information. By any chance if the SQL Server gets restarted or the plan cache is cleared then the information would not be available.

Let us see how we can fetch the execution details with an example. I have used the Adventureworks2012 database for demonstration purpose
SQL Server 2012:
Execute the below mentioned SP on SQL Server 2012

USE [AdventureWorks2012]
GO
EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 135

The execution produces 4 rows
ED1Now let us find the execution details of this stored procedure from the plan cache. Open another Query editor and execute the below mentioned query. The query searches the plan cache for the execution details of the stored procedure, whose name we have filtered in the last line of the query

SELECT qs.sql_handle,qs.creation_time,qs.last_execution_time,
qp.dbid,qs.execution_count,qs.last_rows,qs.last_logical_reads,
qs.last_logical_writes,qs.last_physical_reads,st.text
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 '%USPGET%'---filter by name of the SP

The output would be as follows:
ED2Now if we observe the outcome we would find the following information very handy
1. Last execution Time
2. Execution Count
3. Last_rows: This depicts the number of rows as output when the SP executed last.
4. Last_Logical_Reads
5. Last_Logical_Writes
6. Last_Physical_Reads

In SQL Server 2008 R2, the column that would be missing is Last_rows. So the query that you can use in SQL Server 2008 R2 would be as below

SELECT qs.sql_handle,qs.creation_time,
qs.last_execution_time,
qp.dbid,qs.execution_count,qs.last_logical_reads,
qs.last_logical_writes,qs.last_physical_reads,st.text
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 '%Name of Stored Proc%'
Advertisements

Tagged: , , , , , , , , ,

One thought on “How to find the last execution details of a stored procedure in SQL Server

  1. smith October 21, 2016 at 8:49 pm Reply

    can you please add columns showing who ran and the machine name in the query?

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: