Мониторинг производительности с использованием хранилища запросов

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

Функция хранилища запросов предоставляет аналитику по выбранному плану запросов и производительности для SQL Server, Базы данных SQL Azure, Управляемого экземпляра SQL Azure и Azure Synapse Analytics. Хранилище запросов упрощает устранение неполадок с производительностью, помогая быстро находить разницу в производительности, вызванную изменением плана запросов. Хранилище запросов автоматически собирает журнал запросов, планов и статистики выполнения, сохраняя эти данные для просмотра. Данные разделяются по временным диапазонам, благодаря чему вы можете просматривать закономерности использования и узнавать об изменениях плана запроса на сервере. Хранилище запросов можно настроить с помощью инструкции ALTER DATABASE SET .

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

Внимание

Если вы используете хранилище запросов для аналитики рабочих нагрузок в SQL Server 2016 (13.x), планируйте установку исправлений масштабируемости производительности в кб 4340759 как можно скорее.

включение хранилища запросов;

• Хранилище запросов включается по умолчанию для новых баз данных SQL Azure и управляемого экземпляра SQL Azure.
• хранилище запросов по умолчанию не включен для SQL Server 2016 (13.x), SQL Server 2017 (14.x), SQL Server 2019 (15.x). Он включен по умолчанию в режиме READ_WRITE для новых баз данных, начиная с SQL Server 2022 (16.x). Чтобы включить функции для улучшения журнала производительности, устранения проблем с планом запросов и включения новых возможностей в SQL Server 2022 (16.x), рекомендуется включить хранилище запросов во всех базах данных.
• Хранилище запросов не включено по умолчанию для новых баз данных Azure Synapse Analytics.

Использование страницы "Хранилище запросов" в SQL Server Management Studio

1. В обозревателе объектов щелкните правой кнопкой мыши базу данных и выберите пункт Свойства.

   Примечание.

   Требуется по крайней мере версия 16 Management Studio.

2. В диалоговом окне Свойства базы данных перейдите на страницу Хранилище запросов .

3. В поле Режим работы (запрошенный) выберите значение Чтение и запись.

Использование инструкций Transact-SQL

Используйте инструкцию ALTER DATABASE, чтобы включить хранилище запросов для указанной базы данных. Например:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

В Azure Synapse Analytics включите Хранилище запросов без дополнительных параметров, например:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON;

Другие параметры синтаксиса, связанные с хранилищем запросов, см. в статье Параметры ALTER DATABASE SET (Transact-SQL).

Примечание.

Хранилище запросов не может быть включено для баз данных master и tempdb.

Внимание

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

Сведения о хранилище запросов

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

Так как хранилище запросов сохраняет несколько планов выполнения на запрос, оно может принудительно применить политики, чтобы заставить процессор запросов использовать конкретный план выполнения для запроса. Это называется принудительным выполнением плана. Принудительное выполнение плана в хранилище запросов обеспечивается с использованием механизма, аналогичного указанию запроса USE PLAN , но не требует изменений в приложениях пользователей. Принудительное выполнение плана может решить проблему со снижением производительности запросов, вызванным изменением плана за очень короткий период времени.

Примечание.

хранилище запросов собирает планы для инструкций DML, таких как SELECT, INSERT, , MERGEUPDATEDELETEи BULK INSERT.

По проектированию хранилище запросов не собирает планы для инструкций DDL, таких как CREATE INDEXи т. д. хранилище запросов фиксирует совокупное потребление ресурсов, собирая планы для базовых инструкций DML. Например, хранилище запросов могут отображаться SELECT и INSERT инструкции, выполняемые внутренне для заполнения нового индекса.

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

Статистика ожидания — это еще один источник информации, который помогает устранить неполадки с производительностью в ядро СУБД. Довольно долго статистика ожидания была доступна только на уровне экземпляра, что затрудняло обратное отслеживание до определенного запроса. Начиная с SQL Server 2017 (14.x) и База данных SQL Azure, хранилище запросов включает измерение, отслеживающее статистику ожидания. В следующем примере хранилище запросов позволяет собирать статистику ожидания.

ALTER DATABASE <database_name>
SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );

Ниже перечислены стандартные сценарии использования хранилища запросов.

• Быстрый поиск и устранение снижения производительности планов путем принудительного выполнения предыдущего плана запроса. Исправление запросов, производительность которых была недавно снижена из-за изменений в планах выполнения.
• Определение количества выполнений запросов за заданный период времени и помощь DBA в устранении неполадок с производительностью ресурсов.
• Определение первых n запросов (по времени выполнения, потреблению памяти и т. д.) за последние x часов.
• Аудит журнала планов запросов для указанного запроса.
• Анализ шаблонов использования ресурсов (ЦП, операций ввода-вывода и памяти) для определенной базы данных.
• Определение первых n-запросов, ожидающих ресурсы.
• Понимание характера ожидания для определенного запроса или плана.

Хранилище запросов содержит три хранилища:

• хранилище планов для сохранения сведений о планах выполнения;
• хранилище статистики времени выполнения для сохранения статистических сведений о выполнении;
• хранилище статистики ожидания для сохранения статистических сведений об ожидании.

Количество уникальных планов, которые можно сохранить для запроса в хранилище планов, ограничено параметром конфигурации max_plans_per_query . Для повышения производительности сведения записываются в хранилища асинхронно. Для уменьшения использования свободного места статистические данные времени выполнения в хранилище вычисляются для фиксированного интервала времени. Сведения в этих хранилищах доступны посредством запросов к представлениям каталога в хранилище запросов.

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

SELECT Txt.query_text_id, Txt.query_sql_text, Pln.plan_id, Qry.*, RtSt.*
FROM sys.query_store_plan AS Pln
INNER JOIN sys.query_store_query AS Qry
    ON Pln.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id
INNER JOIN sys.query_store_runtime_stats RtSt
ON Pln.plan_id = RtSt.plan_id;

Хранилище запросов для вторичных реплик

Область применения: SQL Server (начиная с SQL Server 2022 (16.x))

Функция хранилища запросов для вторичных реплик обеспечивает для рабочих нагрузок вторичных реплик такие же функции хранилища запросов, как и для первичных. Если хранилище запросов для вторичных реплик включено, реплики отправляют сведения о выполнении запросов, которые обычно сохраняются в хранилище запросов первичной реплики. Затем первичная реплика сохраняет данные на диск в собственном хранилище запросов. По сути, первичная и все вторичные реплики делят общее хранилище запросов. Хранилище запросов существует в первичной реплике и в нем хранятся данные для всех реплик сразу.

Полные сведения о хранилище запросов вторичных реплик см. в разделе хранилище запросов вторичных реплик группы доступности AlwaysOn.

Использование функции регрессивных запросов

Включив хранилище запросов, обновите информацию о базе данных в области обозревателя объектов, чтобы добавить раздел Хранилище запросов.

Примечание.

Для Azure Synapse Analytics представления хранилища запросов доступны в меню Системные представления в разделе базы данных панели обозревателя объектов.

Выберите регрессированные запросы, чтобы открыть область "Регрессированные запросы" в SQL Server Management Studio. На панели "Запросы со сниженной производительностью" отображаются запросы и планы, сохраненные в хранилище запросов. Используйте раскрывающийся список в верхней части списка для фильтрации запросов на основе различных критериев: длительность (мс) (по умолчанию), время ЦП (мс), логические операции чтения (КБ), логические операции записи (КБ), физические операции чтения (КБ), время CLR (мс), количество операций в памяти (КБ), количество строк, использованная памятью журнала (КБ), временная память базы данных (КБ) и время ожидания (мс).

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

Чтобы принудительно применить план, выберите запрос и план, а затем выберите Принудительно использовать план. Принудительно выполнять можно только те планы, которые были сохранены с помощью функции плана запросов и все еще хранятся в кэше плана запросов.

Поиск ожидающих запросов

Начиная с SQL Server 2017 (14.x) и База данных SQL Azure, статистика ожидания на запрос по времени доступна в хранилище запросов.

Типы ожидания в хранилище запросов объединены в категории ожидания. Сопоставление категорий ожидания с типами ожидания доступно в sys.query_store_wait_stats (Transact-SQL).

Выберите статистику ожидания запросов, чтобы открыть область статистики ожидания запросов в SQL Server Management Studio 18.0 или более поздней версии. Панель статистики ожидания запросов показывает диаграмму с главными категориями ожидания в хранилище запросов. Используйте раскрывающийся список в верхней части, чтобы выбрать статистические критерии для времени ожидания: среднее, максимальное, минимальное, std dev и total (по умолчанию).

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

Используйте раскрывающийся список в верхней части списка для фильтрации запросов на основе различных критериев времени ожидания для выбранной категории ожидания: avg, max, min, std dev и total (по умолчанию). Выберите план для просмотра графического плана запросов. С помощью кнопок можно просмотреть исходный запрос, принудительно применить и отменить план запросов, а также обновить отображаемые на экране сведения.

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

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

Предыдущая процедура	Новая процедура	Действие
Высокий уровень ожиданий RESOURCE_SEMAPHORE на базу данных	Высокий уровень ожиданий памяти в хранилище запросов для конкретных запросов	Найдите в хранилище запросов те запросы, которые используют больше всего памяти. Вероятнее всего, эти запросы препятствуют дальнейшей обработке затронутых запросов. Рекомендуется использовать указание запроса MAX_GRANT_PERCENT для этих запросов или затронутых запросов.
Высокий уровень ожиданий LCK_M_X на базу данных	Высокий уровень ожиданий блокировки в хранилище запросов для конкретных запросов	Проверьте текст затронутых запросов и выявите целевые сущности. Найдите в хранилище запросов другие запросы, изменяющие ту же сущность, которые часто выполняются и (или) имеют большую длительность. Найдя такие запросы, рекомендуется изменить логику приложения, чтобы улучшить параллелизм, или использовать менее строгий уровень изоляции.
Высокий уровень ожиданий PAGEIOLATCH_SH на базу данных	Высокий уровень ожиданий ввода-вывода буфера в хранилище запросов для конкретных запросов	Найдите в хранилище запросов запросы с большим числом физических операций чтения. Если они соответствуют запросам с высоким уровнем ожиданий ввода-вывода, рекомендуется ввести индекс для базовой сущности, чтобы выполнять поиск вместо сканирования и этим минимизировать временные затраты ввода-вывода для запросов.
Высокий уровень ожиданий SOS_SCHEDULER_YIELD на базу данных	Высокий уровень ожиданий ЦП в хранилище запросов для конкретных запросов	Найдите в хранилище запросов те запросы, которые используют больше всего ресурсов ЦП. Выявите те из них, у которых высокое использование ЦП коррелирует с высоким уровнем ожидания ЦП для затронутых запросов. Уделите внимание оптимизации запросов — может иметь место регрессия плана или отсутствующий индекс.

Варианты конфигурации

Сведения о доступных параметрах для настройки параметров хранилища запросов см. в разделе Параметры ALTER DATABASE SET (Transact-SQL).

Запросите представление sys.database_query_store_options, чтобы определить текущие параметры хранилища запросов. В представлении sys.database_query_store_options можно посмотреть о значениях дополнительные сведения.

Примеры настройки параметров конфигурации с помощью инструкций TransactSQL см. в разделе Управление параметрами.

Примечание.

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

Связанные представления, функции и процедуры

Просмотр хранилище запросов хранилище запросов с помощью Management Studio или с помощью следующих представлений и процедур.

Функции хранилища запросов

Функции помогают работать с хранилищем запросов.

sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)

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

В представлениях каталога отображаются сведения о хранилище запросов.

sys.database_query_store_options (Transact-SQL)

sys.query_context_settings (Transact-SQL)

sys.query_store_plan (Transact-SQL)

sys.query_store_query (Transact-SQL)

sys.query_store_query_text (Transact-SQL)

sys.query_store_runtime_stats (Transact-SQL);

sys.query_store_wait_stats (Transact-SQL)

sys.query_store_runtime_stats_interval (Transact-SQL)

sys.database_query_store_internal_state (Transact-SQL)

Хранимые процедуры в хранилище запросов

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

sp_query_store_flush_db (Transact-SQL)

sp_query_store_reset_exec_stats (Transact-SQL)

sp_query_store_force_plan (Transact-SQL)

sp_query_store_unforce_plan (Transact-SQL)

sp_query_store_remove_plan (Transact-SQL)

sp_query_store_remove_query (Transact-SQL)

sp_query_store_clear_message_queues (Transact-SQL)

sp_query_store_consistency_check (Transact-SQL)¹

¹ В чрезвычайных ситуациях хранилище запросов может перейти в состояние ERROR (Ошибка) из-за внутренних ошибок. Начиная с версии SQL Server 2017 (14.x), в таких случаях хранилище запросов можно восстановить, выполнив хранимую процедуру sp_query_store_consistency_check в соответствующей базе данных. Дополнительные сведения приведены в описании для столбца actual_state_desc в статье sys.database_query_store_options.

обслуживание хранилище запросов

Рекомендации и рекомендации по обслуживанию и управлению хранилище запросов были расширены в этой статье: рекомендации по управлению хранилище запросов.

Аудит производительности и устранение проблем

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

Другие статьи о производительности:

• Сценарии использования хранилища запросов

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

• Хранимые процедуры хранилища запросов (Transact-SQL)
• Представления каталога хранилища запросов (Transact-SQL)
• sys.database_query_store_options (Transact-SQL)
• Динамическая статистика запросов
• Монитор активности
• Сбор данных в хранилище запросов
• Наблюдение и настройка производительности
• Средства контроля и настройки производительности
• Использование хранилища запросов с выполняющейся в памяти OLTP
• Рекомендации по хранилище запросов
• Рекомендации по управлению хранилище запросов
• Настройка производительности с помощью хранилища запросов
• Указания хранилища запросов
• Сценарии использования хранилища запросов
• Открытие монитора активности (среда SQL Server Management Studio)