SQL server generating different query_id for same query_text_id after mirroring failover

Question

SQL server generating different query_id for same query_text_id after mirroring failover

udhayan d 181

I am facing a strange problem with one of my SQL Server 2017 Clusters. I faced a query regression in the past and forced a known good plan for that query_id in query store.

When the planned database failover happens every month for OS patching from HostA to HostB the optimizer generates a different query_id for the same query_text_id.

I found the context_settings_id remains same but the query_hash changes. Because the query_id has changed the forced plan is no longer valid and optimizer generates new plans and performance degrades until I go and force the good plan.

It is not the case of some pressure/limit on the query store to cleanup the old plans. I verified it because when the database again fails back from HostB to HostA after a week which is also a monthly planned activity,the query_id is intact and the plan forcing is intact.

ChatGPT says "you’re running into is fundamentally: failover/cold cache → recompiles → Query Store may capture a “new” query identity (new query_id) even when query_text_id and context_settings_id look the same. Mirroring is just the thing that reliably triggers that cold-start compile pattern in your environment every month."

Is this something anybody experienced, because I dont know what can I check or do here to make sure the query_id remains the same after the mirroring failover other than doing some workaround automation to have a post failover step to find new query_id and force the known goon plan_id

0 comments

2 answers

Your answer

Answer 1

Erland Sommarskog 133.7K MVP Volunteer Moderator

I would guess that the database id is different on the two servers. This matters, because the database id is part of the sql_handle, and therefore you get a new query_text_id, and it goes downhill from there.

I seem to recall that an MVP colleague ran into this.

Akhil Gajavelly 1,810 Reputation points Microsoft External Staff Moderator

2026-02-02T05:14:01.4533333+00:00

Hi @udhayan d ,

Thanks for the clarification @Erland Sommarskog . That makes sense if the database_id differs between the two hosts, the sql_handle (and therefore query_hash and query_id) would change, which explains why the forced plan is not retained after failover. verify the database_id on both servers, but this explanation aligns with the observed behavior. Appreciate you sharing this insight.

Thanks,
Akhil.
Akhil Gajavelly 1,810 Reputation points Microsoft External Staff Moderator

2026-02-02T05:17:26.66+00:00

Hi @udhayan d ,

Thanks for the clarification @Erland Sommarskog .
The explanation about database_id changing across failover affecting sql_handle / query_id is the likely root cause here. This aligns with how Query Store identifies queries and explains why forced plans don’t survive the failover but work again after failback.

Could you please confirm if this matches what you’re seeing in your environment? If this resolves the issue, consider marking the answer as accepted so it can help others running into the same behavior.

Thanks,
Akhil.
Akhil Gajavelly 1,810 Reputation points Microsoft External Staff Moderator

2026-02-04T06:18:30.8933333+00:00

Hi @udhayan d ,

Just checking back on this. What you described is expected behavior with Query Store after an AG failover the database_id changes, so query_id / sql_handle change as well, which is why forced plans don’t persist across failover but work again after failback.

If this explains what you’re seeing, please consider marking the answer as accepted so it can help others who run into the same issue.

Thanks,
Akhil.
udhayan d 181 Reputation points

2026-02-13T14:05:52.59+00:00

Thanks @Erland Sommarskog and @Akhil Gajavelly
Yeah I observed that the database_id is not the same but this never caused any issues in the past. Only causing problems last couple of months.

Now I have forced the good plan on both the nodes, so ideally I should not face this issue when the failover happens this month.

Just started capturing all the info about this query_id this month, so my worry is if the query_id is going to change every month because of some other identified issue, then the plan forcing wont work and I need to do some other alerting/automation to identify the regression and force the plan. I will get more clarity next week, because failover is scheduled to happen.

Any safe way to modify the database_id to keep same in both the nodes?
Erland Sommarskog 133.7K Reputation points MVP Volunteer Moderator

2026-02-13T15:21:43.57+00:00

Yeah I observed that the database_id is not the same but this never caused any issues in the past. Only causing problems last couple of months.

You might not have noticed. I remember that my MVP colleague talked about this a couple of years back.

Any safe way to modify the database_id to keep same in both the nodes?

You would need to drop and restore/re-attach databases in order. I am under the impression that the database_id is set to the first free number in sys.databases.

It goes without saying that this comes with some downtime, and you may have to tear down the AG and set it up again.
Akhil Gajavelly 1,810 Reputation points Microsoft External Staff Moderator

2026-04-15T05:39:49.7733333+00:00
Hi @udhayan d ,

Thanks for confirming and testing this.

As discussed, this behavior is expected since database_id is part of the sql_handle, and it differs across replicas, which leads to different query_id values after failover.

There isn’t a safe or supported way to keep database_id consistent across nodes without significant rebuild/reconfiguration.

The recommended approach is to either:

Force the plan on both replicas (as you’ve already done), or

Automate post-failover logic to identify the new query_id and reapply the known good plan.

Monitoring based on query_text rather than query_id can also help make this more robust.

Please share your observations after the next failover that will help confirm if the current approach fully stabilizes the behavior.

Thanks,
Akhil.
Akhil Gajavelly 1,810 Reputation points Microsoft External Staff Moderator

2026-04-23T09:15:01.8266667+00:00

Hi @udhayan d ,

Just checking back on this. If your last failover behaved as expected and the plan forcing / automation is holding stable, then you should be in a good state.

If you still notice inconsistencies after failover, the next step would be to capture what changed (new query_id, plan not applying, etc.) so we can fine-tune the approach.

For now, we’ll assume things are working as expected from your side. Feel free to reopen if anything comes up during future failovers.

Thanks,
Akhil.

Answer 2

The issue you're experiencing with SQL Server generating a different query_id for the same query_text_id after a failover can indeed be related to how SQL Server handles query compilation and execution plans during a cold start. When a failover occurs, the database may experience a cold cache situation, which leads to recompilation of queries. This recompilation can result in a new query_id being assigned, even if the query_text_id and context_settings_id remain the same.

This behavior is not uncommon, especially in environments where mirroring or failover clustering is involved. The optimizer may generate a new plan based on the current state of the database and its statistics at the time of the failover, which can lead to performance regressions if the new plan is not optimal.

To mitigate this issue, you might consider the following approaches:

Plan Guides: Use plan guides to force specific execution plans for your queries, ensuring that the optimizer uses the desired plan even after a failover.
Parameterization: Ensure that your queries are parameterized, as this can help in reusing execution plans and potentially reduce the number of unique query_ids generated.
Monitoring and Automation: Implement monitoring to detect when a failover occurs and automate the process of identifying the new query_id and reapplying the forced plan. This can help maintain performance consistency without manual intervention.
Statistics Maintenance: Regularly update statistics to ensure that the optimizer has the most accurate data to make decisions about query plans.

These strategies can help manage the impact of failovers on query performance and maintain the integrity of your execution plans across failover events.

References:

Share via

SQL server generating different query_id for same query_text_id after mirroring failover

2 answers

Your answer