Share via

Does the read-only replica have its own query optimizer separate from the primary?

Rahul Randive 9,176 Reputation points Microsoft Employee
2024-07-17T21:46:34.9266667+00:00

How are the execution plans for the queries that run on the read-only replica created?  Does the primary create the execution plans for both primary and read-only replicate or does the read-only replica have the ability to create the execution plan independent of the primary?

 Can the same query, which runs on both the primary and the read-only replica have different execution plans?  If so, how is that possible?

If a query is compiled on the read-only replica first, or if it only runs on the read-only replica, does the primary still create the execution plan?

If a query on the primary has a forced execution plan, and that query is also run on the read-only replica, will the forced execution plan be used on the read-only replica?

 PS - Based on common issues that we have seen from customers and other sources, we are posting these questions to help the Azure community.

Azure SQL Database
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. GeethaThatipatri-MSFT 29,017 Reputation points Microsoft Employee
    2024-07-17T21:54:41.7933333+00:00

    Greetings!

    The execution plan for a query is compiled when the query is executed for the first time or if the plan is no longer in the buffer cache. The plan is compiled based on statistics, and the optimizer produces multiple plans and chooses the one with less cost.

    The primary replica creates the execution plans for both primary and read-only replicas

    For a better understanding, here is the doc link for your reference (Read queries on replicas - Azure SQL Database & Azure SQL Managed Instance | Microsoft Learn)

    If a query is compiled on the read-only replica first, or if it only runs on the read-only replica, does the primary still create the execution plan?

     The primary replica creates the execution plans for both primary and read-only replicas. Therefore, if a query is compiled on the read-only replica first or only runs on the read-only replica, the primary replica will still create the execution plan.

     If a query on the primary has a forced execution plan, and that query is also run on the read-only replica, will the forced execution plan be used on the read-only replica?

    If a query on the primary has a forced execution plan and that query is also run on the read-only replica, the forced execution plan will be used on the read-only replica as well. This is because the read-only replica is a copy of the primary replica and has the same execution plan.

    Regards

    Geetha

    0 comments