Microsoft's October 9 Azure SQL support guidance walks through a query slowdown where an intended index-seek plan could no longer be forced after its index was removed. Query Store exposed the dependency through NO_INDEX. This is fresh troubleshooting guidance, not a new Azure SQL feature release. Microsoft's case walkthrough.
The operational lesson is worth bringing into the next maintenance review: preserving a plan configuration does not preserve every object that plan depends on.
Separate the Symptoms from the Explanation
The published example compares a CustomerId index seek with a clustered-index scan. The reported plan estimates are not, on their own, a measured latency result. Microsoft explicitly says that explaining the forcing failure and demonstrating the runtime regression are separate tasks. Evidence in the example.
In an incident, I would start by identifying the affected request, its time window, and the database context. Preserve the relevant query and plan identifiers before changing anything.
Then compare observations that answer different questions. The application trace shows the user impact. The execution plan shows the access strategy. The deployment history shows what changed. None should silently stand in for the others.
Ask whether the comparison uses a representative workload. A single fast execution after an intervention is encouraging, but it is a weak acceptance test for a query whose parameters vary throughout the day.
Read the Forcing State Carefully
Microsoft Learn documents that is_forced_plan does not guarantee the exact plan will be used. The failure count increases on failed recompilation attempts, not every execution, and resets when forcing is switched from off to on. NO_INDEX identifies an index referenced by the plan that no longer exists. Query Store plan metadata.
I would capture the current forcing flag, failure reason, failure count, plan XML, and observation timestamp together. That makes the support note interpretable instead of leaving a screenshot of one field with no context.
Avoid turning the count into an incident rate without additional evidence. In particular, a recent execution timestamp should not be treated as the exact timestamp of a forcing failure.
The Microsoft walkthrough distinguishes current Query Store metadata from dated event evidence. It also recommends checking actual index definitions and deployment changes before attributing the alternative plan to the reported slowdown. Investigation sequence.
Treat Index Removal as a Dependency Change
My recommendation is to add a dependency review to the index-maintenance proposal. Include the affected application owner, the retained plan evidence, known query hints, and any workload that runs less frequently than the normal observation window.
For example, a monthly reconciliation process deserves explicit coverage if the maintenance review normally considers only the previous working week. The purpose is to choose an observation period that represents the business, not to adopt one universal number of days.
Document what evidence is missing as well. If the relevant query history was not captured or has aged out, that uncertainty belongs in the change record rather than being interpreted as proof that an index is unnecessary.
Index Counters Answer a Different Question
The index-usage DMV reports seeks, scans, lookups, and updates. These describe index operations rather than distinct application queries; the counters also have reset behavior, including initialization when the database engine starts. Permission requirements vary by Azure SQL service objective. Index-usage metadata.
I would keep two dated snapshots and annotate known restarts, index changes, and maintenance events. Compare them only after checking whether the observations cover a meaningful continuous period.
Use the counters alongside the workload and plan review. A low observed read count may support further investigation, but it should not become an automatic removal decision without considering the consequence of the reads that do occur.
Put Recovery in the Change Record
A responsible proposal should name the recovery action, its owner, and the evidence needed to trigger it. Preserve the original index definition and review the feasibility and cost of restoring it before the maintenance window begins.
Do not make forced-plan removal or index recreation an automatic response to every slow query. Pick the intervention that matches the evidence, review it through the normal change process, and compare the workload afterward.
Practical Cloud Engineer Takeaway
Preserve query, plan, deployment, and observation identifiers before intervening.
Distinguish plan estimates from measured application performance.
Review forcing dependencies before removing an index.
Check capture coverage and counter resets before drawing conclusions.
Include infrequent business workloads and a reviewed recovery path.
Who Should Care?
Azure SQL administrators, application engineers, and platform teams responsible for schema changes, query performance, or production maintenance approvals.
Make the Maintenance Evidence Reusable
The best outcome is a change record that explains the dependency, the workload evidence, and the recovery decision. That record can help the next engineer understand a regression without reconstructing the entire investigation.
The workflow above is a recommendation based on Microsoft's guidance. I have not run this investigation against a customer database for this article.
Sources
Stay radical, stay curious, and keep pushing the boundaries of what is possible in the cloud.
Chriz
Beyond Cloud with Chriz
Comments