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

Область применения: SQL Server 2022 (16.x) База данных SQL в AzureУправляемый экземпляр SQL AzureБаза данных SQL в Microsoft Fabric

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

Указания хранилища запросов доступны в Базе данных SQL Azure и Управляемом экземпляре SQL Azure. Подсказки хранилища запросов — это также функция, введенная в SQL Server 2022 (16.x).

• Дополнительные сведения о настройке и администрировании с помощью хранилища запросов см. в разделе Мониторинг производительности с помощью хранилища запросов.
• Сведения об обнаружении полезных сведений и настройке производительности с помощью хранилища запросов см. в статье Настройка производительности с помощью хранилища запросов.
• Сведения о работе с хранилищем запросов в базе данных SQL Azure см. в разделе Работа с хранилищем запросов в базе данных SQL Azure.

Посмотрите это видео с обзором подсказок хранилища запросов.

В идеале оптимизатор запросов выбирает оптимальный план выполнения для запроса.

Если оптимальный план не выбран, разработчик или DBA могут потребоваться вручную оптимизировать для определенных условий. Указания запросов указываются с помощью OPTION предложения и могут использоваться для влияния на поведение выполнения запроса. Хотя указания запросов помогают решать различные проблемы производительности, они требуют переделки исходного текста запроса. Администраторы и разработчики баз данных могут не всегда вносить изменения непосредственно в код Transact-SQL для внедрения указания запроса. Transact-SQL может быть жестко закодирован в приложение или автоматически создан приложением. Ранее разработчику могло потребоваться использовать гиды по плану, которые могут быть сложными для использования.

Сведения о том, какие указания запросов можно применять, см. в разделе Поддерживаемые указания запросов.

Как следует из названия, эта функция расширяет возможности хранилища запросов и зависит от него. Хранилище запросов позволяет сохранять сведения о запросах и планах выполнения, а также связанную статистику времени выполнения. хранилище запросов значительно упрощает общее взаимодействие с клиентом по настройке производительности. SQL Server 2016 (13.x) впервые представил хранилище запросов, и теперь оно включено по умолчанию в SQL Server 2022 (16.x), Azure SQL Managed Instance и Azure SQL Database.

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

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

Чтобы использовать подсказки из хранилища запросов:

1. Определите query_id инструкции запроса, которую вам нужно изменить, в хранилище запросов. Это можно сделать различными способами:
   • Запрос представлений каталога хранилища запросов.
   • Использование встроенных отчетов хранилища запросов в SQL Server Management Studio.
   • Использование функции Query Performance Insight в портале Azure для Базы данных Azure SQL.
2. Выполните sys.sp_query_store_set_hints с параметром query_id и строкой указания запроса, которую вы хотите использовать в запросе. Эта строка может содержать одно или несколько указаний запроса. Подробности см. в sys.sp_query_store_set_hints.

Созданные указания Query Store сохраняются после перезапусков и аварийных переключений. Указания хранилища запросов переопределяют жестко запрограммированные указания на уровне инструкции и существующие указания структуры плана.

Если указание запроса противоречит возможности оптимизации запросов, выполнение запроса не блокируется, а указание не применяется. Если указание приведет к сбою запроса, оно игнорируется, а актуальные сведения о сбоях можно просмотреть в sys.query_store_query_hints.

Чтобы создать или изменить указания, используйте sys.sp_query_store_set_hints. Указания указываются в допустимом строковом формате N'OPTION (...)'.

• При создании подсказки хранилища запросов, если для конкретного query_id подсказки хранилища запросов нет, создается новая подсказка хранилища запросов.
• При создании или обновлении указания для хранилища запросов, если такое указание уже существует для определенного query_id, последнее указанное значение переопределяет прежние значения для связанного запроса.
• Если query_id не существует, возникает ошибка.

Чтобы удалить указания, связанные с query_id, используйте sys.sp_query_store_clear_hints.

При применении подсказок следующий результирующий набор отображается в StmtSimple элементе плана выполнения в формате XML:

• Подсказки хранилища запросов имеют приоритет над другими подсказками на уровне жестко заданных инструкций и планов.
• За исключением подсказки ABORT_QUERY_EXECUTION, запросы с подсказками хранилища запросов всегда выполняются. Противоречивые подсказки Query Store игнорируются, если они могли бы вызвать ошибку.
• Если указания хранилища запросов противоречат, SQL Server не блокирует выполнение запросов, и указания хранилища запросов не применяются.
• Простая параметризация — подсказки хранилища запросов не поддерживаются для инструкций, подходящих для простой параметризации.
• Принудительная параметризация - Подсказка RECOMPILE несовместима с принудительной параметризацией, установленной на уровне базы данных. Если для базы данных включена принудительная параметризация, и RECOMPILE подсказка является частью строки подсказок в хранилище запросов для конкретного запроса, SQL Server игнорирует RECOMPILE подсказку и будет применять любые другие, если они заданы.
  • Кроме того, SQL Server выдает предупреждение (код ошибки 12461), указывающее, что RECOMPILE указание было проигнорировано.
  • Дополнительные сведения о том, что нужно учитывать при применении принудительной параметризации см. в рекомендациях по использованию принудительной параметризации.
• Созданные вручную подсказки Query Store исключены из очистки. Указание и запрос не будут удалены из Query Store посредством автоматического хранения политики сбора данных.
  • Запросы можно удалить вручную пользователями, что также удалит связанную подсказку Хранилища запросов.
  • Подсказки Хранилища Запросов, автоматически генерируемые Обратной связью CE, подлежат очистке в соответствии с автоматическим хранением политики захвата.
  • Обратная связь по DOP и обратная связь по выделению памяти формируют поведение запроса без использования подсказок Query Store. При очистке запросов путем автоматического хранения политики отслеживания данные обратной связи DOP и предоставления памяти также удаляются.
  • Вы можете вручную создать тот же намек Хранилища запросов, который был реализован с помощью обратной связи CE, а затем запрос с этим намеком не будет подлежать очистке автоматическим хранением политики захвата.

Подсказки хранилища запросов не оказывают влияния на вторичные реплики, если хранилище запросов для них не активировано. Для получения дополнительной информации см. хранилище запросов для вторичных реплик.

• До выхода SQL Server 2022 (16.x) подсказки хранилища запросов можно применить к первичной реплике группы доступности.
• Начиная с SQL Server 2022 (16.x), когда включено хранилище запросов для вторичных реплик, подсказки хранилища запросов также учитывают реплики для вторичных реплик в группах доступности.
• Вы можете добавить подсказку Хранилища запросов в определенную реплику или набор реплик, если у вас хранилище запросов для дополнительных реплик включено. В sys.sp_query_store_set_query_hints этот параметр устанавливается параметром @query_hint_scope , который был представлен в SQL Server 2022 (16.x).
• Найдите доступные наборы реплик, запрашивая sys.query_store_replicas.
• Найдите планы на вторичных репликах, которые были принудительно установлены, с помощью sys.query_store_plan_forcing_locations.

• Прежде чем оценивать запросы для потенциальных новых подсказок Query Store, выполните поддержку индексов и статистики.
• Протестируйте базу данных приложения на последнем уровне совместимости перед использованием подсказок хранилища запросов.
  • Например, в SQL Server 2022 (16.x) (уровень совместимости 160) была введена оптимизация плана, чувствительного к параметрам (PSP), которая использует несколько активных планов для каждого запроса для работы с неравномерными распределениями данных. Если ваша среда не может использовать последний уровень совместимости, подсказки хранилища запросов с указанием RECOMPILE можно использовать на любом поддерживаемом уровне совместимости.
• Указания хранилища запросов переопределяют поведение плана запросов SQL Server. Рекомендуется использовать подсказки для хранилища запросов только тогда, когда это необходимо для решения проблем, связанных с производительностью.
• Указания хранилища запросов, указания на уровне инструкций, структуры планов и принудительные планы хранилища запросов рекомендуется пересматривать при любом изменении в распределении данных и в ходе проектов миграции базы данных. Изменения в распределении данных могут привести к тому, что Хранилище запросов будет генерировать неоптимальные планы выполнения.

В следующем руководстве по использованию подсказок в хранилище запросов в Базе данных Azure SQL используется импортированная с помощью файла BACPAC база данных. Сведения о том, как импортировать новую базу данных на сервер Базы данных SQL Azure, см. в статье Краткое руководство по импорту BACPAC-файла в базу данных.

-- ************************************************************************ --
-- Query Store hints demo

-- Demo uses "PropertyMLS" database which can be imported from BACPAC here:
-- https://github.com/microsoft/sql-server-samples/tree/master/samples/features/query-store

-- Email QSHintsFeedback@microsoft.com for questions\feedback
-- ************************************************************************ --

/*
    Demo prep, connect to the PropertyMLS database
*/

ALTER DATABASE [PropertyMLS] SET QUERY_STORE CLEAR;
ALTER DATABASE CURRENT SET QUERY_STORE = ON;
ALTER DATABASE CURRENT SET QUERY_STORE  (QUERY_CAPTURE_MODE = ALL);
GO

-- Should be READ_WRITE
SELECT actual_state_desc
FROM sys.database_query_store_options;
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints.
    Checking if any already exist (should be none).
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

/*
     The PropertySearchByAgent stored procedure has a parameter
     used to filter AgentId.  Looking at the statistics for AgentId,
     you will see that there is a big skew for AgentId 101.
*/
SELECT	hist.range_high_key AS [AgentId],
        hist.equal_rows
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist
WHERE s.[name] = N'NCI_Property_AgentId';


-- Show actual query execution plan to see plan compiled.
-- Agent with many properties will have a scan with parallelism.
EXEC [dbo].[PropertySearchByAgent] 101;

-- Agents with few properties still re-use this plan (assuming no recent plan eviction).
EXEC [dbo].[PropertySearchByAgent] 4;


/*
    Now let's find the query_id associated with this query.
*/
SELECT query_sql_text, q.query_id
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 ListingPrice DESC%' and query_sql_text not like N'%query_store%';
GO

/*
     We can set the hint associated with the query_id returned in the previous result set, as below.
     Note, we can designate one or more query hints
*/
EXEC sp_query_store_set_hints @query_id=5, @value = N'OPTION(RECOMPILE)';
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see two different plans, one for AgentId 101 and one for AgentId 4.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

/*
    We can remove the hint using sp_query_store_clear_query_hints
*/
EXEC sp_query_store_clear_hints @query_id = 10;
GO

/*
    That Query Store Hint is now removed
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see one plan again.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

В следующем примере выполняется запрос к sys.query_store_query_text и sys.query_store_query, чтобы получить query_id для текстового фрагмента выполненного запроса:

В этой демонстрации запрос, который мы пытаемся настроить, находится в образце базы данных SalesLT:

SELECT * FROM SalesLT.Address as A 
INNER JOIN SalesLT.CustomerAddress as CA
on A.AddressID = CA.AddressID
WHERE PostalCode = '98052' ORDER BY A.ModifiedDate DESC;

Хранилище запросов не сразу отображает данные запросов в системных представлениях.

Определите запрос в представлениях системного каталога «Хранилище запросов»:

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'%PostalCode =%'  
  AND query_sql_text not like N'%query_store%';
GO

В следующих примерах запрос из предыдущего примера в базе данных SalesLT обозначен как query_id 39.

Определив указание, примените его для принудительного выделения максимального размера памяти в процентах от заданного предела памяти для query_id:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(MAX_GRANT_PERCENT=10)';

Кроме того, указания запросов можно применять с помощью следующего синтаксиса (например, с параметром для принудительного использования устаревшего оценщика кратности):

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

Можно применять несколько указаний запроса в виде списка с разделителями-запятыми:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1, USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110''))';

Просмотрите подсказку Query Store, примененную для query_id 39:

SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason, last_query_hint_failure_reason_desc, query_hint_failure_count, source, source_desc 
FROM sys.query_store_query_hints 
WHERE query_id = 39;

Наконец, удалите указание из query_id 39 с помощью инструкции sp_query_store_clear_hints.

EXEC sys.sp_query_store_clear_hints @query_id = 39;

• sys.query_store_query_hints (Transact-SQL)
• sys.sp_query_store_set_hints (Transact-SQL)
• sys.sp_query_store_clear_hints (Transact-SQL)
• Сохранение плана выполнения в формате XML
• Отображение и сохранение планов выполнения
• Указания (Transact-SQL) — запросы
• Лучшие практики по мониторингу рабочих нагрузок с помощью Хранилища запросов
• Лучшие практики использования подсказок для Хранилища запросов
• Мониторинг производительности с использованием хранилища запросов
• Настройка максимальной степени параллелизма (MAXDOP) в Базе данных SQL Azure