She can dig it!
D Sharon Pruitt

A few posts ago in the plan cache series I discussed the children for the Statements element.  I’ve gotten through two of the elements and in this post we’ll be looking at the StmtCursor and StmtReceive elements.

The SHOWPLAN XML schema defines both that StmtCursor and StmtReceive are types for one or more cursor operations.  The operations can be DECLARE CURSOR, OPEN CURSOR, and FETCH CURSOR.   For the most part, the rest of this post will focus on the StmtCursor element.

Cursor Execution Plan

Each time a cursor is created, the operations for creating and interacting with the cursor are managed through these elements. This activity is not included when the actual execution plan cursor is returned.  The plan that is returned contains the statements that the cursor executes.

Let’s start by looking at the execution plan for a simple cursor:

USE AdventureWorks
GO
DECLARE @SalesOrderID int
DECLARE REALLYCURSED CURSOR FOR
SELECT TOP 3 SalesOrderID FROM Sales.SalesOrderHeader
OPEN REALLYCURSED
FETCH NEXT FROM REALLYCURSED INTO @SalesOrderID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderID = @SalesOrderID
FETCH NEXT FROM REALLYCURSED INTO @SalesOrderID
END
CLOSE REALLYCURSED
DEALLOCATE REALLYCURSED

Executing the above script generates the execution plan below.  As the plan details, the FETCH operation executes four times and the SELECT from Sales.SalesOrderDetail is executed three times.

See the image below for the actual execution plan:

One of the benefits to looking at the execution plan for a CURSOR is that you get the full effect of the execution.  For each statement executed, the cost is calculated and the percentages of cost are a direct result of all of the costs.

Suppose though the number of executions varies quite a bit and you want to see just the plan with the population of the cursor followed by one execution of the logic that the cursor processes.  One way to do this would be to modify the SQL Statement such that it only cycles through once… or you could just look up the compiled plan in the plan cache.

StmtCursor Element

To lookup the compiled plan in the plan cache you will be looking for the StmtCursor element within a execution plan.  As mentioned above, this element is used for the definition of cursors and to describe the OPEN, FETCH, and CLOSE operations that are called against the cursor.

To retrieve the compiled plan for a cursor, we’ll use the exist() method to find plans that contain cursors.  We’ll also use the nodes() method to shred each of the StmtCursor elements to pull out anything useful that can be used from those nodes.  To do this, execute the following query:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT cp.plan_handle
,qp.query_plan
,c.value('@StatementText', 'varchar(255)') AS StatementText
,c.value('@StatementType', 'varchar(255)') AS StatementType
,c.value('CursorPlan[1]/@CursorName', 'varchar(255)') AS CursorName
,c.value('CursorPlan[1]/@CursorActualType', 'varchar(255)') AS CursorActualType
,c.value('CursorPlan[1]/@CursorRequestedType', 'varchar(255)') AS CursorRequestedType
,c.value('CursorPlan[1]/@CursorConcurrency', 'varchar(255)') AS CursorConcurrency
,c.value('CursorPlan[1]/@ForwardOnly', 'varchar(255)') AS ForwardOnly
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('//StmtCursor') t(c)
WHERE qp.query_plan.exist('//StmtCursor') = 1

The output of this query will look like this:

As you can see, you are able to return the DECLARE CURSOR, OPEN CURSOR, FETCH CURSOR, CLOSE CURSOR, and DEALLOCATE CURSOR actions.  Also, the StmtCursor element with the StatementType DECLARE CURSOR element provides information on how the cursor has been constructed with the attributes:

• CursorActualType: Values can be Snapshot, Keyset, or FastForward.  Property determines how data within a cursor is collected and retrieved by the cursor.
• CursorRequestedType: Values can be Static, Keyset, Dynamic, or FastForward.  Property determines how data within a cursor is collected and retrieved by the cursor.
• CursorConcurrency: Determines whether rows updated or deleted with a cursor can succeed if they are modified outside of the cursor before the cursor modification.  Values can be Optimistic, Pessimistic, or Read Only.
• ForwardOnly: Boolean value stating whether the cursor can only move forward or if it can also move in any direction (i.e. first, last, or prior).

Now if you select the link for the query plan, the query plan for the cursor sample above will open.  This execution play has some significant differences over the plan that was returned when the example was executed.

For starters, the populate cursor step takes up 90% of the query cost.  This can be useful for looking for cursors that have a high cost when building the cursor.  Then there is only the two fetch operations and SELECT  statements.  Finally, at this point the execution plan hasn’t been executed so there is not information on the number of executions that will occur with the SELECT statement.

StmtReceive Element

As I mentioned at the beginning, this post would primarily focus on the StmtCursor.  The reason for this is that between myself and others that checked their plan caches were unable to find occurrences of this element.  To attempt to discover the element, people ran the following query:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT cp.plan_handle
,qp.query_plan
,c.value('@StatementText', 'varchar(255)') AS StatementText
,c.value('@StatementType', 'varchar(255)') AS StatementType
,qp.query_plan
,c.query('.')
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('//StmtReceive') t(c)
WHERE qp.query_plan.exist('//StmtReceive') =1

If you look in your plan cache and find StmtReceive elements, I would be interested in hearing about those plans.  Please comment or contact me if you run into this element.

StmtCursor Usefulness

What’s the value in using the StmtCursor element?  For starters, have you ever dug through your stored procedures or dug into the code from all of the applications in your environment to give the cursors a once over?

If you are an adventurous, or a bored, soul – getting them from the stored procedure definitions isn’t too difficult.  Getting them from the application would be more challenging and potentially a colossal waste of time.  Instead, by looking at the plan cache you can pull out all of the compiled plans that are active in your environment.  Nevermind the 42 stored procedures that you found in sys.sql_modules with cursors – instead focus on the few stored procedures that are actually being used.

Some other things to think about when you are combing through your plan cache – do you really need to have an updateable cursor?  Can the cursor be read only?  Then again – is the cursor really needed for the operation it is trying to complete?

Take a few minutes to look through these and you may find some big wins and maybe save some future aches and pains.  Maybe your boss will be impressed enough to send you to an upcoming SQL Saturday event.

Related posts:

1. Can You Dig It? – SHOWPLAN XML Structure
2. Can You Dig It? – StmtUseDb Element
3. Can You Dig It? – StmtCond Element