项目
04/01/2012

对包含游标的查询使用 USE PLAN 查询提示

您可以对指定游标请求的查询使用 USE PLAN 查询提示。下表列出了对于 API 服务器游标、使用 Transact-SQL 扩展语法的 Transact-SQL 游标和使用 ISO 语法的 Transact-SQL 游标，支持使用 USE PLAN 的一些游标滚动选项组合。

滚动选项（API 服务器游标的 @scrollopt 值）	对于 API 服务器游标支持 USE PLAN	对于使用 Transact-SQL 扩展语法的 Transact-SQL 游标支持 USE PLAN	对于使用 ISO 语法的 Transact-SQL 游标支持 USE PLAN
STATIC	Y	Y	不适用
DYNAMIC	N	N	不适用
KEYSET	N	N	不适用
FORWARD_ONLY	N	N	不适用
FAST_FORWARD	Y	Y	不适用
FORWARD_ONLY STATIC	不适用	Y	不适用
INSENSITIVE	不适用	不适用	Y

包含游标的查询具有两个与其关联的查询计划，而不是与不包含游标提交的查询关联的单个计划。这些计划的类型可能为 OPEN、FETCH 或 REFRESH，具体取决于游标的类型。

游标的两个计划一个直接从输入查询生成，另一个自动生成。这两个计划分别称为输入查询计划和生成的计划。下表列出了为 FAST_FORWARD 游标和 STATIC (INSENSITIVE) 游标生成的计划。

游标类型	打开游标计划	提取游标计划	刷新游标计划
FAST_FORWARD	不适用	输入查询	已生成
STATIC	输入查询	已生成	不适用

游标查询的 XML 查询计划有时呈现为包含两个计划的单个 XML 文档。这些计划称为两部分组成的计划。

游标的这些计划有时也呈现为两个单独的计划。例如，在一个 STATIC API 或 Transact-SQL 游标查询计划的 SQL Server Profiler跟踪中，您可以看到生成了两个不同的 Showplan XML For Query Compile 事件。在这种情况下，只有输入查询 (OPEN) 计划对强制计划有意义。您应在 USE PLAN 提示中使用输入查询计划。对于强制计划，也会创建简单的生成 (FETCH) 计划（但它不是必需的）或允许该计划。您可以识别输入查询 (OPEN) 计划，因为它是首先收集与游标查询匹配的行集的计划。

重要提示
请勿尝试对游标查询强制实施非游标计划，反之亦然。否则，即使游标查询与非游标查询相同，强制计划也可能失败。

可以使用描述游标计划的以下类型的 XML 查询计划输出，对特定游标类型使用 USE PLAN 来强制实施计划：

游标的两部分组成的计划
游标的一部分组成的输入查询计划

强制实施的游标计划可以是通过以下任一用于获取 XML 查询计划的机制获取的计划：

基于 XML SQL Server Profiler跟踪事件。这些事件可能包括 Showplan XML、Showplan XML For Query Compile 和 Showplan XML Statistics Profile。
SET SHOWPLAN_XML ON
SET STATISTICS XML ON

动态管理视图和函数，如以下查询：

SELECT *
FROM sys.dm_exec_query_stats 
CROSS APPLY sys.dm_exec_query_plan(plan_handle)

通过应用程序观察 API 服务器游标的使用情况

DB Library、ODBC、ADO 和 OLEDB 应用程序通过使用 API 服务器游标频繁与 SQL Server 进行交互操作。在通过使用这些接口之一编译的应用程序运行时，您可以通过检查 SQL Server ProfilerRPC:Starting 事件来查看提交到 API 服务器游标存储过程的调用。

示例：对包含游标的查询强制计划

此示例假定，您要使用通过 ODBC 游标与 AdventureWorks2008R2 数据库进行交互操作的应用程序，并希望对通过 API 服务器游标例程提交到 SQL Server 的查询强制实施计划。为了强制实施计划，先收集通过游标 API 例程提交的查询的计划，然后创建计划指南以对该查询强制计划。让应用程序再次运行查询，并检查计划以验证其是否已强制实施。

步骤 1：收集计划

启动 SQL Server Profiler跟踪，选择 Showplan XML 事件和 RPC:Starting 事件。让应用程序执行要强制实施计划的查询。单击生成的 RPC:Starting 事件。假定 RPC:Starting 事件包含以下文本数据：

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

通过右键单击包含查询（呈现为 sp_cursorprepexec 语句的参数）的输入查询计划的 Showplan XML 跟踪事件收集查询的计划，然后选择**“提取事件数据”。将事件数据（XML 显示计划）保存到桌面上的文件 CursorPlan.SQLPlan。将文件 CursorPlan.SQLPlan 复制到 CursorPlan.txt。在 SQL Server Management Studio 中，在编辑器窗口打开 CursorPlan.txt。为了以后节省时间，请使用“查找和替换”将计划中的每个单引号 (') 都替换为四个单引号 (''''**)。保存 CursorPlan.txt。

步骤 2：创建计划指南以强制实施计划

通过编写和执行以下 sp_create_plan_guide 语句来创建计划指南以强制实施计划。此计划指南定义在计划指南的 USE PLAN 查询提示中包括上一步中捕获的 XML 计划。

当您编写此计划指南定义时，请将 CursorPlan.txt 的内容粘贴到 @hints 参数中的相应位置（就在 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>'')'

步骤 3：执行查询并验证计划指南是否适用于该查询

让应用程序再次运行查询，并通过使用 SQL Server Profiler中的 XML Showplan 事件收集其 XML 执行计划。

单击计划的 XML Showplan 事件。您应看到该计划就是计划指南中强制实施的计划。

参数化游标查询

如果您要为其创建计划指南的 API 服务器游标查询已参数化，请确保将在 SQL Server ProfilerRPC:Starting 事件中看到的语句字符串和参数定义字符串都包括在计划指南定义中。参数定义字符串也是获得成功的计划指南匹配所必需的，正如它对于通过使用 sp_executesql 提交的参数化查询一样。

请参阅

通过