Розуміння підказок для запитів

  • 4 хв.

Підказки запиту – це параметри або стратегії, які можна застосувати для застосування обробника запитів для використання певного оператора в плані виконання для 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 JOINабо HASH 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_id сховища запитів інструкції запиту, яку потрібно змінити в поданнях каталогу сховища запитів, стандартних звітах сховища запитів або аналіз продуктивності запиту для бази даних Azure SQL. Потім виконайте query_id sp_query_store_set_hintsі рядок підказки запиту, який потрібно застосувати до запиту.

У наведеному нижче прикладі показано, як отримати query_id запит для певного запиту, а потім використати його, щоб застосувати RECOMPILEMAXDOP підказки та підказки до запиту.

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

Є кілька сценаріїв, коли підказки сховища запитів можуть допомогти з проблемами продуктивності на рівні запиту.

  • Повторно компілювати запит під час кожного виконання.
  • Обмежити максимальний ступінь паралелізму для операції оновлення статистики.
  • Використовуйте геш-об'єднання замість об'єднання вкладених циклів.
  • Використовуйте рівень сумісності 110 для певного запиту, зберігаючи базу даних на поточній сумісності.

Докладні відомості про підказки до сховища запитів див. в статті Підказки до сховища запитів.