Использование подсказки USE PLAN в запросах с курсорами

Статья
12/15/2008

Подсказку запроса USE PLAN можно использовать в запросах, которые работают с курсорами. В следующей таблице перечислены комбинации параметров прокрутки курсоров, которые можно использовать в подсказке запроса USE PLAN в серверных курсорах API, курсорах Transact-SQL, использующих расширенный синтаксис Transact-SQL, и курсорах Transact-SQL, использующих синтаксис SQL-92.

Параметр прокрутки (значение параметра @scrollopt для серверных курсоров API)	Поддерживается для серверных курсоров API	Поддерживается для курсоров Transact-SQL, использующих расширенный синтаксис Transact-SQL	Поддерживается для курсоров Transact-SQL, использующих синтаксис SQL 92
STATIC	Да	Да	Не допустим
DYNAMIC	Нет	Нет	Не допустим
KEYSET	Нет	Нет	Не допустим
FORWARD_ONLY	Нет	Нет	Не допустим
FAST_FORWARD	Да	Да	Не допустим
FORWARD_ONLY STATIC	Не допустим	Да	Не допустим
INSENSITIVE	Не допустим	Не допустим	Да

С запросами, которые выполняются без курсора, связан один план запроса, а с запросами с курсорами — два. Эти планы могут иметь типы OPEN, FETCH или REFRESH, в зависимости от типа курсора.

Один из этих двух планов формируется непосредственно из входного запроса, а второй — автоматически. Они называются соответственно планом входного запроса и сформированным планом. В следующей таблице продемонстрированы планы, сформированные для курсоров FAST_FORWARD и STATIC (INSENSITIVE).

Тип курсора	План исполнения курсора OPEN	План исполнения курсора FETCH	План исполнения курсора REFRESH
FAST_FORWARD	Не допустим	План входного запроса	Сформированный план
STATIC	План входного запроса	Сформированный план	Не допустим

Планы выполнения в формате XML для запроса с курсором иногда содержатся в одном XML-документе. Такие планы называют «двойными».

Иногда планы для запроса с курсором разделяются на два отдельных плана. Например, в трассировке SQL Server Profiler для плана API или плана запроса с курсором Transact-SQL типа STATIC можно увидеть два различных события инструкции Showplan XML для компиляции запроса. В этом случае для форсирования планов подходит только план входного запроса (OPEN). Его следует использовать в подсказке запроса USE PLAN. Создается также простой сформированный план (FETCH), однако он не требуется и его нельзя использовать для форсирования планов. Всегда можно распознать план входного запроса (OPEN), поскольку он первым вернет набор строк, удовлетворяющих запросу с курсором.

Важно!
План исполнения без курсора нельзя использовать для запроса с курсором и наоборот. Это может привести к ошибке, даже если запрос с курсором и запрос без курсора совпадают.

Следующие типы планов исполнения запросов с курсорами в формате XML можно использовать в подсказке запроса USE PLAN для определенных типов курсоров:

Двойной план для запроса с курсором
План входного запроса с курсором, состоящий из одной части

Для плана исполнения курсора можно принудительно выбрать план, полученный одним из следующих способов:

Следующие события трассировки SQL Server Profiler: инструкция Showplan XML, инструкция Showplan XML для компиляции запроса и профиль статистики инструкции Showplan XML.
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 часто взаимодействуют с SQL Server при помощи серверных курсоров API. Вызовы хранимых процедур серверных курсоров API можно изучить при помощи событий приложения SQL Server Profiler RPC:Starting, которые регистрируются, когда приложение использует один из этих интерфейсов.

Пример: принудительный выбор плана в запросе с курсором

В этом примере предполагается, что приложение взаимодействует с базой данных AdventureWorks, используя курсоры ODBC. Пользователю необходимо принудительно выбрать план запроса, отправленного серверу SQL Server при помощи серверных курсоров API. Чтобы принудительно выбрать план, необходимо получить план, переданный через 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

Получите план для запроса. Для этого щелкните правой кнопкой мыши событие трассировки инструкция Showplan XML, содержащее план входного запроса, которое задано в виде аргумента приведенной выше инструкции sp_cursorprepexec, и выберите Извлечь данные события. Сохраните данные о событии (инструкция XML showplan) в файл CursorPlan.SQLPlan на рабочем столе. Скопируйте файл CursorPlan.SQLPlan в текстовый файл CursorPlan.txt. Откройте файл CursorPlan.txt в редакторе среды SQL Server Management Studio. Чтобы сэкономить время в будущем, замените одинарные кавычки (') четырьмя одинарными кавычками ('''') при помощи функции Найти и заменить. Сохраните файл 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. Выполните запрос, чтобы убедиться, что руководство плана применено

Еще раз выполните запрос в приложении и получите план выполнения в формате XML, используя событие XML Showplan в приложении SQL Server Profiler.

Щелкните событие инструкция XML Showplan. Убедитесь, что план выполнения совпадает с тем, который был форсирован в руководстве.

Параметризированные запросы с курсорами

Если запрос, отправленный при помощи серверных курсоров API, для которого необходимо создать руководство плана, является параметризированным, убедитесь в том, что в определение руководства плана включены строка инструкции и строка определения параметров, которые получены из события SQL Server Profiler RPC:Starting. Строка определения параметров требуется для получения успешного руководства плана, так же как и при работе с параметризированными запросами, отправляемыми при помощи процедуры sp_executesql.

См. также

Поделиться через

Использование подсказки USE PLAN в запросах с курсорами

Наблюдение за использованием серверных курсоров API приложениями

Пример: принудительный выбор плана в запросе с курсором

Шаг 1. Получите план

Шаг 2. Создайте руководство плана для принудительного выбора плана

Шаг 3. Выполните запрос, чтобы убедиться, что руководство плана применено

Параметризированные запросы с курсорами

См. также

Основные понятия

Другие ресурсы

Справка и поддержка

Дополнительные ресурсы