Query Data Store Forced Plan behavior on AlwaysOn Readable Secondary

 

We are getting following questions repeatedly from many customers so thought of writing a quick blog to explain the behavior.

Question: For example if you have Query Data Store (QDS) enabled for user database participating in Always On Availability Groups and you Forced Plan for specific query, what happened if same query running on readable secondary will it use Forced Plan?

Answer: QDS is not supported on Readable Secondary, so though you have Forced Plan on Primary Replica you “may” see different plan on secondary database because QDS do not force same plan on readable secondary.

Please note, SQL do not create Plan Guide to Force Execution Plan in QDS.

QDS needs to store runtime stats in databases which is not possible when database in readable state.

I am sharing diagram from following article for your reference: /en-us/sql/relational-databases/performance/how-query-store-collects-data

 

Question: Will QDS retain FORCED Plan information when Database failover from Primary replica to secondary Replica?

Answer: Yes, QDS store Forced Plan information in sys.query_store_plan table, so in case of failover you will continue to see same behavior on new Primary.

select * from sys.query_store_plan where is_forced_plan=1

Question: How QDS Force Query Plan? Is it creating Plan Guide to Force Plan?

Answer: As I mentioned earlier QDS do not use plan guide, instead of it use “USE PLAN” query hint which takes an xml_plan as an argument, so QDS basically pull this XML plan information from query_plan column from sys.query_store_plan table.

You can refer following diagram to know how SQL SERVER force Plan.

 

 

 

Vikas Rana | Twitter | Linkedin | Support Escalation Engineer
Microsoft India GTSC