Общие сведения о подсказках запросов

Завершено

Подсказки запросов — это параметры или стратегии, которые можно применить для принуждения процессора запросов к использованию определенного оператора в плане выполнения для операторов SELECT, INSERT, UPDATE или DELETE. Указания запросов переопределяют любой план выполнения, который обработчик запросов может выбрать для заданного запроса с предложением OPTION .

В большинстве случаев оптимизатор запросов выбирает эффективный план выполнения на основе индексов, статистики и распределения данных. Администраторы баз данных редко должны вмешиваться вручную.

Вы можете изменить план выполнения запроса, добавив подсказки запроса в конец запроса. Например, если добавить OPTION (MAXDOP <integer_value>) в конец запроса, использующего один ЦП, запрос может использовать несколько ЦП (параллелизм) в зависимости от выбранного значения. Кроме того, можно использовать OPTION (RECOMPILE) для создания нового временного плана при каждом выполнении запроса.

--With maxdop hint
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total  
FROM Sales.SalesOrderDetail  
WHERE UnitPrice < $5.00  
GROUP BY ProductID, OrderQty  
ORDER BY ProductID, OrderQty  
OPTION (MAXDOP 2)
GO

--With recompile hint
SELECT City
FROM Person.Address
WHERE StateProvinceID=15 OPTION (RECOMPILE)
GO

Хотя указания запросов могут предоставлять локализованное решение для различных проблем, связанных с производительностью, следует избегать их использования в рабочей среде по следующим причинам.

• Наличие постоянной подсказки запроса может привести к структурным изменениям базы данных, которые полезны для этого запроса, который может стать неприменимым.
• Вы не можете воспользоваться новыми и улучшенными функциями в последующих версиях SQL Server, если привязать запрос к конкретному плану выполнения.

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

• FAST <integer_value>— извлекает первое <integer_value> количество строк при продолжении выполнения запроса. Он лучше работает с небольшими наборами данных и низким значением для быстрого указания запроса. По мере увеличения количества строк стоимость запроса становится выше.

• OPTIMIZE FOR— предоставляет инструкции оптимизатору запросов, что конкретное значение для локальной переменной следует использовать при компиляции и оптимизации запроса.

• USE PLAN— оптимизатор запросов использует план запроса, указанный атрибутом xml_plan .

• RECOMPILE— создает новый временный план для запроса и удаляет его сразу после выполнения запроса.

• { LOOP | MERGE | HASH } JOIN— указывает, что все операции соединения выполняются LOOP JOINили MERGE JOINHASH JOIN в целом запросе. Оптимизатор выбирает наименее затратную стратегию соединения из вариантов, если указано несколько подсказок присоединения.

• MAXDOP <integer_value>— переопределяет значение максимальной степени параллелизма sp_configure. Запрос, указывающий этот параметр, также переопределяет регулятор ресурсов.

Вы также можете применить множество подсказок для запроса в одном запросе. В следующем примере используются подсказки запросов HASH GROUP и FAST <integer_value> в одном и том же запросе.

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total  
FROM Sales.SalesOrderDetail  
WHERE UnitPrice < $5.00  
GROUP BY ProductID, OrderQty  
ORDER BY ProductID, OrderQty  
OPTION (HASH GROUP, FAST 10);  
GO    

Дополнительные сведения о подсказках запросов см. в разделе "Подсказки" (Transact-SQL).

Подсказки хранилища запросов

Подсказки хранилища запросов предоставляют простой метод формирования планов запросов без изменения кода приложения.

Подсказки хранилища запросов полезны, если оптимизатор запросов не создает эффективный план выполнения, а разработчик или DBA не может изменить исходный текст запроса. В некоторых приложениях текст запроса может быть жестко закодирован или автоматически создан.

Чтобы использовать подсказки Query Store, необходимо определить query_id запроса, который вы хотите изменить, через представления каталога Query Store, встроенные отчеты или инструменты анализа производительности запросов в Azure SQL Database. Затем выполните sp_query_store_set_hints с query_id и строкой подсказки запроса, которую вы хотите применить к запросу.

** Следующий пример показывает, как получить query_id для конкретного запроса, а затем использовать его, чтобы применить указания RECOMPILE и MAXDOP к запросу.

SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt 
    INNER JOIN sys.query_store_query q 
        ON qt.query_text_id = q.query_text_id 
WHERE query_sql_text like N'%ORDER BY CustomerName DESC%'  
  AND query_sql_text not like N'%query_store%'
GO

--Assuming the query_id returned by the previous query is 42
EXEC sys.sp_query_store_set_hints @query_id= 42, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1)'
GO

В некоторых сценариях подсказки Query Store могут помочь решить проблемы, связанные с производительностью запросов.

• Перекомпилировать запрос при каждом выполнении.
• Ограничить максимальную степень параллелизма для операции обновления статистики.
• Используйте хеш-соединение вместо соединения по вложенным циклам.
• Используйте уровень совместимости 110 для определенного запроса при сохранении базы данных в текущей совместимости.

Дополнительные сведения о подсказках хранилища запросов см. в разделе "Подсказки хранилища запросов".