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

  • Статья

Применяется к:SQL ServerAzure SQL DatabaseAzure, управляемому экземпляру SQL Azure

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

Использование последней версии SQL Server Management Studio

СРЕДА SQL Server Management Studio содержит набор пользовательских интерфейсов, предназначенных для настройки хранилища запросов и использования собранных данных о рабочей нагрузке. Скачайте последнюю версию SQL Server Management Studio.

Краткое описание того, как использовать хранилище запросов в сценариях устранения неполадок, см. в Query Store Azure blogs.

Используйте анализ производительности запросов в базе данных SQL Azure

При запуске хранилища запросов в Базе данных SQL Azure можно использовать аналитику производительности запросов для анализа потребления ресурсов с течением времени. Хотя вы можете использовать Management Studio и Azure Data Studio для получения подробных сведений о потреблении ресурсов для всех запросов, таких как ЦП, память и операции ввода-вывода, аналитика производительности запросов позволяет быстро и эффективно определить их влияние на общее потребление DTU для базы данных. Дополнительные сведения см. в разделе Анализ производительности запросов в базе данных SQL Azure.

Использование хранилища запросов с пулом эластичных баз данных

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

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

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

Query Store troubleshooting

Включите хранилище запросов с помощью Среда Management Studio, как описано в предыдущем разделе, или выполните следующую инструкцию Transact-SQL:

ALTER DATABASE [DatabaseOne] SET QUERY_STORE = ON;

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

Представления хранилища запросов Management Studio работают с набором метрик выполнения, каждый из которых выражается как любой из следующих статистических функций:

Версия SQL Server Метрика выполнения Статистическая функция
SQL Server 2016 (13.x) CPU time (время ЦП), Duration (длительность), Execution count (число выполнений), Logical reads (число логических операций чтения), Logical writes (число логических операций записи), Memory consumption (потребление памяти), Physical reads (число физических операций чтения), CLR time (время среды CLR), Degree of parallelism (DOP) (степень параллелизма) и Row count (число строк) Average (среднее), Maximum (максимум), Minimum (минимум), Standard Deviation (стандартное отклонение), Total (всего)
SQL Server 2017 (14.x) CPU time (время ЦП), Duration (длительность), Execution count (число выполнений), Logical reads (число логических операций чтения), Logical writes (число логических операций записи), Memory consumption (потребление памяти), Physical reads (число физических операций чтения), CLR time (время среды CLR), Degree of parallelism (DOP) (степень параллелизма), Row count (число строк), Log memory (память, занимаемая журналом), TempDB memory (память, занимаемая базой данных TempDB) и Wait times (время ожидания) Average (среднее), Maximum (максимум), Minimum (минимум), Standard Deviation (стандартное отклонение), Total (всего)

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

Query Store views

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

Представление SQL Server Management Studio Сценарий
Регрессированные запросы Выявите запросы, метрики выполнения для которых недавно регрессировали (т. е. стали хуже).
Используйте это представление для сопоставления наблюдаемых проблем производительности в приложении с фактическими запросами, которые необходимо улучшить или исправить.
Общее потребление ресурсов Анализируйте общее потребление ресурсов базы данных для любой из метрик выполнения.
Используйте это представление для определения шаблонов ресурсов (дневная и ночная рабочие нагрузки) и оптимизации общего потребления для базы данных.
Основные запросы, потребляющие ресурсы Выберите интересующую метрику выполнения и определите запросы, которые имели максимальные значения в указанном промежутке времени.
Используйте это представление, чтобы сосредоточиться на наиболее важных запросах, которые оказывают самое большое воздействие на потребление ресурсов базы данных.
Запросы с принудительными планами Здесь приводятся планы, которые были принудительно выполнены ранее с помощью Query Store.
В этом представлении можно быстро получить доступ ко всем текущим принудительным планам.
Запросы с высокой вариативностью Анализ запросов с высокой вариативностью выполнения с учетом всех доступных параметров, таких как длительность, время ЦП, ввод-вывод данных и использование памяти, в соответствующем временном интервале.
Используйте это представление для выявления запросов с сильно варьируемой производительностью, которые могут влиять на работу пользователей в приложениях.
Статистика ожидания запросов Анализируйте категории ожидания, наиболее активные в базе данных, и определите, какие запросы вносят наибольший вклад в выбранную категорию ожидания.
Используйте это представление, чтобы проанализировать статистику ожидания и определить запросы, которые могут влиять на работу пользователей в приложениях.

Область применения: начиная с SQL Server Management Studio версии 18.0 и SQL Server 2017 (14.x).
Отслеживаемые запросы Отслеживайте выполнение наиболее важных запросов в реальном времени. Как правило, эти представления используются, когда имеются запросы с принудительными планами и требуется убедиться в стабильной производительности запросов.

Совет

Подробное описание того, как использовать Среда Management Studio для выявления запросов с самым большим потреблением ресурсов и исправления тех, чьи метрики ухудшились из-за изменения плана, см. в Query Store Azure Blogs.

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

  • Если запрос выполнялся с несколькими планами и последний план оказался значительно хуже предыдущего, можно применить механизм принудительного использования плана. SQL Server пытается принудительно заставить план в оптимизаторе. Если это не удастся сделать, будет порождено событие XEvent и оптимизация будет выполнена как обычно.

    Query Store force plan

    Заметка

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

    Форма Значение
    Круг Запрос выполнен, то есть обычное выполнение успешно завершено.
    Square Запрос отменен, то есть клиент прервал выполнение.
    Треугольник Сбой, то есть выполнение прервано с исключением.

    Кроме того, размер фигуры отражает количество выполнений запроса за указанный интервал времени. Чем больше это количество, тем больше размер фигуры.

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

    Query Store show plan

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

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

Совет

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

Убедитесь, что хранилище запросов собирает данные запросов непрерывно

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

USE [QueryStoreDB];
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Разница между actual_state_desc и desired_state_desc показывает, что произошло автоматическое изменение режима работы. Самое частое изменение — автоматическое переключение хранилища запросов в режим "только чтение". В исключительно редких случаях хранилище запросов может оказаться в состоянии ошибки из-за внутренних ошибок.

Если фактическое состояние доступно только для чтения, используйте readonly_reason столбец для определения первопричины. Скорее всего, вы обнаружите, что хранилище запросов перешло в режим "только чтение" из-за превышения квоты на размер. В этом случае readonly_reason для 65536 задано значение 65536. Другие причины см. в разделе sys.database_query_store_options (Transact-SQL).

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

  • Увеличьте максимальный размер хранилища с помощью параметра MAX_STORAGE_SIZE_MBALTER DATABASE.

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

    ALTER DATABASE [QueryStoreDB] SET QUERY_STORE CLEAR;

Можно применить одно или оба этих действия, выполнив следующую инструкцию, которая явно изменяет режим работы обратно на режим чтения и записи:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

Выполните следующие упреждающие действия.

  • Вы можете предотвратить автоматические изменения режима работы, применяя рекомендации. Если размер хранилища запросов всегда будет меньше максимально допустимого значения, это существенно уменьшит вероятность перехода в режим "только чтение". Активируйте политику на основе размера, как описано в разделе Настройка хранилища запросов, чтобы хранилище запросов автоматически очищало данные при достижении предельного размера.
  • Чтобы обеспечить сохранение последних данных, настройте политику на основе времени для регулярного удаления устаревшей информации.
  • Наконец, следует рассмотреть возможность установки автоматическогорежима записи запросов, так как в нем отфильтровываются запросы, которые обычно меньше всего соответствуют вашей рабочей нагрузке.

Состояние ошибки

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

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Если проблема сохраняется, это означает повреждение данных в хранилище запросов, сохраненных на диске.

Начиная с SQL Server 2017 (14.x), хранилище запросов можно восстановить, выполнив sys.sp_query_store_consistency_check хранимую процедуру в затронутой базе данных. Прежде чем пытаться выполнять операцию восстановления, необходимо отключить хранилище запросов. Ниже приведен пример запроса для использования или изменения с целью проверки согласованности и восстановления QDS:

IF EXISTS (SELECT * FROM sys.database_query_store_options WHERE actual_state=3) 
BEGIN
  BEGIN TRY
    ALTER DATABASE [QDS] SET QUERY_STORE = OFF
    Exec [QDS].dbo.sp_query_store_consistency_check
    ALTER DATABASE [QDS] SET QUERY_STORE = ON
    ALTER DATABASE [QDS] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
  END TRY
 
  BEGIN CATCH 
    SELECT  
      ERROR_NUMBER() AS ErrorNumber  
      ,ERROR_SEVERITY() AS ErrorSeverity  
      ,ERROR_STATE() AS ErrorState  
      ,ERROR_PROCEDURE() AS ErrorProcedure  
      ,ERROR_LINE() AS ErrorLine  
      ,ERROR_MESSAGE() AS ErrorMessage; 
  END CATCH;   
END

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

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

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE CLEAR;
GO

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Избегайте использования не параметризованных запросов

Использовать запросы без параметров не рекомендуется за исключением случаев, когда этого никак нельзя избежать. Пример — в случае нерегламентированного анализа. Кэшированные планы не могут использоваться повторно, что заставляет оптимизатор запросов компилировать запросы для каждого уникального текста запроса. См. рекомендации по использованию принудительной параметризации.

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

Следуйте приведенным ниже рекомендациям.

Совет

При использовании решения объектно-реляционного сопоставления (ORM), такого как Entity Framework (EF), запросы приложений, такие как деревья запросов LINQ, отправляемых вручную, или определенные необработанные запросы SQL, могут не параметризоваться. Это влияет на повторное использование плана и возможность отслеживать запросы в хранилище запросов. Дополнительные сведения см. в статьях Кэширование и параметризация запросов EF и Необработанные запросы SQL EF.

Поиск непараметровизованных запросов в хранилище запросов

Чтобы узнать количество планов, сохраненных в хранилище запросов с помощью приведенного ниже запроса, можно использовать динамические административные представления хранилища запросов, SQL Server, управляемый экземпляр SQL Azure или базу данных SQL Azure:

SELECT count(Pl.plan_id) AS plan_count, Qry.query_hash, Txt.query_text_id, Txt.query_sql_text
FROM sys.query_store_plan AS Pl
INNER JOIN sys.query_store_query AS Qry
    ON Pl.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id
GROUP BY Qry.query_hash, Txt.query_text_id, Txt.query_sql_text
ORDER BY plan_count desc;

В следующем примере создается сеанс расширенных событий для записи события query_store_db_diagnostics, что может быть пригодиться при диагностике потребления ресурсов запросов. В SQL Server такой сеанс расширенных событий по умолчанию создает файл событий в папке журналов SQL Server. Например, при установке SQL Server 2019 (15.x) в Windows по умолчанию файл событий (XEL-файл) создается в папке C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log. Для управляемого экземпляра SQL Azure укажите вместо этого расположение хранилища BLOB-объектов Azure. Дополнительные сведения см. в статье Файл событий XEvent для управляемого экземпляра SQL Azure. Событие qds.query_store_db_diagnostics для базы данных SQL Azure недоступно.

CREATE EVENT SESSION [QueryStore_Troubleshoot] ON SERVER 
ADD EVENT qds.query_store_db_diagnostics(
      ACTION(sqlos.system_thread_id,sqlos.task_address,sqlos.task_time,sqlserver.database_id,sqlserver.database_name))
ADD TARGET package0.event_file(SET filename=N'QueryStore',max_file_size=(100))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);

С помощью этих данных можно узнать количество планов в хранилище запросов, а также многие другие статистические сведения. Чтобы понять объем используемой памяти и количество планов, отслеживаемых хранилищем запросов, изучите столбцы plan_count, query_count, max_stmt_hash_map_size_kb и max_size_mb. Если количество планов больше обычного, это может указывать на увеличение числа непараметризованных запросов. Используйте приведенный ниже запрос динамических административных представлений хранилища запросов для проверки параметризованных запросов и непараметризованных запросов в хранилище запросов.

Для параметризованных запросов:

SELECT qsq.query_id, qsqt.query_sql_text
FROM sys.query_store_query AS qsq 
INNER JOIN sys.query_store_query_text AS qsqt
ON qsq.query_text_id= qsqt.query_text_id 
WHERE qsq.query_parameterization_type<>0 or qsqt.query_sql_text like '%@%';

Для непараметризованных запросов:

SELECT qsq.query_id, qsqt.query_sql_text
FROM sys.query_store_query AS qsq 
INNER JOIN sys.query_store_query_text AS qsqt
ON qsq.query_text_id= qsqt.query_text_id 
WHERE query_parameterization_type=0;

Избегайте шаблона DROP и CREATE для хранения объектов

Хранилище запросов связывает запись запроса с содержащим объектом, например хранимой процедурой, функцией или триггером. При повторном создании содержащего объекта создается новая запись запроса для того же текста запроса. Это препятствует отслеживанию статистики производительности для этого запроса с течением времени и использованию механизма принудительного применения планов. Чтобы избежать такой ситуации, используйте процесс ALTER <object> для изменения определения содержащего объекта везде, где это возможно.

Регулярно проверяйте состояние принудительных планов

Форсирование плана — это удобный механизм, позволяющий исправлять производительность важных запросов и делать их более предсказуемыми. Как и в случае указаний планов и структур планов, не гарантируется, что это будет использовано в будущих выполнениях. Обычно, когда схема базы данных изменяется так, что объекты, упоминаемые в плане выполнения, изменяются или удаляются, принудительное выполнение плана начинает завершаться сбоем. В этом случае SQL Server возвращается к перекомпиляции запросов, а фактическая причина сбоя принудительного выполнения отображается в sys.query_store_plan. Следующий запрос возвращает информацию о принудительно выполненных планах.

USE [QueryStoreDB];
GO

SELECT p.plan_id, p.query_id, q.object_id as containing_object_id,
    force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan AS p
JOIN sys.query_store_query AS q on p.query_id = q.query_id
WHERE is_forced_plan = 1;

Полный список причин см. в статье sys.query_store_plan. Кроме того, можно использовать XEvent query_store_plan_forcing_failed для отслеживания и устранения неполадок с принудительным выполнением планов.

Совет

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

Избегайте переименования баз данных для запросов с принудительными планами

Планы выполнения ссылаются на объекты по трехкомпонентным именам, например database.schema.object.

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

Использование хранилища запросов на критически важных серверах

Глобальные флаги трассировки 7745 и 7752 можно использовать для повышения уровня доступности баз данных с помощью хранилища запросов. Дополнительные сведения см. в статье о флагах трассировки.

  • Флаг трассировки 7745 предотвращает поведение по умолчанию, когда хранилище запросов записывает данные на диск до завершения работы SQL Server. Это означает, что будут потеряны все собранные данные хранилища запросов, которые еще не сохранены на диске, за период времени, определенный в DATA_FLUSH_INTERVAL_SECONDS.
  • Флаг трассировки 7752 включает асинхронную загрузку для хранилища запросов. Это позволяет восстановить базу данных и выполнять запросы раньше, чем будет полностью восстановлено хранилище запросов. По умолчанию загрузка хранилища запросов выполняется в синхронном режиме. Этот вариант не позволяет выполнять запросы до полного восстановления хранилища запросов, но зато предотвращает потерю запросов при сборе данных.

Заметка

Начиная с SQL Server 2019 (15.x), это поведение управляется подсистемой, а флаг трассировки 7752 не действует.

Внимание

Если вы используете хранилище запросов для аналитики рабочей нагрузки JIT в SQL Server 2016 (13.x), планируйте установку улучшений масштабируемости производительности в SQL Server 2016 (13.x) с пакетом обновления 2 (SP2) с пакетом обновления 2 (KB 4340759) как можно скорее. Без этих улучшений, если база данных испытывает значительные рабочие нагрузки, может возникнуть конкуренция циклической блокировки и производительность сервера может снизиться. В частности, вы можете столкнуться с интенсивной конкуренцией циклической блокировки QUERY_STORE_ASYNC_PERSIST или циклической блокировки SPL_QUERY_STORE_STATS_COOKIE_CACHE. После применения этого улучшения хранилище запросов больше не будет вызывать конфликты циклической блокировки.

Внимание

Если вы используете хранилище запросов для jit-аналитики рабочей нагрузки в SQL Server (SQL Server 2016 (13.x) через SQL Server 2017 (14.x)), планируйте установить улучшение масштабируемости производительности в SQL Server 2016 (13.x) с пакетом обновления 2 (SP2 CU15), SQL Server 2017 (14.x) CU23 и SQL Server 2019 (15.x) CU9 как можно скорее. Без этого улучшения, если база данных находится под тяжелыми нерегламентированными рабочими нагрузками, хранилище запросов может использовать большой объем памяти и производительность сервера может стать медленной. После применения этого улучшения хранилище запросов накладывает внутренние ограничения на объем памяти, который может использовать различные компоненты, и может автоматически изменять режим работы на только для чтения до тех пор, пока не будет возвращено достаточно памяти в ядро СУБД. Обратите внимание, что ограничения внутренней памяти хранилища запросов не документированы, так как они могут быть изменены.

Использование хранилища запросов в активной георепликации базы данных SQL Azure

Хранилище запросов на вторичной активной геореплике Базы данных SQL Azure будет доступным только для чтения копии действия на первичной реплике.

Избегайте несоответствия уровней с георепликацией базы данных SQL Azure. База данных-получатель должна иметь такой же или практически такой же объем вычислительных ресурсов, что и база данных-источник, и находиться на том же уровне службы, что и база данных-источник. Взгляните на тип ожидания HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO в sys.dm_db_wait_stats, который указывает на регулирование скорости журнала транзакций на первичной реплике из-за задержки на вторичной реплике.

Дополнительные сведения об оценке и настройке размера Базы данных-получателя SQL Azure с активной георепликацией см. в разделе Настройка базы данных-получателя.

Сохранить хранилище запросов в соответствии с рабочей нагрузкой

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

См. также

Далее