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

  • Статья

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

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

Параметр прокрутки (значение параметра @scrollopt для серверных курсоров API)

Поддерживается для серверных курсоров API

Поддерживается для курсоров Transact-SQL, использующих расширенный синтаксис Transact-SQL

Поддерживается для курсоров Transact-SQL, использующих синтаксис ISO

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

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

В этом примере предполагается, что приложение взаимодействует с базой данных База данных AdventureWorks2008R2, используя курсоры 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 ProfilerRPC:Starting. Строка определения параметров требуется для получения успешной структуры плана, так же как и при работе с параметризированными запросами, отправляемыми при помощи процедуры sp_executesql.