Database Management SystemsQuery Compilation

Query Compilation

LevelAdvanced

Duration75 mins

TopicQuery Compilation

4 / 5

Plan Reuse

The Art of Knowing When to Reuse

We've established that query compilation and caching eliminate redundant work. But a fundamental tension lurks beneath the surface: plans optimized for one set of parameter values may perform terribly for others.

Consider a query filtering on user status:

When status = 'active' (95% of users): Full table scan may be optimal
When status = 'suspended' (0.1% of users): Index seek is clearly better

If the database caches the plan from the first execution and blindly reuses it, performance disasters follow. This is the plan reuse problem—and solving it requires sophisticated strategies that balance compilation cost against execution efficiency.

Plan reuse encompasses the decisions databases make about when a cached plan is suitable, how plans can be generalized across parameter variations, and what mechanisms detect and correct suboptimal plan choices.

What You Will Learn

By the end of this page, you will understand the spectrum from custom plans to generic plans, the parameter sniffing problem and its solutions, adaptive query optimization techniques, and how modern databases dynamically adjust plan reuse strategies.

The Plan Reuse Spectrum

Plan reuse exists on a spectrum from always custom to always generic, with various hybrid approaches in between.

Custom Plans (No Reuse)

Every execution triggers fresh optimization using actual parameter values. The optimizer "sees" the concrete values and can make optimal access path decisions.

Pros: Optimal plan for each execution Cons: High optimization overhead; not scalable for OLTP

Generic Plans (Full Reuse)

One plan is generated without knowledge of parameter values and reused for all executions. The optimizer must make assumptions about parameter distributions.

Pros: Zero optimization overhead after first execution Cons: Plan may be suboptimal for specific parameter values

Hybrid/Adaptive (Conditional Reuse)

Database starts with one approach and adapts based on observed behavior. May maintain multiple plans for different parameter ranges.

Plan Reuse Strategy Comparison
Strategy	Optimization Cost	Plan Quality	Best For	Challenges
Always Custom	High (every execution)	Optimal	Complex OLAP, rare queries	OLTP scalability
Always Generic	Low (once)	May be suboptimal	Simple OLTP, uniform data	Skewed distributions
Threshold-Based	Medium	Good average	Mixed workloads	Tuning thresholds
Adaptive	Variable	Improves over time	Unknown/changing workloads	Complexity, overhead
Multiple Plans	High initial	Near-optimal	Highly skewed data	Memory, plan selection

The OLTP vs. OLAP Divide

OLTP workloads (thousands of simple queries/second) favor aggressive plan reuse—optimization cost dominates. OLAP workloads (few complex queries running minutes/hours) favor fresh optimization—plan quality dominates. Modern systems often auto-detect workload type and adjust strategy accordingly.

Parameter Sniffing: The Double-Edged Sword

Parameter sniffing is the database technique of examining actual parameter values during query compilation to generate an optimized plan. It's simultaneously one of the most powerful optimization techniques and one of the most common causes of performance problems.

How it works:

Prepared statement is executed for the first time
Optimizer examines the provided parameter values
Using data statistics, optimizer estimates result sizes and costs
Plan is generated based on these estimates
Plan is cached and reused for subsequent executions

The problem scenario:

-- First execution: popular product (1 million orders)
EXECUTE get_orders_by_product @product_id = 1;
-- Optimizer sees product_id=1 → 1M rows → chooses table scan
-- Plan cached: TABLE SCAN

-- Second execution: rare product (10 orders)  
EXECUTE get_orders_by_product @product_id = 99999;
-- Reuses cached TABLE SCAN plan
-- Scans 10 million rows to find 10 results (terrible!)
-- Should have used INDEX SEEK

parameter_sniffing_demo.sql
SQL Server
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- SQL Server: Demonstrating Parameter Sniffing Problem
 
-- Setup: Orders table with highly skewed distribution
-- Product 1: 1,000,000 orders (popular)
-- Products 2-100000: ~10 orders each (rare)
 
-- Create procedure
CREATE PROCEDURE GetOrdersByProduct
    @ProductID INT
AS
BEGIN
    SELECT OrderID, CustomerID, OrderDate, Amount
    FROM Orders
    WHERE ProductID = @ProductID;
END;
GO
 
-- First execution with popular product
-- Optimizer "sniffs" @ProductID = 1, estimates 1M rows
-- Chooses Table Scan (correct for 1M rows)
EXEC GetOrdersByProduct @ProductID = 1;
-- Plan cached: Clustered Index Scan (Table Scan)
 
-- Subsequent execution with rare product
-- Reuses cached Table Scan
-- BAD: Scans 10M rows to find 10 results!
EXEC GetOrdersByProduct @ProductID = 99999;
 
-- Check the cached plan
SELECT 
    cp.usecounts,
    cp.objtype,
    st.text,
    qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.text LIKE '%GetOrdersByProduct%'
    AND cp.objtype = 'Proc';
 
-- The plan shows Table Scan being used for ALL executions
-- even when Index Seek would be orders of magnitude faster

When Sniffing Helps

•First execution is representative of typical usage
•Data distribution is relatively uniform
•Plan choice doesn't vary much across values
•Query is simple with clear access paths
•Workload is consistent over time

When Sniffing Hurts

•First execution is outlier (rare value)
•Highly skewed data distribution
•Optimal plan varies significantly by parameter
•After service restart (random first query)
•During testing with non-production data

Solutions to Parameter Sniffing Problems

Various techniques mitigate parameter sniffing problems, each with trade-offs:

1. RECOMPILE Hints

Force re-optimization on every execution. Sacrifices plan reuse for plan quality.

2. OPTIMIZE FOR Hints

Tell optimizer to optimize for a specific "typical" value or for unknown (average) values.

3. Plan Guides

Database-level rules that force specific plans for matching queries.

4. Query Variations

Different stored procedures or query variants for different parameter ranges.

5. Disable Sniffing

Use local variables to hide parameter values from optimizer.

6. Adaptive Query Processing

Let the database automatically adjust based on runtime feedback.

sniffing_solutions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
-- Solutions to Parameter Sniffing in SQL Server
 
-- ============================================
-- Solution 1: RECOMPILE Hint (per execution)
-- ============================================
CREATE PROCEDURE GetOrdersByProduct_Recompile
    @ProductID INT
AS
BEGIN
    SELECT OrderID, CustomerID, OrderDate, Amount
    FROM Orders
    WHERE ProductID = @ProductID
    OPTION (RECOMPILE);  -- Fresh plan every time
END;
 
-- Pros: Always optimal plan
-- Cons: Compilation overhead on every call
 
 
-- ============================================
-- Solution 2: OPTIMIZE FOR Hint
-- ============================================
-- Option A: Optimize for specific "typical" value
CREATE PROCEDURE GetOrdersByProduct_OptimizeFor
    @ProductID INT
AS
BEGIN
    SELECT OrderID, CustomerID, OrderDate, Amount
    FROM Orders
    WHERE ProductID = @ProductID
    OPTION (OPTIMIZE FOR (@ProductID = 50000)); -- Mid-range product
END;
 
-- Option B: Optimize for "unknown" (uses average statistics)
CREATE PROCEDURE GetOrdersByProduct_Unknown
    @ProductID INT
AS
BEGIN
    SELECT OrderID, CustomerID, OrderDate, Amount
    FROM Orders
    WHERE ProductID = @ProductID
    OPTION (OPTIMIZE FOR (@ProductID UNKNOWN));
END;
 
-- Pros: Single plan that's "good enough" for most cases
-- Cons: Still suboptimal for outliers
 
 
-- ============================================
-- Solution 3: Local Variable (Disable Sniffing)
-- ============================================
CREATE PROCEDURE GetOrdersByProduct_NoSniff
    @ProductID INT
AS
BEGIN
    DECLARE @LocalProductID INT = @ProductID;
    
    -- Optimizer can't "see" the actual value
    SELECT OrderID, CustomerID, OrderDate, Amount
    FROM Orders
    WHERE ProductID = @LocalProductID;
END;
 
-- Pros: Simple technique; uses average cardinality
-- Cons: Never gets optimal plan for specific values
 
 
-- ============================================
-- Solution 4: Multiple Procedures by Data Range
-- ============================================
CREATE PROCEDURE GetOrdersByProduct_Smart
    @ProductID INT
AS
BEGIN
    DECLARE @OrderCount INT;
    
    -- Get cardinality estimate from statistics
    SELECT @OrderCount = 
        (SELECT SUM(rows) 
         FROM sys.dm_db_partition_stats 
         WHERE object_id = OBJECT_ID('Orders')
           AND index_id IN (0, 1));
    
    -- Route to appropriate implementation
    IF @OrderCount > 10000
        EXEC GetOrdersByProduct_TableScan @ProductID;
    ELSE
        EXEC GetOrdersByProduct_IndexSeek @ProductID;
END;
 
-- Pros: Optimal plan for each scenario
-- Cons: Complex; maintenance overhead; threshold tuning

Adaptive Query Processing

Modern databases increasingly use adaptive query processing—techniques that allow execution to adjust based on runtime feedback rather than solely on compile-time estimates.

Key adaptive techniques:

1. Adaptive Joins

Choose join algorithm (hash vs. nested loop vs. merge) at runtime based on actual row counts, not estimates.

2. Adaptive Memory Grants

Adjust memory allocation during execution if initial estimates were wrong.

3. Interleaved Execution

Pause execution after materializing intermediate results, re-optimize remaining plan with actual cardinalities.

4. Adaptive Statistics

Update histogram estimates based on observed query results.

5. Plan Correction

Detect plan regressions and automatically switch to better alternatives.

adaptive_processing.sql
SQL Server
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
-- SQL Server Intelligent Query Processing Features
-- (Available in SQL Server 2017+ and Azure SQL Database)
 
-- ============================================
-- Adaptive Joins
-- ============================================
-- The optimizer defers join type decision until runtime
-- Based on actual rows from first input, chooses:
-- - Hash Join for large row counts
-- - Nested Loop for small row counts
 
-- Example query that benefits:
SELECT o.OrderID, c.CompanyName, p.ProductName
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID  
JOIN Products p ON o.ProductID = p.ProductID
WHERE o.OrderDate > @StartDate;
 
-- For @StartDate = '2024-01-01' → many rows → Hash Join
-- For @StartDate = '2025-01-14' → few rows → Nested Loop
-- Adaptive Joins choose automatically at runtime!
 
-- Check if adaptive join was used
SELECT 
    qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE qp.query_plan.exist('//RelOp[@AdaptiveJoin="true"]') = 1;
 
 
-- ============================================
-- Memory Grant Feedback  
-- ============================================
-- Initial execution may get memory grant wrong
-- Subsequent executions adjust based on actual usage
 
-- Query with potentially large memory requirements
SELECT o.OrderDate, SUM(od.Quantity * od.UnitPrice) as Total
FROM Orders o
JOIN OrderDetails od ON o.OrderID = od.OrderID
GROUP BY o.OrderDate
ORDER BY o.OrderDate;
 
-- First execution: estimated memory grant (may be too high/low)
-- Second execution: adjusted based on actual usage
-- This feedback persists across executions
 
-- View memory grant feedback
SELECT 
    qt.query_sql_text,
    rs.avg_query_max_used_memory,
    rs.last_query_max_used_memory,
    p.is_memory_grant_feedback_adjusted,
    p.memory_grant_feedback_reason
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE p.is_memory_grant_feedback_adjusted = 1;
 
 
-- ============================================
-- Batch Mode Adaptive Memory Grants (2019+)
-- ============================================
-- Works in batch mode (columnstore) processing
-- Adjusts memory in real-time during execution
 
ALTER DATABASE SCOPED CONFIGURATION 
    SET BATCH_MODE_ADAPTIVE_JOINS = ON;
 
ALTER DATABASE SCOPED CONFIGURATION 
    SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

Adaptive Cursor Sharing in Oracle

Oracle's Adaptive Cursor Sharing (ACS) automatically detects bind-sensitive queries and maintains multiple execution plans for different bind value ranges. When a query is marked bind-sensitive, Oracle monitors execution and generates new plans when cardinality differs significantly. This is tracked in V$SQL with columns IS_BIND_SENSITIVE and IS_BIND_AWARE.

Multi-Plan Approaches

Instead of forcing a single plan to serve all parameter values, some systems maintain multiple plans for the same query, selecting the appropriate one at runtime.

Strategies for multi-plan management:

1. Range-Based Plan Selection

Partition parameter space into ranges; maintain separate plan for each range.

2. Cardinality-Based Selection

Store plans indexed by estimated result cardinality; select plan matching runtime estimate.

3. Dynamic Plan Switching

Monitor execution metrics; switch plans when performance degrades beyond threshold.

4. Query Store Plan Forcing

Manually or automatically force specific plans for specific parameter patterns.

multi_plan_implementation.py
Python
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
# Conceptual Multi-Plan Management System
 
from dataclasses import dataclass
from typing import Dict, List, Optional, Tuple
from enum import Enum
 
@dataclass
class ExecutionStats:
    """Statistics from plan execution."""
    actual_rows: int
    execution_time_ms: float
    logical_reads: int
    physical_reads: int
 
@dataclass
class CachedPlan:
    """A cached execution plan with its applicability metadata."""
    plan_id: str
    plan: object  # Compiled plan
    cardinality_range: Tuple[int, int]  # (min_rows, max_rows)
    total_executions: int = 0
    total_time_ms: float = 0.0
    avg_time_ms: float = 0.0
    
    def update_stats(self, stats: ExecutionStats):
        self.total_executions += 1
        self.total_time_ms += stats.execution_time_ms
        self.avg_time_ms = self.total_time_ms / self.total_executions
 
 
class PlanSelector:
    """Selects optimal plan based on estimated cardinality."""
    
    def __init__(self, query_hash: str):
        self.query_hash = query_hash
        self.plans: List[CachedPlan] = []
        self.default_plan: Optional[CachedPlan] = None
        
        # Regression detection
        self.baseline_time_ms: Optional[float] = None
        self.regression_threshold = 2.0  # 2x slower = regression
    
    def add_plan(
        self, 
        plan: object, 
        cardinality_range: Tuple[int, int]
    ) -> CachedPlan:
        """Add a new plan variant for a cardinality range."""
        cached = CachedPlan(
            plan_id=f"{self.query_hash}_{len(self.plans)}",
            plan=plan,
            cardinality_range=cardinality_range
        )
        self.plans.append(cached)
        
        if self.default_plan is None:
            self.default_plan = cached
        
        return cached
    
    def select_plan(self, estimated_cardinality: int) -> CachedPlan:
        """Select best plan for given cardinality estimate."""
        
        # Find plan whose range contains the estimate
        for plan in self.plans:
            min_rows, max_rows = plan.cardinality_range
            if min_rows <= estimated_cardinality <= max_rows:
                return plan
        
        # No matching range: use default or closest
        if self.default_plan:
            return self.default_plan
        
        # Find closest range
        return min(
            self.plans,
            key=lambda p: min(
                abs(p.cardinality_range[0] - estimated_cardinality),
                abs(p.cardinality_range[1] - estimated_cardinality)
            )
        )
    
    def record_execution(
        self, 
        plan: CachedPlan, 
        stats: ExecutionStats
    ):
        """Record execution statistics; detect regression."""
        old_avg = plan.avg_time_ms if plan.total_executions > 0 else None
        plan.update_stats(stats)
        
        # Update baseline if this is best seen
        if (self.baseline_time_ms is None or 
            stats.execution_time_ms < self.baseline_time_ms):
            self.baseline_time_ms = stats.execution_time_ms
        
        # Detect regression
        if self.baseline_time_ms and stats.execution_time_ms > (
            self.baseline_time_ms * self.regression_threshold
        ):
            self._handle_regression(plan, stats)
    
    def _handle_regression(
        self, 
        plan: CachedPlan, 
        stats: ExecutionStats
    ):
        """Handle detected plan regression."""
        print(f"Regression detected for {plan.plan_id}")
        print(f"Expected: ~{self.baseline_time_ms:.2f}ms")
        print(f"Actual: {stats.execution_time_ms:.2f}ms")
        
        # Options:
        # 1. Trigger recompilation for this cardinality
        # 2. Fall back to different plan
        # 3. Alert monitoring system
        # 4. Mark plan for review
 
 
# Example usage
selector = PlanSelector("query_abc123")
 
# Add plans for different cardinality ranges
selector.add_plan(index_seek_plan, (0, 1000))       # Small results: use index
selector.add_plan(table_scan_plan, (10001, 999999999))  # Large results: use scan
selector.add_plan(mixed_plan, (1001, 10000))        # Medium: hybrid approach
 
# At runtime, select plan based on estimated rows
estimated_rows = estimate_cardinality(parameter_values)
plan = selector.select_plan(estimated_rows)
result, stats = execute_plan(plan.plan, parameter_values)
selector.record_execution(plan, stats)

Multi-Plan Management Trade-offs
Approach	Memory Cost	Selection Overhead	Optimal Hit Rate	Complexity
Single Generic Plan	Low	None	Variable	Low
2-3 Range Plans	Moderate	Low	High	Moderate
N Cardinality Plans	High	Moderate	Very High	High
Per-Value Plans	Very High	High (hash lookup)	Optimal	Very High

Plan Regression Detection

Plan regression occurs when a query suddenly uses a less efficient plan—often after a database restart, statistics update, or schema change. Detecting and correcting regressions is crucial for maintaining stable performance.

Common causes of regression:

Statistics staleness — Optimizer makes decisions based on outdated data distribution
Parameter sniffing flip — New first-execution parameter produces bad plan
Cardinality estimation errors — Complex predicates or correlated columns
Schema changes — Index additions/removals change optimal access paths
Upgrade changes — New optimizer version chooses differently

Detection strategies:

Query Store / Workload Repository — Track historical plan performance
Real-time monitoring — Alert on execution time spikes
A/B comparison — Compare new plan against known-good baseline
Automatic reversion — Roll back to previous plan on detected regression

regression_detection.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
-- SQL Server Query Store: Plan Regression Detection
 
-- Enable Query Store
ALTER DATABASE YourDatabase
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    QUERY_CAPTURE_MODE = ALL
);
 
-- Find queries with multiple plans where newer plan is worse
WITH PlanStats AS (
    SELECT 
        q.query_id,
        p.plan_id,
        p.is_forced_plan,
        rs.first_execution_time,
        rs.last_execution_time,
        rs.count_executions,
        rs.avg_duration / 1000000.0 as avg_duration_sec,
        rs.avg_cpu_time / 1000000.0 as avg_cpu_sec,
        rs.avg_logical_io_reads
    FROM sys.query_store_query q
    JOIN sys.query_store_plan p ON q.query_id = p.query_id
    JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
    WHERE rs.count_executions >= 10  -- Minimum executions
)
SELECT 
    newer.query_id,
    qt.query_sql_text,
    older.plan_id as good_plan_id,
    older.avg_duration_sec as good_plan_duration,
    newer.plan_id as bad_plan_id,
    newer.avg_duration_sec as bad_plan_duration,
    (newer.avg_duration_sec / NULLIF(older.avg_duration_sec, 0)) 
        as regression_factor
FROM PlanStats older
JOIN PlanStats newer ON older.query_id = newer.query_id 
    AND older.plan_id < newer.plan_id
JOIN sys.query_store_query q ON older.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE newer.avg_duration_sec > older.avg_duration_sec * 2  -- 2x regression
    AND newer.last_execution_time > older.last_execution_time
ORDER BY regression_factor DESC;
 
-- Force a known-good plan (automatic regression fix)
EXEC sp_query_store_force_plan 
    @query_id = 12345, 
    @plan_id = 67;
 
-- Check forced plans
SELECT 
    q.query_id,
    p.plan_id,
    p.is_forced_plan,
    p.force_failure_count,
    qt.query_sql_text
FROM sys.query_store_plan p
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE p.is_forced_plan = 1;
 
-- Unforce plan
EXEC sp_query_store_unforce_plan 
    @query_id = 12345, 
    @plan_id = 67;
 
-- Enable automatic plan correction (SQL Server 2017+)
ALTER DATABASE YourDatabase
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

Best Practices for Plan Reuse

Effective plan reuse requires understanding your workload and applying appropriate strategies. Here are battle-tested practices from production environments.

Plan Reuse Best Practices

•Keep statistics fresh — Stale statistics are the #1 cause of bad plans. Configure auto-analyze/auto-update and supplement with manual ANALYZE for critical tables after bulk operations.
•Use parameterized queries consistently — Avoid ad-hoc SQL with literals. Inconsistent parameterization fragments the plan cache.
•Monitor for parameter sniffing issues — Track queries with high variance in execution time. These are candidates for RECOMPILE or OPTIMIZE FOR hints.
•Leverage Query Store / Workload Repository — Enable historical plan tracking. This provides invaluable data for regression detection and performance analysis.
•Test with production-like data — Plan choices in dev with 1000 rows differ dramatically from production with 100 million rows.
•Consider plan stability features — SQL Server's Automatic Plan Correction, Oracle's SQL Plan Management, PostgreSQL's pg_hint_plan can stabilize critical queries.
•Size plan cache appropriately — Too small causes frequent eviction and re-compilation. Monitor cache hit rates and eviction counts.
•Document critical query plans — For business-critical queries, capture baseline plans and set up monitoring for deviations.

Plan Reuse Decision Matrix
Query Characteristic	Recommended Strategy	Rationale
Simple, uniform data	Generic plan, full reuse	Plan quality varies little by parameter
Highly skewed data	OPTIMIZE FOR UNKNOWN or adaptive	Avoid worst-case sniffing
Critical business query	Plan guide / forced plan	Stability over optimality
Ad-hoc reporting	Custom plans (RECOMPILE)	Each query is different
High-frequency OLTP	Aggressive caching	Minimize optimization overhead
Complex joins	Adaptive joins + monitoring	Runtime conditions vary
Range predicates	Consider multiple plans	Selectivity varies dramatically

Don't Over-Optimize

Most queries don't need special plan reuse handling. Start with default behavior, instrument monitoring, and intervene only for queries demonstrating problems. Premature optimization of plan reuse strategies can add complexity without benefit.

Summary: Plan Reuse

We've explored the nuanced world of plan reuse—from the tension between optimization cost and plan quality, through parameter sniffing challenges, to modern adaptive techniques. Let's consolidate the key insights:

Key Takeaways

•Plan reuse trades optimization cost for plan quality — Generic plans avoid compilation but may be suboptimal; custom plans are optimal but expensive.
•Parameter sniffing is a double-edged sword — Beneficial when first execution is typical; harmful when it's an outlier.
•Multiple solutions exist — RECOMPILE, OPTIMIZE FOR hints, plan guides, local variables, and adaptive processing each address different scenarios.
•Adaptive query processing is the modern solution — Runtime feedback allows databases to self-correct without manual intervention.
•Multi-plan approaches handle skew — Maintaining multiple plans for cardinality ranges provides near-optimal execution across diverse parameters.
•Regression detection is crucial — Query Store and similar tools catch when plans go bad, enabling quick correction.
•Fresh statistics are foundational — Most plan problems trace back to stale statistics; keep them current.

What's next:

We've explored query compilation, prepared statements, caching, and plan reuse. The final page brings everything together by examining the performance benefits of these techniques—quantifying improvements, understanding when each approach is most valuable, and establishing guidelines for production deployment.

Page Complete

You now understand the complexities of plan reuse, from the plan reuse spectrum through parameter sniffing challenges to adaptive query processing. You can analyze plan reuse problems and apply appropriate mitigation strategies. Next, we'll conclude with a comprehensive look at performance benefits.

4 / 5

Loading learning content...

Database Management SystemsQuery Compilation

Query Compilation

LevelAdvanced

Duration75 mins

TopicQuery Compilation

4 / 5

Plan Reuse

The Art of Knowing When to Reuse

Consider a query filtering on user status:

When status = 'active' (95% of users): Full table scan may be optimal
When status = 'suspended' (0.1% of users): Index seek is clearly better

What You Will Learn

The Plan Reuse Spectrum

Plan reuse exists on a spectrum from always custom to always generic, with various hybrid approaches in between.

Custom Plans (No Reuse)

Every execution triggers fresh optimization using actual parameter values. The optimizer "sees" the concrete values and can make optimal access path decisions.

Pros: Optimal plan for each execution Cons: High optimization overhead; not scalable for OLTP

Generic Plans (Full Reuse)

One plan is generated without knowledge of parameter values and reused for all executions. The optimizer must make assumptions about parameter distributions.

Pros: Zero optimization overhead after first execution Cons: Plan may be suboptimal for specific parameter values

Hybrid/Adaptive (Conditional Reuse)

Database starts with one approach and adapts based on observed behavior. May maintain multiple plans for different parameter ranges.

Plan Reuse Strategy Comparison
Strategy	Optimization Cost	Plan Quality	Best For	Challenges
Always Custom	High (every execution)	Optimal	Complex OLAP, rare queries	OLTP scalability
Always Generic	Low (once)	May be suboptimal	Simple OLTP, uniform data	Skewed distributions
Threshold-Based	Medium	Good average	Mixed workloads	Tuning thresholds
Adaptive	Variable	Improves over time	Unknown/changing workloads	Complexity, overhead
Multiple Plans	High initial	Near-optimal	Highly skewed data	Memory, plan selection

The OLTP vs. OLAP Divide

Parameter Sniffing: The Double-Edged Sword

How it works:

Prepared statement is executed for the first time
Optimizer examines the provided parameter values
Using data statistics, optimizer estimates result sizes and costs
Plan is generated based on these estimates
Plan is cached and reused for subsequent executions

The problem scenario:

-- First execution: popular product (1 million orders)
EXECUTE get_orders_by_product @product_id = 1;
-- Optimizer sees product_id=1 → 1M rows → chooses table scan
-- Plan cached: TABLE SCAN

-- Second execution: rare product (10 orders)  
EXECUTE get_orders_by_product @product_id = 99999;
-- Reuses cached TABLE SCAN plan
-- Scans 10 million rows to find 10 results (terrible!)
-- Should have used INDEX SEEK

parameter_sniffing_demo.sql
SQL Server
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- SQL Server: Demonstrating Parameter Sniffing Problem
 
-- Setup: Orders table with highly skewed distribution
-- Product 1: 1,000,000 orders (popular)
-- Products 2-100000: ~10 orders each (rare)
 
-- Create procedure
CREATE PROCEDURE GetOrdersByProduct
    @ProductID INT
AS
BEGIN
    SELECT OrderID, CustomerID, OrderDate, Amount
    FROM Orders
    WHERE ProductID = @ProductID;
END;
GO
 
-- First execution with popular product
-- Optimizer "sniffs" @ProductID = 1, estimates 1M rows
-- Chooses Table Scan (correct for 1M rows)
EXEC GetOrdersByProduct @ProductID = 1;
-- Plan cached: Clustered Index Scan (Table Scan)
 
-- Subsequent execution with rare product
-- Reuses cached Table Scan
-- BAD: Scans 10M rows to find 10 results!
EXEC GetOrdersByProduct @ProductID = 99999;
 
-- Check the cached plan
SELECT 
    cp.usecounts,
    cp.objtype,
    st.text,
    qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.text LIKE '%GetOrdersByProduct%'
    AND cp.objtype = 'Proc';
 
-- The plan shows Table Scan being used for ALL executions
-- even when Index Seek would be orders of magnitude faster

When Sniffing Helps

•First execution is representative of typical usage
•Data distribution is relatively uniform
•Plan choice doesn't vary much across values
•Query is simple with clear access paths
•Workload is consistent over time

When Sniffing Hurts

•First execution is outlier (rare value)
•Highly skewed data distribution
•Optimal plan varies significantly by parameter
•After service restart (random first query)
•During testing with non-production data

Solutions to Parameter Sniffing Problems

Various techniques mitigate parameter sniffing problems, each with trade-offs:

1. RECOMPILE Hints

Force re-optimization on every execution. Sacrifices plan reuse for plan quality.

2. OPTIMIZE FOR Hints

Tell optimizer to optimize for a specific "typical" value or for unknown (average) values.

3. Plan Guides

Database-level rules that force specific plans for matching queries.

4. Query Variations

Different stored procedures or query variants for different parameter ranges.

5. Disable Sniffing

Use local variables to hide parameter values from optimizer.

6. Adaptive Query Processing

Let the database automatically adjust based on runtime feedback.

sniffing_solutions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
-- Solutions to Parameter Sniffing in SQL Server
 
-- ============================================
-- Solution 1: RECOMPILE Hint (per execution)
-- ============================================
CREATE PROCEDURE GetOrdersByProduct_Recompile
    @ProductID INT
AS
BEGIN
    SELECT OrderID, CustomerID, OrderDate, Amount
    FROM Orders
    WHERE ProductID = @ProductID
    OPTION (RECOMPILE);  -- Fresh plan every time
END;
 
-- Pros: Always optimal plan
-- Cons: Compilation overhead on every call
 
 
-- ============================================
-- Solution 2: OPTIMIZE FOR Hint
-- ============================================
-- Option A: Optimize for specific "typical" value
CREATE PROCEDURE GetOrdersByProduct_OptimizeFor
    @ProductID INT
AS
BEGIN
    SELECT OrderID, CustomerID, OrderDate, Amount
    FROM Orders
    WHERE ProductID = @ProductID
    OPTION (OPTIMIZE FOR (@ProductID = 50000)); -- Mid-range product
END;
 
-- Option B: Optimize for "unknown" (uses average statistics)
CREATE PROCEDURE GetOrdersByProduct_Unknown
    @ProductID INT
AS
BEGIN
    SELECT OrderID, CustomerID, OrderDate, Amount
    FROM Orders
    WHERE ProductID = @ProductID
    OPTION (OPTIMIZE FOR (@ProductID UNKNOWN));
END;
 
-- Pros: Single plan that's "good enough" for most cases
-- Cons: Still suboptimal for outliers
 
 
-- ============================================
-- Solution 3: Local Variable (Disable Sniffing)
-- ============================================
CREATE PROCEDURE GetOrdersByProduct_NoSniff
    @ProductID INT
AS
BEGIN
    DECLARE @LocalProductID INT = @ProductID;
    
    -- Optimizer can't "see" the actual value
    SELECT OrderID, CustomerID, OrderDate, Amount
    FROM Orders
    WHERE ProductID = @LocalProductID;
END;
 
-- Pros: Simple technique; uses average cardinality
-- Cons: Never gets optimal plan for specific values
 
 
-- ============================================
-- Solution 4: Multiple Procedures by Data Range
-- ============================================
CREATE PROCEDURE GetOrdersByProduct_Smart
    @ProductID INT
AS
BEGIN
    DECLARE @OrderCount INT;
    
    -- Get cardinality estimate from statistics
    SELECT @OrderCount = 
        (SELECT SUM(rows) 
         FROM sys.dm_db_partition_stats 
         WHERE object_id = OBJECT_ID('Orders')
           AND index_id IN (0, 1));
    
    -- Route to appropriate implementation
    IF @OrderCount > 10000
        EXEC GetOrdersByProduct_TableScan @ProductID;
    ELSE
        EXEC GetOrdersByProduct_IndexSeek @ProductID;
END;
 
-- Pros: Optimal plan for each scenario
-- Cons: Complex; maintenance overhead; threshold tuning

Adaptive Query Processing

Modern databases increasingly use adaptive query processing—techniques that allow execution to adjust based on runtime feedback rather than solely on compile-time estimates.

Key adaptive techniques:

1. Adaptive Joins

Choose join algorithm (hash vs. nested loop vs. merge) at runtime based on actual row counts, not estimates.

2. Adaptive Memory Grants

Adjust memory allocation during execution if initial estimates were wrong.

3. Interleaved Execution

Pause execution after materializing intermediate results, re-optimize remaining plan with actual cardinalities.

4. Adaptive Statistics

Update histogram estimates based on observed query results.

5. Plan Correction

Detect plan regressions and automatically switch to better alternatives.

adaptive_processing.sql
SQL Server
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
-- SQL Server Intelligent Query Processing Features
-- (Available in SQL Server 2017+ and Azure SQL Database)
 
-- ============================================
-- Adaptive Joins
-- ============================================
-- The optimizer defers join type decision until runtime
-- Based on actual rows from first input, chooses:
-- - Hash Join for large row counts
-- - Nested Loop for small row counts
 
-- Example query that benefits:
SELECT o.OrderID, c.CompanyName, p.ProductName
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID  
JOIN Products p ON o.ProductID = p.ProductID
WHERE o.OrderDate > @StartDate;
 
-- For @StartDate = '2024-01-01' → many rows → Hash Join
-- For @StartDate = '2025-01-14' → few rows → Nested Loop
-- Adaptive Joins choose automatically at runtime!
 
-- Check if adaptive join was used
SELECT 
    qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE qp.query_plan.exist('//RelOp[@AdaptiveJoin="true"]') = 1;
 
 
-- ============================================
-- Memory Grant Feedback  
-- ============================================
-- Initial execution may get memory grant wrong
-- Subsequent executions adjust based on actual usage
 
-- Query with potentially large memory requirements
SELECT o.OrderDate, SUM(od.Quantity * od.UnitPrice) as Total
FROM Orders o
JOIN OrderDetails od ON o.OrderID = od.OrderID
GROUP BY o.OrderDate
ORDER BY o.OrderDate;
 
-- First execution: estimated memory grant (may be too high/low)
-- Second execution: adjusted based on actual usage
-- This feedback persists across executions
 
-- View memory grant feedback
SELECT 
    qt.query_sql_text,
    rs.avg_query_max_used_memory,
    rs.last_query_max_used_memory,
    p.is_memory_grant_feedback_adjusted,
    p.memory_grant_feedback_reason
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE p.is_memory_grant_feedback_adjusted = 1;
 
 
-- ============================================
-- Batch Mode Adaptive Memory Grants (2019+)
-- ============================================
-- Works in batch mode (columnstore) processing
-- Adjusts memory in real-time during execution
 
ALTER DATABASE SCOPED CONFIGURATION 
    SET BATCH_MODE_ADAPTIVE_JOINS = ON;
 
ALTER DATABASE SCOPED CONFIGURATION 
    SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

Adaptive Cursor Sharing in Oracle

Multi-Plan Approaches

Instead of forcing a single plan to serve all parameter values, some systems maintain multiple plans for the same query, selecting the appropriate one at runtime.

Strategies for multi-plan management:

1. Range-Based Plan Selection

Partition parameter space into ranges; maintain separate plan for each range.

2. Cardinality-Based Selection

Store plans indexed by estimated result cardinality; select plan matching runtime estimate.

3. Dynamic Plan Switching

Monitor execution metrics; switch plans when performance degrades beyond threshold.

4. Query Store Plan Forcing

Manually or automatically force specific plans for specific parameter patterns.

multi_plan_implementation.py
Python
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
# Conceptual Multi-Plan Management System
 
from dataclasses import dataclass
from typing import Dict, List, Optional, Tuple
from enum import Enum
 
@dataclass
class ExecutionStats:
    """Statistics from plan execution."""
    actual_rows: int
    execution_time_ms: float
    logical_reads: int
    physical_reads: int
 
@dataclass
class CachedPlan:
    """A cached execution plan with its applicability metadata."""
    plan_id: str
    plan: object  # Compiled plan
    cardinality_range: Tuple[int, int]  # (min_rows, max_rows)
    total_executions: int = 0
    total_time_ms: float = 0.0
    avg_time_ms: float = 0.0
    
    def update_stats(self, stats: ExecutionStats):
        self.total_executions += 1
        self.total_time_ms += stats.execution_time_ms
        self.avg_time_ms = self.total_time_ms / self.total_executions
 
 
class PlanSelector:
    """Selects optimal plan based on estimated cardinality."""
    
    def __init__(self, query_hash: str):
        self.query_hash = query_hash
        self.plans: List[CachedPlan] = []
        self.default_plan: Optional[CachedPlan] = None
        
        # Regression detection
        self.baseline_time_ms: Optional[float] = None
        self.regression_threshold = 2.0  # 2x slower = regression
    
    def add_plan(
        self, 
        plan: object, 
        cardinality_range: Tuple[int, int]
    ) -> CachedPlan:
        """Add a new plan variant for a cardinality range."""
        cached = CachedPlan(
            plan_id=f"{self.query_hash}_{len(self.plans)}",
            plan=plan,
            cardinality_range=cardinality_range
        )
        self.plans.append(cached)
        
        if self.default_plan is None:
            self.default_plan = cached
        
        return cached
    
    def select_plan(self, estimated_cardinality: int) -> CachedPlan:
        """Select best plan for given cardinality estimate."""
        
        # Find plan whose range contains the estimate
        for plan in self.plans:
            min_rows, max_rows = plan.cardinality_range
            if min_rows <= estimated_cardinality <= max_rows:
                return plan
        
        # No matching range: use default or closest
        if self.default_plan:
            return self.default_plan
        
        # Find closest range
        return min(
            self.plans,
            key=lambda p: min(
                abs(p.cardinality_range[0] - estimated_cardinality),
                abs(p.cardinality_range[1] - estimated_cardinality)
            )
        )
    
    def record_execution(
        self, 
        plan: CachedPlan, 
        stats: ExecutionStats
    ):
        """Record execution statistics; detect regression."""
        old_avg = plan.avg_time_ms if plan.total_executions > 0 else None
        plan.update_stats(stats)
        
        # Update baseline if this is best seen
        if (self.baseline_time_ms is None or 
            stats.execution_time_ms < self.baseline_time_ms):
            self.baseline_time_ms = stats.execution_time_ms
        
        # Detect regression
        if self.baseline_time_ms and stats.execution_time_ms > (
            self.baseline_time_ms * self.regression_threshold
        ):
            self._handle_regression(plan, stats)
    
    def _handle_regression(
        self, 
        plan: CachedPlan, 
        stats: ExecutionStats
    ):
        """Handle detected plan regression."""
        print(f"Regression detected for {plan.plan_id}")
        print(f"Expected: ~{self.baseline_time_ms:.2f}ms")
        print(f"Actual: {stats.execution_time_ms:.2f}ms")
        
        # Options:
        # 1. Trigger recompilation for this cardinality
        # 2. Fall back to different plan
        # 3. Alert monitoring system
        # 4. Mark plan for review
 
 
# Example usage
selector = PlanSelector("query_abc123")
 
# Add plans for different cardinality ranges
selector.add_plan(index_seek_plan, (0, 1000))       # Small results: use index
selector.add_plan(table_scan_plan, (10001, 999999999))  # Large results: use scan
selector.add_plan(mixed_plan, (1001, 10000))        # Medium: hybrid approach
 
# At runtime, select plan based on estimated rows
estimated_rows = estimate_cardinality(parameter_values)
plan = selector.select_plan(estimated_rows)
result, stats = execute_plan(plan.plan, parameter_values)
selector.record_execution(plan, stats)

Multi-Plan Management Trade-offs
Approach	Memory Cost	Selection Overhead	Optimal Hit Rate	Complexity
Single Generic Plan	Low	None	Variable	Low
2-3 Range Plans	Moderate	Low	High	Moderate
N Cardinality Plans	High	Moderate	Very High	High
Per-Value Plans	Very High	High (hash lookup)	Optimal	Very High

Plan Regression Detection

Common causes of regression:

Statistics staleness — Optimizer makes decisions based on outdated data distribution
Parameter sniffing flip — New first-execution parameter produces bad plan
Cardinality estimation errors — Complex predicates or correlated columns
Schema changes — Index additions/removals change optimal access paths
Upgrade changes — New optimizer version chooses differently

Detection strategies:

Query Store / Workload Repository — Track historical plan performance
Real-time monitoring — Alert on execution time spikes
A/B comparison — Compare new plan against known-good baseline
Automatic reversion — Roll back to previous plan on detected regression

regression_detection.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
-- SQL Server Query Store: Plan Regression Detection
 
-- Enable Query Store
ALTER DATABASE YourDatabase
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    QUERY_CAPTURE_MODE = ALL
);
 
-- Find queries with multiple plans where newer plan is worse
WITH PlanStats AS (
    SELECT 
        q.query_id,
        p.plan_id,
        p.is_forced_plan,
        rs.first_execution_time,
        rs.last_execution_time,
        rs.count_executions,
        rs.avg_duration / 1000000.0 as avg_duration_sec,
        rs.avg_cpu_time / 1000000.0 as avg_cpu_sec,
        rs.avg_logical_io_reads
    FROM sys.query_store_query q
    JOIN sys.query_store_plan p ON q.query_id = p.query_id
    JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
    WHERE rs.count_executions >= 10  -- Minimum executions
)
SELECT 
    newer.query_id,
    qt.query_sql_text,
    older.plan_id as good_plan_id,
    older.avg_duration_sec as good_plan_duration,
    newer.plan_id as bad_plan_id,
    newer.avg_duration_sec as bad_plan_duration,
    (newer.avg_duration_sec / NULLIF(older.avg_duration_sec, 0)) 
        as regression_factor
FROM PlanStats older
JOIN PlanStats newer ON older.query_id = newer.query_id 
    AND older.plan_id < newer.plan_id
JOIN sys.query_store_query q ON older.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE newer.avg_duration_sec > older.avg_duration_sec * 2  -- 2x regression
    AND newer.last_execution_time > older.last_execution_time
ORDER BY regression_factor DESC;
 
-- Force a known-good plan (automatic regression fix)
EXEC sp_query_store_force_plan 
    @query_id = 12345, 
    @plan_id = 67;
 
-- Check forced plans
SELECT 
    q.query_id,
    p.plan_id,
    p.is_forced_plan,
    p.force_failure_count,
    qt.query_sql_text
FROM sys.query_store_plan p
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE p.is_forced_plan = 1;
 
-- Unforce plan
EXEC sp_query_store_unforce_plan 
    @query_id = 12345, 
    @plan_id = 67;
 
-- Enable automatic plan correction (SQL Server 2017+)
ALTER DATABASE YourDatabase
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

Best Practices for Plan Reuse

Effective plan reuse requires understanding your workload and applying appropriate strategies. Here are battle-tested practices from production environments.

Plan Reuse Best Practices

•Keep statistics fresh — Stale statistics are the #1 cause of bad plans. Configure auto-analyze/auto-update and supplement with manual ANALYZE for critical tables after bulk operations.
•Use parameterized queries consistently — Avoid ad-hoc SQL with literals. Inconsistent parameterization fragments the plan cache.
•Monitor for parameter sniffing issues — Track queries with high variance in execution time. These are candidates for RECOMPILE or OPTIMIZE FOR hints.
•Leverage Query Store / Workload Repository — Enable historical plan tracking. This provides invaluable data for regression detection and performance analysis.
•Test with production-like data — Plan choices in dev with 1000 rows differ dramatically from production with 100 million rows.
•Consider plan stability features — SQL Server's Automatic Plan Correction, Oracle's SQL Plan Management, PostgreSQL's pg_hint_plan can stabilize critical queries.
•Size plan cache appropriately — Too small causes frequent eviction and re-compilation. Monitor cache hit rates and eviction counts.
•Document critical query plans — For business-critical queries, capture baseline plans and set up monitoring for deviations.

Plan Reuse Decision Matrix
Query Characteristic	Recommended Strategy	Rationale
Simple, uniform data	Generic plan, full reuse	Plan quality varies little by parameter
Highly skewed data	OPTIMIZE FOR UNKNOWN or adaptive	Avoid worst-case sniffing
Critical business query	Plan guide / forced plan	Stability over optimality
Ad-hoc reporting	Custom plans (RECOMPILE)	Each query is different
High-frequency OLTP	Aggressive caching	Minimize optimization overhead
Complex joins	Adaptive joins + monitoring	Runtime conditions vary
Range predicates	Consider multiple plans	Selectivity varies dramatically

Don't Over-Optimize

Summary: Plan Reuse

Key Takeaways

•Plan reuse trades optimization cost for plan quality — Generic plans avoid compilation but may be suboptimal; custom plans are optimal but expensive.
•Parameter sniffing is a double-edged sword — Beneficial when first execution is typical; harmful when it's an outlier.
•Multiple solutions exist — RECOMPILE, OPTIMIZE FOR hints, plan guides, local variables, and adaptive processing each address different scenarios.
•Adaptive query processing is the modern solution — Runtime feedback allows databases to self-correct without manual intervention.
•Multi-plan approaches handle skew — Maintaining multiple plans for cardinality ranges provides near-optimal execution across diverse parameters.
•Regression detection is crucial — Query Store and similar tools catch when plans go bad, enabling quick correction.
•Fresh statistics are foundational — Most plan problems trace back to stale statistics; keep them current.

What's next:

Page Complete

4 / 5