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

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

  • Статья

Область применения: SQL Server 2016 (13.x) и более поздние версии Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (только для выделенного пула SQL)SQL база данных в Microsoft Fabric

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

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

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

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

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

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

Чтобы отслеживать производительность в базе данных SQL Fabric, используйте панель мониторинга производительности.

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

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

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

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

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

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

ALTER DATABASE [DatabaseOne] SET QUERY_STORE = ON;

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

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

Версия SQL Server Метрика выполнения Статистическая функция
SQL Server 2016 (13.x) время ЦП, длительность, число выполнений, логические чтения, логические записи, потребление памяти, физические чтения, время CLR, степень параллелизма (DOP), и число строк Среднее, Максимум, Минимум, Стандартное отклонение, Сумма
SQL Server 2017 (14.x) Время ЦП, длительность, число выполнений, число логических операций чтения, число логических операций записи, потребление памяти, число физических операций чтения, время исполнения CLR, степень параллелизма, число строк, память, занимаемая журналом, память, занимаемая базой данных TempDB и время ожидания Среднее, Максимум, Минимум, Стандартное отклонение, Всего

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

Снимок экрана из SSMS, показывающий расположение представлений Хранилища запросов.

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

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

Снимок экрана из SSMS с кнопкой принудительного использования плана в хранилище запросов.

Примечание.

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

Фигура Значение
Круг Запрос выполнен, то есть обычное выполнение успешно завершено.
Квадрат Отменено, что означает, что выполнение было прервано по инициативе клиента.
Треугольник Неудача, то есть выполнение прервано из-за исключения.

Кроме того, размер фигуры отражает количество выполнений запроса за указанный интервал времени. Размер увеличивается с увеличением числа выполнений.

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

Снимок экрана из SSMS плана хранилища запросов с выделенным уведомлением об отсутствующих индексах.

Если вы запускаете рабочую нагрузку на базе данных 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. Другие причины см. в разделе sys.database_query_store_options (Transact-SQL).

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

  • Увеличьте максимальный размер хранилища с помощью MAX_STORAGE_SIZE_MB параметра ALTER 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, управляемом экземпляре Azure SQL или базе данных Azure SQL.

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 в данных событий, чтобы понять объем используемой памяти и количество планов, отслеживаемых хранилищем запросов. Если число планов выше нормального, это может указывать на увеличение непараметризованных запросов. Используйте приведенный ниже запрос динамических управляемых представлений (DMVs) в Хранилище запросов для анализа параметризованных и непараметризованных запросов.

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

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 рассмотрите функцию подсказки хранилища запросов для принудительного применения подсказок к запросам без изменений кода. Для получения дополнительной информации и примеров см. указы Query Store.

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

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

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

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

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

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

Примечание.

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

Внимание

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

Внимание

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

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

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

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

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

Держите хранилище запросов настроенным в соответствии с вашей рабочей нагрузкой.

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

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