Tag Archives: SQL server 2008 R2

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%'

Modified DMV’s in SQL Server 2008 R2 and SQL Server 2012

In this post i will discuss about two dynamic management views that has been enhanced with new columns in SQL Server 2008 R2 and SQL Server 2012. The DMV’s are

1) sys.dm_exec_query_stats
2) sys.dm_exec_sessions

Let us begin with [sys.dm_exec_query_stats]
So what does this view reflect. It reflects performance data for stored query plans in SQL Server plan cache. The data is aggregated and is helpful for performance analysis. When a query plan is eliminated from the plan cache, the related row is also removed. In SQL Server 2008 R2 onwards, 3 new columns have been added to this DMV. They are as follows as explained on Technet.

a) last_rows: The number of rows returned by the last execution of the query.
b) min_rows: Minimum number of rows returned by the query since last recompilation of the query and execution of the plan.
c) max_rows: Maximum number of rows returned by the query over the number of times that the plan has been executed since it was last compiled.

The next dmv that was modified with new columns are [sys.dm_exec_sessions]
This view returns a row per authenticated session on SQL Server. It shows information about all the active user connections and internal tasks that are being performed. The new columns that have been added to this view are as follows:

a) Database_id: iD of the current database for each session.
b) authenticating_database_id:
i) When the source of the session is a contained database, then the value is the db id of the authenticating database.
ii) The value will be 1 if it is a server scoped principal i.e a windows or SQL login.
iii) This will be Null if the session is that of an internal task.
c) open_transaction_count: Number of open transactions per session.

%d bloggers like this: