Using the USE PLAN Query Hint on Queries with Cursors
You can use the USE PLAN query hint with queries that specify cursor requests. The following table shows the cursor scroll options combinations that are supported with USE PLAN for API server cursors, Transact-SQL cursors that use Transact-SQL Extended Syntax, and Transact-SQL cursors that use SQL-92 Syntax.
| Scroll option (@scrollopt value for API server cursors) | USE PLAN supported for API server cursors | USE PLAN supported for Transact-SQL cursors that use Transact-SQL Extended Syntax | USE PLAN supported for Transact-SQL cursors that use SQL 92 Syntax |
|---|---|---|---|
STATIC |
Y |
Y |
Not applicable |
DYNAMIC |
N |
N |
Not applicable |
KEYSET |
N |
N |
Not applicable |
FORWARD_ONLY |
N |
N |
Not applicable |
FAST_FORWARD |
Y |
Y |
Not applicable |
FORWARD_ONLY STATIC |
Not applicable |
Y |
Not applicable |
INSENSITIVE |
Not applicable |
Not applicable |
Y |
Queries with cursors have two query plans associated with them, instead of the single plan associated with queries submitted without cursors. These plans may be of type OPEN, FETCH or REFRESH, depending on the type of the cursor.
One of the two plans for a cursor is directly generated from the input query, and the other plan is automatically generated. These plans are called the input query plan and the generated plan, respectively. The following table shows the plans that are generated for FAST_FORWARD and STATIC (INSENSITIVE) cursors.
| Cursor type | Open cursor plan | Fetch cursor plan | Refresh cursor plan |
|---|---|---|---|
FAST_FORWARD |
Not applicable |
Input query |
Generated |
STATIC |
Input query |
Generated |
Not applicable |
The XML query plans for a cursor query sometimes appear as a single XML document that contains both plans. These plans are called two-part plans.
The plans for a cursor also sometimes appear as two separate plans. For example, in a SQL Server Profiler trace for a STATIC API or Transact-SQL cursor query plan, you can see that two different Showplan XML For Query Compile events are generated. Only the input query (OPEN) plan is significant for plan forcing in this case. You should use the input query plan in a USE PLAN hint. A simple generated (FETCH) plan is also created but it is not required, or allowed, for plan forcing. You can recognize the input query (OPEN) plan because it is the plan that first gathers the set of rows that match the cursor query.
Important
Do not try to force a noncursor plan for a cursor query or vice versa. Plan forcing might fail if you do this, even if the cursor query and noncursor query are the same.
The following types of XML query plan outputs that describe cursor plans can be used to force a plan with USE PLAN for specific cursor types:
- A two-part plan for the cursor
- A one-part input query plan for the cursor
The cursor plan you force can be a plan obtained through any one of the following mechanisms for obtaining an XML query plan:
XML-based SQL Server Profiler trace events. These events can include Showplan XML, Showplan XML For Query Compile, and Showplan XML Statistics Profile.
SET SHOWPLAN_XML ON
SET STATISTICS XML ON
Dynamic management views and functions, such as the following query:
SELECT * FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_query_plan(plan_handle)
Observing API Server Cursor Usage by Applications
DB Library, ODBC, ADO, and OLEDB applications frequently interact with SQL Server by using API server cursors. You can see the calls that are submitted to API server cursor stored procedures by examining the SQL Server Profiler RPC:Starting events when an application that is built by using one of these interfaces is running.
Example: Forcing a Plan on a Query with a Cursor
This example assumes that you are using an application that interacts with the AdventureWorks database by using ODBC cursors, and you want to force the plan for a query submitted to SQL Server by using an API server cursor routine. To force the plan, collect a plan for a query submitted through a cursor API routine and then create a plan guide to force the plan for that query. Have the application run the query again and examine the plan to verify that it has been forced.
Step 1: Collect the Plan
Start a SQL Server Profiler trace and select the Showplan XML and RPC:Starting events. Have the application execute the query for which you want to force the plan. Click the RPC:Starting event that is generated. Suppose the RPC:Starting event has the following text data:
DECLARE @p1 int
SET @p1=-1
DECLARE @p2 int
SET @p2=0
DECLARE @p5 int
SET @p5=8
DECLARE @p6 int
SET @p6=8193
DECLARE @p7 int
SET @p7=0
EXEC sp_cursorprepexec @p1 OUTPUT,@p2 OUTPUT,NULL,N'SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate BETWEEN ''20030101'' AND ''20040101''',@p5 OUTPUT,@p6 OUTPUT,@p7 OUTPUT
SELECT @p1, @p2, @p5, @p6, @p7
Collect the plan for the query by right clicking the Showplan XML trace event containing the input query plan for the query that appears as an argument to the sp_cursorprepexec statement above, and then selecting Extract Event Data. Save the event data (an XML showplan) to a file CursorPlan.SQLPlan on the desktop. Copy the file CursorPlan.SQLPlan to CursorPlan.txt. In SQL Server Management Studio, open CursorPlan.txt in an editor window. To save time later, use Find and Replace to replace each single quotation mark (') in the plan with four single quotation marks (''''). Save CursorPlan.txt.
Step 2: Create the Plan Guide to Force the Plan
Create a plan guide by writing and executing the following sp_create_plan_guide statement to force the plan. This plan guide definition includes the XML plan captured in the previous step in a USE PLAN query hint in the plan guide.
When you are writing this plan guide definition, paste the contents of CursorPlan.txt into the appropriate position in the @hints argument (just after OPTION(USE PLAN N'').
exec sp_create_plan_guide
@name = N'CursorGuide1',
@stmt = N'SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate BETWEEN ''20030101'' AND ''20040101''',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION(USE PLAN N''<ShowPlanXML xmlns=''''https://schemas.microsoft.com/sqlserver/2004/07/showplan'''' Version=''''0.5'''' Build=''''9.00.1116''''><BatchSequence><Batch><Statements><StmtSimple>
…
</StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>'')'
Step 3: Execute the Query and Verify That the Plan Guide Is Applied to It
Have the application run the query again and gather its XML execution plan by using the XML Showplan event in SQL Server Profiler.
Click the XML Showplan event for the plan. You should see that the plan is the one that you forced in the plan guide.
Parameterized Cursor Queries
If the API server cursor query that you want to create a plan guide for is parameterized, make sure you include both the statement string and the parameter definition string you see in the SQL Server Profiler RPC:Starting event in the plan guide definition. The parameter definition string is also required to obtain a successful plan guide match, just as it is with parameterized queries submitted by using sp_executesql.
See Also
Concepts
Specifying Query Plans with Plan Forcing
Optimizing Queries in Deployed Applications by Using Plan Guides
Other Resources
Query Performance
sp_create_plan_guide (Transact-SQL)