Azure SQL Database Query Performance, misleading as it includes primary and standby data

Question

Query Performance is including information for primary and secondary (note this is a business critical read-only replica, not a geo). Take a query ID provided, go to query store, it shows on Secondary, not Primary. This gives misleading information as it is reporting high consuming queries that are not running on this DB, they're actually running on the secondary. I'm betting this was introduced with this enhancement "https://techcommunity.microsoft.com/blog/azuredbsupport/read-only-queries-are-now-captured-in-the-primary-query-store-/4485048" in January. Why isn't there an option to select Primary/Secondary? What is this doing to automation? If it's including queries from the secondary, then making changes on primary based on this, could be very bad. Some cases could be good. Either way, it's misleading and I'd argue wrong. The page is labeled for the primary, no other metrics are for the secondary but this one buries information for the secondary in it.

Answer 1

While reviewing Performance Insights in the Azure Portal, please note that the portal presents query information for both primary and secondary databases together, as it relies on the Query Store data from the primary database, which also reflects activity from the secondary replica as a part of product improvement.

At present, if you need to clearly distinguish queries executed on the primary versus the secondary database, this can be done using SQL Server Management Studio (SSMS).

As mentioned earlier, there is a plan to enhance the Azure Portal in the future so that it clearly shows whether a query was executed on the primary or the secondary replica. Our product team is currently working on this. However, at this moment, there is no confirmed ETA for when this enhancement will be available.

To help the product team better understand customer needs and improve this experience, I kindly request you to share your feedback using the link: https://feedback.azure.com/d365community/forum/ef2b2b38-2f25-ec11-b6e6-000d3a4f0f84. Your feedback is highly valuable and will help drive future improvements.

Answer 2

Hi **Nancy Wotawa,

**Please be informed that if the query runs on secondary then it will show as secondary only.

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.

Read-only workload executed on secondary replicas may appear under the primary’s Query Store and Query Performance Insight on Azure Portal, even though execution still occurs on the replica. This behavior is by design and enabled to provide better end-to-end performance visibility. The document also outlines quick methods via T‑SQL and SSMS Query Store reports to verify whether a query actually ran on the primary or secondary replica.

To identify where a query actually ran (Primary vs. Replica), you can check the new column in **sys.query_store_runtime_stats.
**https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-runtime-stats-transact-sql?view=sql-server-ver17

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;

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

• 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.

https://learn.microsoft.com/en-us/sql/relational-databases/performance/query-store-for-secondary-replicas?view=sql-server-ver17

Answer 3

Hi Nancy Wotawa,
Thank you for calling this out, your observation is absolutely valid, and I understand why this feels misleading when you’re looking at Query Performance from the Primary perspective.

What you’re seeing is tied to the recent enhancement where read-only queries executed on replicas are now captured in the Primary Query Store. Because of this change, Query Store on the Primary is no longer limited to just Primary-executed workload , it can also include queries coming from the read-only replica.

In a Business Critical configuration, where read traffic is intentionally routed to the secondary, this can surface queries in the Primary Query Performance view that never actually ran on the Primary. At the moment, the UX does not clearly distinguish between Primary and Secondary execution, nor does it provide a filter to separate them. That’s why it appears as though high resource queries are impacting the Primary when they are in fact running on the replica.

From a support standpoint, this is currently expected behavior, but we agree it introduces ambiguity when interpreting performance data. Because of this, we recommend validating the execution context of top queries before taking any tuning actions. This can be done by correlating with connection intent (read-only routing) or using DMVs to confirm where the workload is originating from.

You also raised an important point around automation, any process that relies on Query Store data (for example, automatic tuning or plan forcing) should take this behavior into account, since decisions made solely on aggregated data could target the wrong workload.

There is a clear gap here in terms of visibility and control. Having the ability to distinguish or filter queries by Primary vs Secondary would make this feature much more usable, especially for read-scale scenarios like yours. I’ll make sure this feedback is captured and shared with the product team.

Please let me know if you’d like help with queries/DMVs to differentiate workloads, happy to assist further.