Query Store для доступных для чтения вторичных реплик (предварительная версия)

Применимо к: SQL Server 2022 (16.x) и более поздним версиям Azure SQL DatabaseAzure SQL Managed Instance

Query Store для вторичных реплик с возможностью чтения предоставляет аналитические данные о рабочих нагрузках, выполняемых на таких репликах. При включении вторичные реплики передают сведения о выполнении запросов потока (например, статистику времени выполнения и ожидания) в основную реплику, где данные сохраняются в Query Store и отображаются во всех репликах.

Доступность

Query Store для читаемых вторичных реплик доступен начиная с SQL Server 2025 (17.x), а также в Azure SQL Database и Azure SQL Managed Instance с Always-up-to-date update policy. Для SQL Server 2022 (16.x) Query Store для вторичных реплик, доступных для чтения, необходимо включить флаг трассировки 12606, чтобы использовать эту функцию.

В следующей таблице приведены сведения о доступности и состоянии хранилища запросов для доступных для чтения вторичных файлов.

¹ хранилище запросов для доступных для чтения вторичных файлов в настоящее время недоступно в уровне служб гипермасштабирования Azure SQL Database.
² Хранилище запросов для доступных для чтения вторичных файлов остается в ограниченной предварительной версии для SQL Server 2022 (16.x), поэтому не поддерживается в рабочей среде и по умолчанию отключается. Чтобы включить только Query Store для доступных для чтения вторичных реплик в SQL Server 2022 (16.x), необходимо включить флаг трассировки 12606 для основной и всех доступных для чтения вторичных реплик. Флаг трассировки 12606 не предназначен для промышленных развертываний, основанных на SQL Server 2022 (16.x). Дополнительные сведения см. в заметках о выпуске SQL Server 2022.

Поддерживаемые сценарии высокой доступности

• Прежде чем использовать Query Store для доступных для чтения вторичных реплик в экземпляре SQL Server 2025 (17.x), необходимо настроить группу доступности Always On.

• Для Azure SQL Database, Query Store для читаемых вторичных реплик поддерживает следующие планы обслуживания:

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

• Для Azure SQL Managed Instance с политикой Always-up-to-date Query Store для доступных для чтения вторичных реплик поддерживает следующие уровни служб:

  • Общая цель с группой отработки отказа (отсутствие встроенных реплик высокой доступности; требуется конфигурация группы отработки отказа для обеспечения вторичной поддержки).
  • Критически важный для бизнеса (включает встроенные резервные копии для обеспечения высокой доступности)

Включить Query Store для читаемых вторичных реплик

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

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

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

ALTER DATABASE [Database_Name]
    FOR SECONDARY
    SET QUERY_STORE = ON
    (OPERATION_MODE = READ_WRITE);

Включите автоматическую коррекцию планов для вторичных реплик

Применимо к: SQL Server 2022 (16.x) и более поздних версий, Azure SQL Database.

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

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

ALTER DATABASE [Database_Name]
FOR SECONDARY
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

Отключите Query Store для вторичных реплик

Чтобы отключить функцию Query Store для дополнительных реплик во всех вторичных репликах, подключитесь к базе данных master в реплике primary и выполните следующий сценарий для каждой требуемой базы данных:

ALTER DATABASE [Database_Name]
    FOR SECONDARY
    SET QUERY_STORE = ON
    (OPERATION_MODE = READ_ONLY);

Проверьте, что Query Store включен на вторичных репликах

Вы можете проверить, включен ли Query Store в реплике secondary, подключився к базе данных на вторичной реплике и выполните следующую инструкцию T-SQL:

SELECT desired_state_desc,
       actual_state_desc,
       readonly_reason
FROM sys.database_query_store_options;

Результаты запроса представления каталога sys.database_query_store_options должны указывать на то, что фактическое состояние хранилища запросов — READ_CAPTURE_SECONDARY, с readonly_reason — 8.

Замечания

Терминология

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

В настоящее время поддерживаются роли:

• Primary
• Secondary
• Гео вторичное
• Вторичная географическая отказоустойчивость
• Именованная реплика

Принцип работы

Данные, связанные с запросами, можно анализировать как нагрузки по ролям. Query Store для вторичных реплик, настроенных для чтения, позволяет отслеживать производительность любой уникальной рабочей нагрузки, доступной только для чтения, которая может выполняться на вторичных репликах. Данные агрегируются на уровне роли. Например, конфигурация SQL Server distributed availability group может состоять из следующих элементов:

• Одна первичная реплика, часть группы доступности 1 (AG1)

• Две локальные вторичные реплики также являются частью AG1

• Одна удаленная первичная реплика в другом расположении, которая входит в отдельную группу доступности (AG2). В терминах SQL Server он также часто называется глобальным переадресатором, однако Query Store для доступных для чтения вторичных реплик будет распознавать и ссылаться на него как на реплику Geo secondary, предполагая, что это вторичная реплика, географически распределенная.

Если AG1 и AG2 настроены для разрешения подключений только для чтения, когда рабочая нагрузка только для чтения выполняется на любой из вторичных реплик AG1, статистика выполнения Query Store отправляется на основную реплику AG1 и агрегируется и сохраняется как данные, созданные из роли secondary, прежде чем эти данные будут отправлены обратно всем вторичным репликам, включая глобальный форвардер в AG2. Когда отдельная рабочая нагрузка выполняется в отношении AG2, глобальный пересылатель, данные отправляются обратно в основную реплику AG1 и сохраняются в качестве данных, созданных из Geo secondary роли.

С точки зрения наблюдаемости представление системного каталога sys.query_store_runtime_stats расширено, чтобы определить роль, из которой возникла статистика выполнения. Существует связь между этим представлением и представлением системного каталога sys.query_store_replicas, которое может предоставить более удобочитаемое имя роли. В SQL Server столбец replica_name имеет значение NULL. Однако столбец заполняется для уровня службы гипермасштабирования, replica_name если присутствует именованная реплика и используется для рабочих нагрузок только на чтение.

Пример запроса T-SQL, который можно использовать для предоставления общего анализа топ-50 запросов за последние 8 часов, которые использовали ресурсы ЦП со всех реплик будет:

-- Top 50 queries by CPU across all replicas in the last 8 hours
DECLARE @hours AS INT = 8;

SELECT TOP 50 qsq.query_id,
              qsp.plan_id,
              CASE qrs.replica_group_id WHEN 1 THEN 'PRIMARY' WHEN 2 THEN 'SECONDARY' WHEN 3 THEN 'GEO SECONDARY' WHEN 4 THEN 'GEO HA SECONDARY' ELSE CONCAT('NAMED REPLICA_', qrs.replica_group_id) END AS replica_type,
              qsq.query_hash,
              qsp.query_plan_hash,
              SUM(qrs.count_executions) AS sum_executions,
              SUM(qrs.count_executions * qrs.avg_logical_io_reads) AS total_logical_reads,
              SUM(qrs.count_executions * qrs.avg_cpu_time / 1000.0) AS total_cpu_ms,
              AVG(qrs.avg_logical_io_reads) AS avg_logical_io_reads,
              AVG(qrs.avg_cpu_time / 1000.0) AS avg_cpu_ms,
              ROUND(TRY_CAST (SUM(qrs.avg_duration * qrs.count_executions) AS FLOAT) / NULLIF (SUM(qrs.count_executions), 0) * 0.001, 2) AS avg_duration_ms,
              COUNT(DISTINCT qsp.plan_id) AS number_of_distinct_plans,
              qsqt.query_sql_text
FROM sys.query_store_runtime_stats_interval AS qsrsi
     INNER JOIN sys.query_store_runtime_stats AS qrs
         ON qrs.runtime_stats_interval_id = qsrsi.runtime_stats_interval_id
     INNER JOIN sys.query_store_plan AS qsp
         ON qsp.plan_id = qrs.plan_id
     INNER JOIN sys.query_store_query AS qsq
         ON qsq.query_id = qsp.query_id
     INNER JOIN sys.query_store_query_text AS qsqt
         ON qsq.query_text_id = qsqt.query_text_id
WHERE qsrsi.start_time >= DATEADD(HOUR, -@hours, GETUTCDATE())
GROUP BY qsq.query_id, qsq.query_hash, qsp.query_plan_hash, qsp.plan_id, qrs.replica_group_id, qsqt.query_sql_text
ORDER BY SUM(qrs.count_executions * qrs.avg_cpu_time / 1000.0) DESC, AVG(qrs.avg_cpu_time / 1000.0) DESC;

Отчеты Query Store в SQL Server Management Studio (SSMS) 21 и более поздних версиях предоставляют раскрывающийся список Replica, через который можно просматривать данные Query Store в различных наборах и ролях реплик. Кроме того, в представлении Object explorer узел Query Store отражает текущее состояние Query Store (например, ), если подключен к вторичной реплике, доступной для чтения.

Query Store для удобочитаемых вторичных реплик телеметрии в Azure SQL Database

Применимо к: Azure SQL Database

При потоковой передаче данных runtime statistics из Query Store через параметры диагностики Azure два столбца помогают идентифицировать исходный источник телеметрии реплики.

• is_primary_b: Логическое значение, указывающее, получены ли данные из первичной реплики (true) или вторичной реплики (false)
• replica_group_id: целое число, соответствующее роли реплики

Эти столбцы важны для уточнения метрик и данных производительности при анализе нагрузок в наборах реплик. При настройке параметров диагностики для потоковой передачи статистики выполнения Query Store в Log Analytics, Центры событий или Azure Storage, убедитесь, что ваши запросы и панели управления включают эти столбцы для правильной сегментации данных по роли реплики. Дополнительные сведения о настройке параметров диагностики и доступных метрик см. в разделе Diagnostic settings in Azure Monitor.

Рекомендации по повышению производительности Query Store для читаемых вторичных реплик

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

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

Если включена Query Store для доступных для чтения вторичных реплик, она не создает отдельную конечную точку сети. Вместо этого он устанавливает новый логический путь связи по существующему транспортному уровню:

• Для Azure SQL Database (кроме Hyperscale), Azure SQL Managed Instance и SQL Server используется транспортный уровень высокой доступности и восстановления после аварии (HADR) Always On.

• Для Azure SQL Database Hyperscale используется другой транспортный уровень, называемый транспортным уровнем ввода-вывода удаленных больших двоичных объектов. Транспортный уровень удалённого ввода-вывода Blob — это канал связи между вычислительными узлами и серверами службы журналов и страниц. Транспортный уровень удаленного ввода-вывода большого двоичного объекта предоставляет надежный зашифрованный канал для перемещения записей журналов и страниц данных.

Этот путь мультиплексирует данные выполнения Query Store (текст запроса, планы, статистики времени выполнения и ожидания) вместе с обычным трафиком записи журнала с использованием того же зашифрованного сеанса. Функция имеет собственные очереди записи и получения, которые можно просматривать, запрашивая sys.database_query_store_internal_state представление с точки зрения любой реплики:

SELECT pending_message_count,
       messaging_memory_used_mb
FROM sys.database_query_store_internal_state;

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

Видимость идентификатора или идентификатора плана отрицательного запроса

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

Прежде чем данные Query Store сохраняются в первичной реплике из вторичных реплик, доступных для чтения, запросам и планам могут быть назначены временные идентификаторы в локальном представлении Query Store в памяти - MEMORYCLERK_QUERYDISKSTORE_HASHMAP. Идентификаторы запросов и планов могут отображаться как отрицательные числа и служат заполнителями до тех пор, пока основная реплика не назначит достоверный идентификатор, что происходит после того, как Query Store определяет, что запрос соответствует настроенным требованиям режима захвата. Если политика захвата данных настроена, вы можете проверить требования, которые необходимо выполнить, запросив представление системного sys.database_query_store_options каталога.

SELECT query_capture_mode_desc,
       capture_policy_execution_count,
       capture_policy_total_compile_cpu_time_ms,
       capture_policy_total_execution_cpu_time_ms
FROM sys.database_query_store_options;

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

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

• параметры ALTER DATABASE SET (Transact-SQL)
• sys.query_store_replicas
• sys.query_store_plan_forcing_locations (Transact-SQL)
• sys.sp_query_store_force_plan (Transact-SQL)
• подсказки Query Store
• сценарии использования Query Store
• sys.database_query_store_options (Transact-SQL)
• методы лучших практик для мониторинга рабочих нагрузок с помощью Query Store
• Лучшие практики для управления Query Store
• Настройте производительность с помощью Query Store