Azure SQL Query Store Now Captures Read-Only Queries

Azure SQL Database and SQL Server 2025 now capture query performance data from read-only replicas — a feature called “Query Store for Secondary Replicas.” Previously, Query Store only tracked queries running on the primary (read-write) database, leaving performance issues on read replicas invisible to administrators.

This update solves a critical blind spot: applications using ApplicationIntent=ReadOnly connections to offload read traffic couldn’t be monitored effectively. Now all query execution data flows back to the primary replica’s Query Store for unified performance analysis.

Read-only replicas handle report queries, analytics, and read-heavy workloads to reduce load on the primary database. But when performance problems occurred on these replicas, administrators had limited visibility:

• No query execution history: Couldn’t see which queries ran on replicas
• No execution plans: Unable to diagnose why queries were slow
• No runtime statistics: Missing CPU time, duration, and read counts
• Manual DMV queries required: Had to connect to each replica separately and query system views in real-time

The new feature provides complete visibility into replica workloads from a single Query Store on the primary.

How It Works

Architecture Overview

When enabled, read-only replicas send query execution data back to the primary replica using the same secure communication channel that handles database replication:

1. Query runs on secondary replica (read-only copy)
2. Execution statistics collected (plan, duration, CPU, reads)
3. Data transmitted to primary via encrypted Always On transport layer
4. Primary persists to Query Store with replica identification metadata
5. Unified view available showing both primary and replica queries

Identifying Replica Source

Query Store data now includes two columns to distinguish where queries ran:

These columns let you segment performance analysis by replica, answering questions like “Are reports slower on replicas than on the primary?”

Availability

Important: SQL Server 2022 requires trace flag 12606 to enable this feature, but Microsoft explicitly states this is preview-only and not supported for production use.

Setting It Up

For Azure SQL Database

No action required — the feature enables automatically when you have read replicas configured. Query Store captures replica data by default.

For SQL Server 2025

Prerequisites:

• Always On availability group configured
• At least one readable secondary replica
• Query Store enabled on the database

Verify it’s working:

SELECT 
    actual_state_desc,
    readonly_reason
FROM sys.database_query_store_options;

Expected result: actual_state_desc shows READ_CAPTURE_SECONDARY when the feature is active.

Check replica message queue status:

SELECT 
    pending_message_count,
    messaging_memory_used_mb
FROM sys.database_query_store_internal_state;

This shows how many messages are waiting to be sent from replicas to the primary.

Analyzing Replica Performance

Query to See Replica vs. Primary Activity

SELECT 
    qsq.query_id,
    qsqt.query_sql_text,
    qsrs.is_primary_b,
    qsrs.replica_group_id,
    qsrs.count_executions,
    qsrs.avg_duration / 1000.0 AS avg_duration_ms,
    qsrs.avg_cpu_time / 1000.0 AS avg_cpu_ms
FROM sys.query_store_query AS qsq
JOIN sys.query_store_query_text AS qsqt 
    ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_runtime_stats AS qsrs
    ON qsq.query_id = qsrs.query_id
WHERE qsrs.is_primary_b = 0  -- Only secondary replicas
ORDER BY qsrs.avg_duration DESC;

Common Analysis Scenarios

Compare same query performance across replicas:

SELECT 
    qsq.query_id,
    LEFT(qsqt.query_sql_text, 100) AS query_preview,
    CASE WHEN qsrs.is_primary_b = 1 THEN 'Primary' 
         ELSE 'Secondary' END AS replica_type,
    qsrs.avg_duration / 1000.0 AS avg_duration_ms,
    qsrs.count_executions
FROM sys.query_store_query AS qsq
JOIN sys.query_store_query_text AS qsqt 
    ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_runtime_stats AS qsrs
    ON qsq.query_id = qsrs.query_id
WHERE qsq.query_id = 1234  -- Replace with your query_id
ORDER BY qsrs.is_primary_b DESC;

Find queries running only on replicas:

SELECT DISTINCT
    qsq.query_id,
    LEFT(qsqt.query_sql_text, 200) AS query_text
FROM sys.query_store_query AS qsq
JOIN sys.query_store_query_text AS qsqt 
    ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_runtime_stats AS qsrs
    ON qsq.query_id = qsrs.query_id
WHERE qsrs.is_primary_b = 0
AND NOT EXISTS (
    SELECT 1 FROM sys.query_store_runtime_stats AS qsrs2
    WHERE qsrs2.query_id = qsq.query_id
    AND qsrs2.is_primary_b = 1
);

Monitoring with Azure Diagnostic Settings

When streaming Query Store data to Log Analytics, Event Hubs, or Azure Storage, the replica identification columns automatically appear in your logs.

Configure diagnostic settings to include:

• QueryStoreRuntimeStatistics
• QueryStoreWaitStatistics

In your Log Analytics queries, filter by replica:

QueryStoreRuntimeStatistics
| where is_primary_b == false  // Secondary replicas only
| summarize 
    AvgDuration = avg(avg_duration),
    TotalExecutions = sum(count_executions)
  by query_id, replica_group_id
| order by AvgDuration desc

Important Considerations

Performance Impact

• Minimal overhead: Uses existing replication channel
• Queues handle bursts: Capture and receive queues buffer messages
• Memory usage: Monitor messaging_memory_used_mb in sys.database_query_store_internal_state

Limitations

• Azure Hyperscale: Uses different transport layer (Remote Blob I/O) but works the same way
• Query Performance Insight (QPI): Doesn’t currently support replica_group_id filtering
• Data retention: Same as primary Query Store retention settings

Troubleshooting

If replica data isn’t appearing:

1. Verify Query Store is enabled: SELECT actual_state_desc FROM sys.database_query_store_options
2. Check it’s not in READ_ONLY mode due to storage limits
3. Confirm readable secondaries exist and are online
4. Review message queue: SELECT * FROM sys.database_query_store_internal_state

Query Store for Secondary Replicas eliminates the performance monitoring blind spot for read-only workloads. Instead of manually connecting to each replica and querying DMVs in real-time, you now have historical performance data for all replicas in one centralized Query Store.

The feature works automatically in Azure SQL Database and SQL Server 2025, requiring no configuration changes. Query execution data flows from secondaries to the primary via the encrypted replication channel, with minimal performance overhead.

For administrators managing read-heavy workloads distributed across replicas, this provides essential visibility: which queries run where, how they perform, which execution plans they use, and whether replica performance matches the primary.

The addition of is_primary_b and replica_group_id columns makes it easy to segment analysis by replica, compare performance across the replica set, and identify queries that only run on secondaries.

If you’re using Azure SQL Database with read replicas or SQL Server Always On with readable secondaries, this feature is already working for you—start querying Query Store with the new replica columns to gain insights into your read-only workload performance.

Follow us on Bluesky, LinkedIn, and X to Get Instant Updates