Stabilizing Azure SQL Database Queries With Query Store Plan Forcing

An Azure SQL Database query that is fast for one parameter value and slow for the next is a classic parameter sniffing problem. The optimizer compiles a plan for the first set of parameter values it sees and reuses it, so a plan that is ideal for a selective value can be disastrous for a common value and vice versa. The useful takeaway is that Query Store plus plan forcing can make that performance deterministic without changing indexes or application code.

Why parameter sniffing is visible in Azure SQL Database

Azure SQL Database compiles parameterized queries once and caches the plan. Parameter sniffing means the initial sniffed values shape cardinality estimates and join and access choices. When runtime distributions drift from the sniffed values, the same query text can show wide variance in duration and logical reads.

Query Store is a built-in store that persists query text, plan shapes, and runtime aggregates per query. It is available on General Purpose and Business Critical tiers on v12 and later, with retention configurable up to 30 days by default. It lets you compare the same query_id across different plans and parameter variations over time.

Making regressions observable with Query Store

Enable Query Store in the target database where you will run diagnostics. This requires db_owner or CONTROL on the database.

ALTER DATABASE CURRENT SET QUERY_STORE = ON;

Verify capture is active by checking that rows appear in sys.query_store_plan and sys.query_store_runtime_stats. Query Store adds storage overhead in the database, so monitor size and retention on heavily loaded workloads.

Identify a candidate query by text pattern and look for multiple plans with divergent runtime stats.

SELECT qs.query_id, qst.query_sql_text
FROM sys.query_store_plan qs
JOIN sys.query_store_query q ON qs.query_id = q.query_id
JOIN sys.query_store_query_text qst ON q.query_text_id = qst.query_text_id
WHERE qst.query_sql_text LIKE N'%your_pattern%';

Meaningful placeholders are your_pattern, query_id and plan_id. Review avg_duration and execution count in sys.query_store_runtime_stats per plan_id to decide which plan correlates with stable performance for the typical workload.

Plan forcing as a surgical mitigation

Plan forcing locks a specific plan_id for a query_id. The optimizer will continue to use the forced plan until you release it, regardless of new parameter values. This stabilizes performance without modifying query text, indexes, or application code.

Force a plan in the database context where Query Store is enabled.

EXEC sys.sp_query_store_force_plan @query_id = <query_id>, @plan_id = <plan_id>;

Confirm the force is registered in sys.query_store_plan where is_forced_plan = 1. A practical check is to compare avg_duration in sys.query_store_runtime_stats before and after forcing for the same query_id.

Rollback is supported because forcing is metadata only. Release the force when data distribution changes or a better plan emerges.

EXEC sys.sp_query_store_unforce_plan @query_id = <query_id>, @plan_id = <plan_id>;

Risk: a forced plan prevents the optimizer from adopting improvements if data skew changes. Review forced plans periodically and release them when the workload shifts.

Worked example decision flow

Problem: a reporting query with a @CustomerId parameter shows intermittent slowness. Query Store shows two plans for the same query_id, one with consistently lower avg_duration for the majority of executions.

Decision: force the lower avg_duration plan for the query_id. Do not change indexes or add OPTION hints. Monitor runtime stats for stability and watch for plan regression signals such as rising avg_duration or increased waits.

Limitation: plan forcing does not fix root causes like missing indexes, poor parameter design, or data skew. It is a targeted mitigation for unstable plans, not a comprehensive solution. Query Store storage growth can impact performance, so set appropriate retention and cleanup policies and verify in your environment.

Actionable closing

Enable Query Store, identify a query_id with plan variance, compare runtime stats across plan_ids, and force the plan that matches typical workload behavior. Schedule a review to release the force if distribution changes. Verify all version and tier capabilities in your Azure SQL Database before applying.