Plan Stability and Plan Freezing
In this lesson, you will learn how to prevent your database from choosing a slower execution plan after data changes by "locking" a known-good plan in place.
The Problem: When "Good" Goes Bad
As you learned in previous lessons, the query optimizer relies on statistics to estimate how many rows a query will return. Sometimes, a sudden influx of data or a change in data distribution can cause the optimizer to recalculate its strategy, leading it to swap a high-performing index seek for a sluggish full table scan. This is called a plan regression.
Even if your indexes are perfectly tuned, the database might suddenly decide that a different approach is "cheaper" based on skewed or outdated statistics. Plan stability is the practice of ensuring that the database uses the exact same execution plan that you know performs well, regardless of what the statistics say.
How Plan Freezing Works
Plan freezing (often called "Plan Pinning" or "Query Store hints" depending on your specific database system) acts as a mandate to the optimizer. You are essentially telling the engine: "Ignore your current cost calculations; always use this specific internal ID for this query."
Imagine you have a query that suddenly starts taking 10 seconds instead of 10 milliseconds. You identify the original, fast execution plan, and you "freeze" it.
-- Conceptual example of locking a plan
EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 105;
Once this is active, the optimizer will skip its internal cost-benefit analysis for that query. Even if you add millions of rows or update your histograms, the database will bypass the re-calculation phase and execute the query using the exact steps associated with plan_id = 105.
This creates a safety net. It allows you to maintain consistent performance during volatile data shifts while you take the time to investigate why the optimizer's estimations drifted in the first place. You aren't just relying on statistics anymore; you are relying on a verified, successful path of execution.
Key Takeaway: Plan freezing prevents performance regression by forcing the database to ignore fluctuating statistics and stick to a proven, high-performing execution plan.