Read-only queries are now captured in the Primary Query Store.

1) Scope & Symptoms

Observed symptom: Queries for the read‑only replica are now visible in the primary database’s Query Store.

Please verify the application’s connection string and check whether it is using read‑only or read‑write intent. If the connection string specifies ApplicationIntent=ReadOnly, the query will automatically route to the secondary replica.

Example read‑only connection string:

server=tcp:<server>.database.windows.net,1433;
Database=<db>;
ApplicationIntent=ReadOnly;
Trusted_Connection=False;
Encrypt=True;
Asynchronous Processing=true;
TrustServerCertificate=True;
User Id=<user>; Password=<pwd>

2) Background / Known Platform Behavior

Azure SQL Database now captures read‑only replica queries directly in both Query Store and Performance Insight.
This enhancement provides clear visibility into how queries execute on the primary and the read‑only replica, helping identify performance patterns more accurately. With this update, Query Store records execution data from both primary and read‑only replicas for all new and existing databases, making troubleshooting simpler, faster, and more reliable.

Key points:

• Read‑only workload behavior remains unchanged — applications can continue using Read‑Only routing normally.
• Queries executed on Read‑Only replicas now appear in QDS and DMVs on the primary server, providing full transparency.
• To identify where a query actually ran (Primary vs. Replica), you can check the new column in sys.query_store_runtime_stats.

 sys.query_store_runtime_stats (Transact-SQL) - SQL Server | Microsoft Learn

3) Verification: Confirm Execution on Replica (T‑SQL)

Run this query either on primary database or read replica

SELECT TOP 200 
    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 CAST(qrs.replica_group_id AS NVARCHAR(200))
    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,
    TRY_CONVERT(XML, qsp.query_plan) AS [query_plan]
FROM sys.query_store_runtime_stats_interval qsrsi 
INNER JOIN sys.query_store_runtime_stats qrs 
    ON qrs.runtime_stats_interval_id = qsrsi.runtime_stats_interval_id  
INNER JOIN sys.query_store_plan qsp 
    ON qsp.plan_id = qrs.plan_id  
INNER JOIN sys.query_store_query qsq  
    ON qsq.query_id = qsp.query_id  
INNER JOIN sys.query_store_query_text qsqt 
    ON qsq.query_text_id = qsqt.query_text_id
WHERE qsrsi.start_time >= DATEADD(HOUR, -8, GETUTCDATE())  
GROUP BY 
    qsq.query_id, 
    qsq.query_hash, 
    qsp.query_plan_hash,
    qsp.plan_id, 
    qrs.replica_group_id, 
    qsqt.query_sql_text,
    qsp.query_plan
ORDER BY 
    SUM(qrs.count_executions * qrs.avg_logical_io_reads) DESC;

Above query will give you result like below, where you can see the replica_type column to verify.

4) Use the T‑SQL query below to verify whether your query (if you have the Query ID) was executed on the Primary or a Secondary replica.

SELECT 
    qsq.query_id,
    CASE qrs.replica_group_id
        WHEN 1 THEN 'PRIMARY'
        WHEN 2 THEN 'SECONDARY'
        ELSE CAST(qrs.replica_group_id AS NVARCHAR(50))
    END AS replica_type,
    SUM(qrs.count_executions) AS execs
FROM sys.query_store_runtime_stats qrs
JOIN sys.query_store_plan qsp ON qsp.plan_id = qrs.plan_id
JOIN sys.query_store_query qsq ON qsq.query_id = qsp.query_id
WHERE qsq.query_id = YourQueryID
GROUP BY qsq.query_id, qrs.replica_group_id;

Note: If your read‑only query on a replica is using a suboptimal plan, or if you need to force or unforce a plan through Query Store, you can use this information to identify the appropriate replica_group_id, and then apply the required hint using sys.sp_query_store_set_query_hints.

Reference Article: sys.sp_query_store_set_hints (Transact-SQL) - SQL Server | Microsoft Learn

5) User‑Friendly Check via Query Store UI

Use SSMS Query Store reports to visually confirm execution location:

- Open SSMS and connect to the database.

- Expand Database → Query Store.

- Open “Top Resource Consuming Queries” (or “Queries with High Variation”).

- Use the **Replica** dropdown to choose Primary, Secondary, GeoSecondary, or GeoHASecondary.

- Confirm recent intervals show the query under **SECONDARY**.

Figure: Query Store — Replica filter dropdown. (Replica option visible in **SSMS 2021** onwards).

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

• General purpose with active geo-replication or a failover group configuration (no built-in high availability replicas; requires geo-replication or failover group configuration for secondary support)
• Premium (includes built-in high availability replicas; active geo-replication or failover groups also supported)
• Business critical (includes built-in high availability replicas; active geo-replication or failover groups also supported)

Reference Article:

Query Store for Secondary Replicas - SQL Server | Microsoft Learn

sys.query_store_replicas (Transact-SQL) - SQL Server | Microsoft Learn