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

  • Статья

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

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

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

Внимание

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

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

Обзор

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

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

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

Случаи использования указаний для хранилища запросов

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

The workflow for Query Store Hints.

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

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

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

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

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

Если указание запроса противоречит возможности оптимизации запросов, выполнение запроса не блокируется, а указание не применяется. Если указание приведет к сбою запроса, оно игнорируется, а актуальные сведения о сбоях можно просмотреть в 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:

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

Примечание.

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

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

  • хранилище запросов подсказки переопределяют другие подсказки на уровне жестко закодированных инструкций и руководства по плану.
  • Запросы всегда выполняются. Противоположные хранилище запросов намеки игнорируются, которые в противном случае вызывают ошибку.
  • Если хранилище запросов намеки противоречат, SQL Server не блокирует выполнение запросов, а хранилище запросов указание не применяется.
  • Простая параметризация — указания хранилища запросов не поддерживаются для инструкций, подходящих для простой параметризации.
  • Принудительная параметризация — указание RECOMPILE несовместимо с настройкой принудительной параметризации на уровне базы данных. Если база данных имеет набор принудительной параметризации, а указание RECOMPILE является частью строки подсказок, заданной в хранилище запросов для запроса, SQL Server игнорирует подсказку RECOMPILE и будет применять любые другие подсказки, если они применяются.
  • Созданные вручную подсказки хранилище запросов исключаются из очистки. Указание и запрос не будут удалены из хранилище запросов автоматическим хранением политики отслеживания.
    • Запросы можно удалить вручную пользователями, которые также удалят связанный хранилище запросов подсказки.
    • хранилище запросов подсказки автоматически создаются Обратная связь CE подлежит очистке автоматическим хранением политики отслеживания.
    • Поведение запроса формы обратной связи и предоставления памяти DOP без использования подсказок хранилище запросов. При очистке запросов путем автоматического хранения политики отслеживания данные обратной связи 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_реплика.
  • Поиск планов, принудительных на вторичных реплика с sys.query_store_plan_forcing_locations.

Рекомендации по использованию указаний хранилища запросов

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

Примеры

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

В следующем пошаговом руководстве по указаниям хранилища запросов в Базе данных SQL Azure используется база данных, импортированная с помощью 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;

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