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

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

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

Доступность

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Проверьте, что хранилище запросов включен на вторичных репликах

Вы можете проверить, включен ли хранилище запросов в реплике 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
• Гео вторичное
• Вторичная географическая отказоустойчивость
• Именованная реплика

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

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

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

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

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

Если AG1 и AG2 настроены для разрешения подключений только для чтения, когда рабочая нагрузка только для чтения выполняется на любой из вторичных реплик AG1, статистика выполнения хранилище запросов отправляется на основную реплику 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;

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

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

Применимо к: База данных SQL Azure

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

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

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

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

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

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

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

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

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

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

SELECT pending_message_count,
       messaging_memory_used_mb
FROM sys.database_query_store_internal_state;

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

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

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

Прежде чем данные хранилище запросов сохраняются в первичной реплике из вторичных реплик, доступных для чтения, запросам и планам могут быть назначены временные идентификаторы в локальном представлении хранилище запросов в памяти - MEMORYCLERK_QUERYDISKSTORE_HASHMAP. Идентификаторы запросов и планов могут отображаться как отрицательные числа и служат заполнителями до тех пор, пока основная реплика не назначит достоверный идентификатор, что происходит после того, как хранилище запросов определяет, что запрос соответствует настроенным требованиям режима захвата. Если политика захвата данных настроена, вы можете проверить требования, которые необходимо выполнить, запросив представление системного 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)
• подсказки хранилище запросов
• сценарии использования хранилище запросов
• sys.database_query_store_options (Transact-SQL)
• методы лучших практик для мониторинга рабочих нагрузок с помощью хранилище запросов
• Лучшие практики для управления хранилище запросов
• Настройте производительность с помощью хранилище запросов