Указания хранилища запросов

Статья
03/05/2025

Область применения: 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.

Внимание

Так как оптимизатор запросов SQL Server обычно выбирает оптимальный план выполнения для запроса, мы рекомендуем использовать только указания в качестве последнего средства для опытных разработчиков и администраторов баз данных. Дополнительные сведения см. в разделе Указания запросов.

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

Обзор

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

Если оптимальный план не выбран, разработчик или 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.

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

Определите query_id инструкции запроса, которую вам нужно изменить, в хранилище запросов. Это можно сделать различными способами:
- Запрос представлений каталога хранилища запросов.
- Использование встроенных отчетов хранилища запросов в SQL Server Management Studio.
- Использование функции Query Performance Insight в портале Azure для Базы данных Azure SQL.
Выполните 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 не существует, возникает ошибка.

Примечание.

Полный список поддерживаемых указаний см. в sys.sp_query_store_set_hints.

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

Атрибуты XML плана выполнения

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

Attribute	Description
`QueryStoreStatementHintText`	Фактические указания хранилища запросов, примененные к запросу
`QueryStoreStatementHintId`	Уникальный идентификатор указания запроса
`QueryStoreStatementHintSource`	Источник подсказки хранилища запросов (например, "User")

Примечание.

Эти XML-элементы доступны через выходные данные команд Transact-SQL SET STATISTICS XML и SET SHOWPLAN XML.

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

Подсказки хранилища запросов имеют приоритет над другими подсказками на уровне жестко заданных инструкций и планов.
Запросы всегда выполняются. Противоречивые подсказки 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.

Примеры

А. Демонстрация подсказок хранилища запросов

В следующем руководстве по использованию подсказок в хранилище запросов в Базе данных 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

B. Идентифицировать запрос в хранилище запросов

В следующем примере выполняется запрос к 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_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;

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

Указания хранилища запросов

Обзор

Когда использовать подсказки для хранилища запросов

Системные хранимые процедуры для подсказок хранилища запросов

Атрибуты XML плана выполнения

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

подсказки хранилища запросов и группы доступности

Рекомендации по использованию подсказок Query Store

Примеры

А. Демонстрация подсказок хранилища запросов

B. Идентифицировать запрос в хранилище запросов

Обратная связь

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

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

Указания хранилища запросов

Обзор

Когда использовать подсказки для хранилища запросов

Системные хранимые процедуры для подсказок хранилища запросов

Атрибуты XML плана выполнения

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

подсказки хранилища запросов и группы доступности

Рекомендации по использованию подсказок Query Store

Примеры

А. Демонстрация подсказок хранилища запросов

B. Идентифицировать запрос в хранилище запросов

Связанный контент

Обратная связь

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