SQL Server Compatibility Level 100 vs 160: Rethinking from a Query Store Perspective

Changing the database Compatibility Level in SQL Server is far more than just a matter of "version support." It is a major architectural decision that determines how queries are optimized, how cardinality is estimated, and whether modern Intelligent Query Processing (IQP) features are utilized.

When a specific query slows down, database administrators often temporarily restore performance by lowering the compatibility level. However, this comes at a steep price: sacrificing the optimizer improvements of the latest database engine for the entire database. In this post, we will look at the differences between Compatibility Level 100 and 160 from a Query Store perspective and share a practical database tuning strategy.

---

1. Does Query Store Work Under Compatibility Level 100?

One of the most common misconceptions is that "Query Store cannot be used under legacy compatibility levels like 100." Simply put, this is false.

Even if the Compatibility Level is set to 100 (equivalent to SQL Server 2008), as long as the database is running on SQL Server 2016 or higher, you can enable Query Store to leverage its core functions:

• Performance Data Collection: Collects query texts, execution plans, and runtime statistics (average duration, CPU, logical reads, etc.).
• Plan Regression Comparison: Compares performance before and after changing compatibility levels or between specific periods.
• Plan Forcing: Forces a specific, reliable plan_id for a given query_id to ensure execution stability.

Therefore, you don't need to give up Query Store just because you're running a lower compatibility level.

---

2. 100 vs 160: The Boundary of Intelligent Query Processing (IQP)

However, when moving beyond collection to automated query optimization, the compatibility level becomes a critical prerequisite. The latest Intelligent Query Processing features introduced in SQL Server 2022 (Compatibility Level 160) require level 160 to function.

① Parameter Sensitive Plan (PSP) Optimization

A persistent performance issue occurs when a parameterized query reuse plans that are not optimal for all arguments.

• The Issue: An Index Seek plan optimized for tenant_id = 1 (returning only a few rows) is cached, and then reused for tenant_id = 999 (returning millions of rows), causing severe performance degradation (known as Parameter Sniffing).
• IQP Solution: PSP Optimization maintains multiple execution plans for a single parameterized query and maps them dynamically based on the input parameter boundaries. (Requires Compatibility Level 160)

② Cardinality Estimation Feedback (CE Feedback)

The SQL Server optimizer predicts the number of rows (cardinality) returned by search conditions based on statistics. When this estimate is wrong, it ruins join order, join type (Hash vs Nested Loop), memory allocation, and parallelism.

• IQP Solution: CE Feedback logs the difference between predicted and actual row counts in Query Store and adjusts the estimation model for subsequent compilations. (Requires Compatibility Level 160 and Query Store in READ_WRITE state)

③ DOP Feedback & Memory Grant Feedback

• DOP Feedback: Monitors parallel query execution overhead to find and set the optimal Degree of Parallelism.
• Memory Grant Feedback: Prevents TempDB Spills due to insufficient memory allocations or resource wasting due to over-allocated memory during sorting/hashing by adjusting memory sizes dynamically on subsequent executions.

---

3. The Risks of Locking Compatibility Level at 100

A few legacy queries might run faster under the old Cardinality Estimator (Legacy CE) and optimization rules of Compatibility Level 100. But keeping the entire database at level 100 just to keep those few queries happy leads to massive downsides:

1. Blockage of Modern Optimizer Improvements: The database loses access to PSP, CE Feedback, DOP Feedback, and Batch Mode on Rowstore.
2. Inefficient Resource Usage: Missing out on optimizations tailored for modern CPU and memory architectures leads to higher hardware costs.

---

4. Best Practices for Migration & Query Control

To safely upgrade to Compatibility Level 160 without query regressions, follow a "Global Upgrade with Targeted Control" strategy.

graph TD
    A[1. Work under Level 100] --> B[2. Enable Query Store & Gather stable execution plans]
    B --> C[3. Upgrade database Compatibility Level to 160]
    C --> D{4. Detect query regression?}
    D -- Yes --> E[5. Force the stable plan_id using Query Store]
    D -- Yes --> F[6. Apply Query Store Hints like FORCE_LEGACY_CARDINALITY_ESTIMATION to the query]
    D -- No --> G[7. Benefit from PSP, CE Feedback, and other modern IQP features]

Step-by-Step Upgrade Protocol

1. Preparation: Enable Query Store under Level 100 to capture stable plans under production workloads.
2. Upgrade: Set the database Compatibility Level to 160.
3. Monitor: Keep a close eye on the 'Regressed Queries' dashboard in Query Store.
4. Mitigate:
   • Plan Forcing: For queries that slowed down, force the proven plan ID collected prior to the upgrade.
   • Query Store Hints: If plan forcing isn't possible (e.g., dynamic SQL), use Query Store Hints to apply targeted legacy directives (like FORCE_LEGACY_CARDINALITY_ESTIMATION) only to specific query IDs.

---

5. Designing Proper Statistics

The fact that "running UPDATE STATISTICS makes the system fast whenever it slows down" is a classic performance indicator. It means the optimizer's row count estimate is drifting from reality, resulting in poor execution plans.

• Default Rules: Always keep AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS ON.
• Advanced Needs: When data distribution changes rapidly or is heavily skewed (e.g., specific tenant IDs or dates), auto-statistics might not be enough. You should design:
  • Filtered Statistics: Target specific distribution patterns used frequently in search criteria.
  • Scheduled FULLSCAN Maintenance: Set up night-time jobs to run UPDATE STATISTICS ... WITH FULLSCAN on critical tables.

Conclusion

The modern paradigm of database performance tuning is not to hold back the entire system on a legacy compatibility level. Instead, upgrade the entire database to the latest level (160) to unlock modern engine features, and handle exceptional queries on a case-by-case basis using Query Store, Hints, and customized statistics.