Query Store for readable secondaries

Applies to: SQL Server 2025 (17.x) Preview Azure SQL Database Azure SQL Managed Instance

Query Store for readable secondaries enables Query Store insights for workloads that run on secondary replicas. When enabled, secondary replicas stream query execution information (such as runtime and wait statistics) to the primary replica, where the data is persisted in Query Store and made visible across all replicas.

The feature was originally introduced in SQL Server 2022 (16.x), however it was off by default and required a trace flag to enable. This was due in part because the feature was and remains to be in a preview state for SQL Server 2022 (16.x).

Beginning with SQL Server 2025 (17.x) Preview and Azure SQL Database, Query Store for readable secondaries is enabled by default.

Enable Query Store for readable secondaries

Before you use Query Store for readable secondaries on a SQL Server 2025 (17.x) Preview instance, an Always On availability group must be configured.

For Azure SQL Database, Query Store for readable secondaries supports the following service tiers:

• General purpose with active geo-replication (no built-in high availability replicas; requires geo-replication configuration for secondary support)
• Premium (includes built-in high availability replicas; active geo-replication also supported)
• Business critical (includes built-in high availability replicas; active geo-replication also supported)
• Azure SQL Managed Instance with the Always-up-to-date policy
  • General purpose with a failover group
  • Business critical (includes built-in high availability replicas)

Applies to: SQL Server 2022 (16.x) and later versions.

If Query Store isn't already enabled and in READ_WRITE mode on the primary replica, you must enable it before proceeding. Execute the following script for each desired database on the primary replica:

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

To enable the Query Store on all readable secondaries, connect to the primary replica and execute the following script for each database that is to be enlisted to use the feature.

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

Enable automatic plan correction for secondary replicas

Applies to: SQL Server 2022 (16.x) and later versions, Azure SQL Database.

After enabling Query Store for secondary replicas, you can optionally enable automatic tuning to allow the automatic plan correction feature to force plans on secondary replicas. This enables the query optimizer to automatically identify and fix query performance issues caused by execution plan regressions on secondary replicas.

To enable automatic plan correction for secondary replicas, connect to the primary replica and execute the following script for each desired database:

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

Disable Query Store for secondary replicas

To disable the Query Store for secondary replicas feature on all secondary replicas, connect to the master database on the primary replica and execute the following script for each desired database:

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

Validate Query Store is enabled on secondary replicas

You can validate that Query Store is enabled on a secondary replica by connecting to the database on the secondary replica and execute the following t-sql statement:

SELECT desired_state_desc,
       actual_state_desc,
       readonly_reason
FROM sys.database_query_store_options;

The results from querying the sys.database_query_store_options catalog view should indicate that the Query Store's actual state is READ_CAPTURE_SECONDARY with a readonly_reason of 8.

Remarks

Terminology

A replica set is defined as a database's read/write replica (primary) and one or more read-only replicas (secondary) treated as a logical unit. A role in this context refers to the role of a specific replica. When a replica is serving in the primary role, it's the read/write replica that can perform both data modifications and read activity. When a replica is configured to only perform read-only activity, it's serving in a secondary role (secondary, geo secondary, geo ha secondary). Roles can change via a planned or unplanned failover events, when this happens, a primary can become a secondary or vice versa.

The currently supported roles are:

• Primary
• Secondary
• Geo secondary
• Geo HA secondary
• Named Replica

How it works

The data stored about queries can be analyzed as workloads on a role basis. Query Store for readable secondaries give you the ability to monitor the performance of any unique, read-only workload that might be executing against secondary replicas. The data is aggregated at the role level. For example, a SQL Server distributed availability groups configuration might consist of:

• One primary replica, part of Availability Group 1 (AG1)

• Two local secondary replicas, also part AG1

• One remote primary replica in another location that is part of a separate availability group (AG2). In SQL Server terms, it would also be commonly referred to as a global forwarder, however, the Query Store for readable secondaries feature will recognize and refer to it as a Geo secondary replica, assuming that it's a geographically distributed secondary replica.

If AG1 and AG2 are configured to allow read-only connections when a read-only workload executes against either of AG1's secondary replicas, the Query Store execution statistics are sent to AG1's primary replica and aggregated and persisted as data that was generated from the secondary role before that data is sent back to all of the secondary replicas including the global forwarder in AG2. When a separate workload is executed against AG2's primary, the global fowarder, its data is sent back to the primary replica of AG1 and persisted as data that was generated from Geo secondary role.

From an observability perspective, the sys.query_store_runtime_stats system catalog view is extended to help identify the role where the execution statistics originated from. There's a relationship between this view and the sys.query_store_replicas system catalog view, which can provide a more friendly name of the role. In SQL Server, Azure SQL Database, and Azure SQL Managed Instance, the replica_name column is NULL. However, the replica_name column is populated for the Hyperscale service tier if there's a named replica present and is being used for read-only workloads.

An example of a t-sql query that could be used to provide an overall analysis of the top 50 queries over the last 8 hours, which consumed CPU resources from all replicas would be:

-- 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;

The Query Store reports in SQL Server Management Studio (SSMS) 21 and later versions provide a Replica dropdown list, which provides a way to view Query Store data across various replica sets/roles. Also, inside of the Object explorer view, the Query Store node reflects the current state of Query Store (that is, READ_CAPTURE) if connected to a readable secondary replica.

Query Store for readable secondaries telemetry in Azure diagnostic settings

Applies to: Azure SQL Database

When streaming Query Store runtime statistics through Azure diagnostic settings, two columns are included to help identify the replica source of the telemetry data:

• is_primary_b: A Boolean value indicating whether the data originated from the primary replica (true) or a secondary replica (false)
• replica_group_id: An integer that corresponds to the replica role

These columns are essential for disambiguating metrics and performance data when analyzing workloads across replica sets. When configuring diagnostic settings to stream Query Store runtime statistics to Log Analytics, Event Hubs, or Azure Storage, ensure your queries and dashboards account for these columns to properly segment data by replica role. For more information on configuring diagnostic settings and available metrics, see Diagnostic settings in Azure Monitor.

Performance considerations for Query Store for readable secondaries

The channel used by secondary replicas to send query information back to the primary replica is the same channel used to keep secondary replicas up to date. What does channel mean here?

In an availability group (HADR) configuration, replicas synchronize with each other using a dedicated transport layer that carries log blocks, acknowledgments, and status messages between the primary and secondary replicas. This ensures data consistency and failover readiness.

When Query Store for readable secondaries is enabled, it doesn't create a separate network endpoint. Instead, it establishes a new logical communication path over the existing transport layer:

• For Azure SQL Database (non-Hyperscale), Azure SQL Managed Instance, and SQL Server, this uses the high availability and disaster recovery (HADR) Always On transport layer.

• For Azure SQL Database Hyperscale, the RbIo (Remote Blob I/O) transport layer is used, which is the communications channel between the compute nodes and the Log Service/Page Servers. RbIo provides a reliable, encrypted channel for moving log records and data pages.

This path multiplexes Query Store execution data (query text, plans, runtime/wait stats) alongside the normal log record traffic, using the same encrypted session. The feature has its own capture and receive queues, which can be viewed by querying the sys.database_query_store_internal_state view from any replica's perspective:

SELECT pending_message_count,
       messaging_memory_used_mb
FROM sys.database_query_store_internal_state;

Data from secondaries is persisted in the same Query Store tables on the primary, which can increase storage requirements. Under heavy load, you might observe latency or backpressure on the transport channel. The same ad hoc query capture limitations that apply to Query Store on the primary also apply to secondaries. For more information and guidance on managing Query Store size and capture policies, see Keep the most relevant data in Query Store.

Negative query ID/plan ID visibility

Negative IDs indicate temporary in-memory placeholders for queries/plans on secondaries before persistence to the primary.

Before Query Store data is persisted to the primary from readable secondary replicas, queries and plans might be assigned temporary identifiers within the local in-memory representation of Query Store - the MEMORYCLERK_QUERYDISKSTORE_HASHMAP. The query and plan IDs can appear as negative numbers and are placeholders until the primary replica assigns an authoritative identifier, which occurs after Query Store determines that a query meets the configured capture mode requirements. If a custom capture policy is in place, you can review the requirements that must be met by querying the sys.database_query_store_options system catalog view.

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;

Once a query is designated as captured, its runtime/wait statistics and plan can be persisted, and the local temporary IDs are replaced with positive IDs. This also allows you to use plan forcing or hinting capabilities.

Related content

• ALTER DATABASE SET options (Transact-SQL)
• sys.query_store_replicas
• sys.query_store_plan_forcing_locations (Transact-SQL)
• sys.sp_query_store_force_plan (Transact-SQL)
• Query Store hints
• Query Store Usage Scenarios
• sys.database_query_store_options (Transact-SQL)
• Best practices for monitoring workloads with Query Store
• Best practices for managing the Query Store
• Tune performance with the Query Store