Overview

This playbook targets a very specific, common scenario: databases restored from SQL Server 2016 onto SQL Server 2022 run fine at compatibility_level 130, but the application slows down once the level is raised to 160. Compatibility 160 switches on a newer Cardinality Estimator, newer optimizer transformations, and Intelligent Query Processing all at once, so plans that were healthy under 2016 can suddenly get worse estimates, join choices, and memory grants. The goal here is to diagnose the real cause first (server config, statistics, CE, parameter sniffing, or IQP) and remediate surgically while staying on CL 160, treating Legacy CE and full rollback to 130 as last resorts. Everything you need is on this page and in the sidebar - work top to bottom, or jump straight to the guided approach.

Typical root cause

Slowness at CL 160 is usually not a broken install. Compatibility 160 enables a newer Cardinality Estimator (CE), different optimizer transformations, and Intelligent Query Processing (IQP). Plans that worked under CE 130 can get worse row estimates under CE 160, leading to different join types, memory grants, spills, and parameter-sensitive plan changes.

  • CE estimate changes on joins, filters, and correlated predicates
  • IQP features turned on together when skipping 2017/2019: UDF inlining, batch mode on rowstore, PSP
  • Parameter sniffing amplified on skewed data
  • Implicit conversions and non-SARGable SQL exposed by new plan choices
  • Post-restore: stale 2016 stats / row counts if FULLSCAN + DBCC UPDATEUSAGE were skipped

Backup/restore nuances (2016 -> 2022)

Restore carries over 2016 statistics headers and modification counters. Jumping to CL 160 also activates SQL 2019 + 2022 IQP features the app never saw under 2016. New hosts often change core/NUMA counts, so CTFP (raise from default 5 to 30-50) and MAXDOP must be re-baselined. On engagements where stats/indexes/MAXDOP are already done, keep these steps as a checklist for the next migration.

  • Post-restore: DBCC UPDATEUSAGE(0) then UPDATE STATISTICS ... WITH FULLSCAN before CL 160
  • Instance: CTFP 30-50, MAXDOP per NUMA physical cores, optimize for ad hoc = 1
  • Widespread CL 160 pain: Legacy CE / UDF inlining OFF / optimizer hotfixes as scoped bridges

What this playbook does

  • Diagnose before hints - rule out server config, blocking, stats, CE, sniffing, and IQP layers
  • Capture baselines - Query Store golden plans at CL 130 before flipping UAT/prod to 160
  • Find regressions - compare duration, CPU, logical reads, and waits before vs after
  • Remediate surgically - plan force, Query Store hints (SQL 2022), IQP isolation, targeted rewrites
  • Cut over safely - controlled move to CL 160 with rollback and monitoring scripts

How to run it

  1. Open this HTML in a browser (Chrome/Edge). Use the sidebar or Script library.
  2. Follow the methodology flow top to bottom. Click a step to open its recommended script.
  3. View or Download a script, open SSMS, replace [YourDB] and date windows where marked.
  4. Run read-only diagnostics first on UAT. Scripts show safety level (Safe / Test First / High Risk).
  5. Keep production at CL 130 until Query Store has 3-7 days of baseline at 130, then test 160 in UAT.
  6. Do not flip prod to 160 without Query Store or equivalent before/after comparison.
Consulting rule: Determine whether slowness is Server config, Engine (blocking/waits), CE, Statistics, Parameter sniffing, IQP, or Application before query hints or Legacy CE. Hints and plan force are last resorts.

Fastest safe path (from Quick Start)

  1. Post-restore baseline: UPDATEUSAGE + stats FULLSCAN; set CTFP/MAXDOP/ad hoc on the new host.
  2. Keep production at CL 130; enable Query Store and capture workload (3-7 days).
  3. On UAT, set COMPATIBILITY_LEVEL = 160, clear proc cache, run the automatic regression report.
  4. Few regressions: force last-good plan or apply Query Store hints (no app deploy on SQL 2022).
  5. Many regressions: Legacy CE and/or disable UDF inlining / isolate one IQP feature - keep CL 160.
  6. Rewrite stubborn SQL; remove temporary bridges; move production to 160 in a change window.

Emergency rollback: ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL = 130;

What this file is

This is a self-contained HTML runbook for DBAs and consultants. It packages diagnostic SQL, remediation scripts, a methodology flowchart, and Microsoft/community references for the common scenario: databases restored from SQL Server 2016 onto SQL Server 2022, application stable at compatibility_level 130, but slower after moving to 160.

All scripts are embedded in this HTML (View / Copy / Download work offline with no folder path required). Companion markdown guides live under docs/ (relative to this HTML): docs/00_README_Troubleshooting_Playbook.md, docs/QUICK_START.md, docs/CLIENT_CUTOVER_CHECKLIST.md, docs/BACKUP_RESTORE_NUANCES_2016_to_2022.md, docs/HOW_TO_SHARE.md.

Sharing: Zip this entire folder and send it. Recipients should unzip, then open Compat160_Targeted_Fixes.html from the unzipped root. No drive letters or machine-specific paths are used. Loose .sql files also sit under relative folders (01_Diagnostics/, 03_QueryStore_PlanForce/, etc.). Documentation is in docs/.

Guided approach (for junior DBAs)

Follow steps in order - like an expert mentor. Complete each step, then choose whether it fixed the issue or you must continue.

Rule: Do not skip ahead. Do not apply query hints or Legacy CE until Step 9+. Work in UAT first. Keep production at CL 130 until you have a Query Store baseline.
Progress: Step 1 of 10

Steps

    Resolved - good work.
    Document what fixed it (script used, scoped config, forced plan_id, or rewrite). Keep monitoring for 24-48 hours with the post-cutover health check. If you only used a temporary bridge (Legacy CE / UDF inlining OFF), open a follow-up ticket to remove it after a permanent fix.

    Methodology flow

    Click a step to open the recommended script. Only proceed down when the layer is ruled out or fixed.

    Hints / Legacy CE / plan force sit at the bottom intentionally. Microsoft Learn and field consultants treat them as controlled mitigations after diagnosis.

    Fix path at CL 160

    After methodology confirms CE/IQP layer.

    1. Run
    2. Few queries -> QS hint or force plan
    3. Many queries -> isolate one IQP feature; temp Legacy CE only as bridge
    4. Enable Automatic Tuning; rewrite stubborn SQL; remove bridges

    Settings explained (FAQ)

    Deep dive on the two database-scoped toggles clients ask about most: TSQL_SCALAR_UDF_INLINING = OFF and LEGACY_CARDINALITY_ESTIMATION = ON. What each one is, why you would use it, the consequences, and how to troubleshoot further.

    Read this first - the mental model. Compatibility level 160 bundles three separate things together: (1) the Cardinality Estimator (CE) version, (2) Intelligent Query Processing (IQP) features such as scalar UDF inlining and batch mode, and (3) optimizer/parser behavior. The two settings below let you turn off one piece at a time and keep the database at CL 160, instead of rolling the whole database back to 130. That is why they are "surgical bridges", not permanent fixes. Both are database-scoped configurations, not query hints - the client's application code and stored procedures do not change.

    1. TSQL_SCALAR_UDF_INLINING = OFF

    What it is. Scalar UDF inlining is an IQP feature introduced in SQL Server 2019 and active at CL 150+. When ON (the default at CL 160), the optimizer rewrites the body of a scalar user-defined function (e.g. dbo.fn_CalcTax(...)) directly into the calling query, so the whole statement gets one combined execution plan instead of running the function row-by-row. Setting TSQL_SCALAR_UDF_INLINING = OFF turns that rewrite off and returns to the classic SQL 2016 behavior where the scalar UDF executes once per row.

    Why turning it OFF can help. Inlining is usually a win, but for some functions it backfires and is a very common cause of post-CL-160 slowness:

    • Bad cost/estimates on complex UDFs - functions with many statements, WHILE loops, or data access get inlined into a giant, hard-to-estimate plan that the optimizer costs poorly.
    • Huge / slow-to-compile plans - inlining a UDF used in many places can explode plan size and increase compile time and memory grants.
    • Different (worse) join or memory-grant choices - the combined plan picks a strategy that was never a problem when the UDF ran separately.
    • Timeouts that only appear at CL 160 - the same query text runs fine at 130/150-without-inlining but regresses once inlined.

    Turning inlining OFF keeps the database at CL 160 (so you retain every other 2022 improvement) while removing just this one behavior, restoring the SQL 2016 execution pattern for scalar UDFs.

    Use case (when to reach for it). You confirmed via diagnostics that regressed queries call scalar UDFs, the plans show inlined function logic, and disabling inlining in a test restores performance. Ideal when many objects are affected and you cannot rewrite them all immediately.

    Consequences / trade-offs.

    • UDFs that did benefit from inlining lose that benefit database-wide (this is a blunt, DB-level switch).
    • It masks the root cause - the real fix is rewriting scalar UDFs as inline table-valued functions (iTVFs) or set-based logic.
    • Scope is the whole database; you cannot inline some UDFs and not others with this switch (use OPTION(USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING')) or the INLINE = OFF function option for per-object control).

    Scoping it tighter (preferred order). Per-statement hint → per-function WITH INLINE = OFF → database-scoped OFF (broadest). Start narrow.

    How to troubleshoot further.

    • List inlineable functions and confirm which are actually being inlined.
    • Compare a regressed query with inlining ON vs OFF and diff the plans and metrics.
    • Long term, convert hot scalar UDFs to iTVFs / set-based code, then re-enable inlining.
    -- Database-wide (blunt) - keeps CL 160, disables only UDF inlining:
ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

-- Narrower: single query
SELECT ... OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));

-- Narrowest: single function
ALTER FUNCTION dbo.fn_Example (...) WITH INLINE = OFF ...;

    2. LEGACY_CARDINALITY_ESTIMATION = ON

    What it does. The Cardinality Estimator is the part of the optimizer that predicts how many rows each operator will produce. SQL Server 2014 introduced a "new CE"; SQL 2016/2019/2022 refined it further. At CL 160 the database uses the newest CE. Setting LEGACY_CARDINALITY_ESTIMATION = ON tells the optimizer to use the SQL Server 2012-and-earlier "legacy" (CE 70) row-estimation model while keeping the database at CL 160. It is the database-scoped equivalent of the trace flag 9481 / USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION').

    Why you might need it. The new CE makes different assumptions (independence vs correlation of predicates, ascending-key handling, join containment). On some schemas - especially older ones designed against the legacy CE - these assumptions produce worse estimates, which cascade into wrong join types, undersized/oversized memory grants, and spills. When a broad set of queries regress at CL 160 and diagnostics point at estimate changes (not blocking, not stats, not UDFs), the legacy CE can restore the SQL 2016-era estimates immediately without an application change.

    "Will this set all the CE back to 2016?" - Important clarification. Not exactly. It does not revert the database to compatibility level 130, and it is not the same CE that CL 130 used. CL 130 runs the CE 130 model; LEGACY_CARDINALITY_ESTIMATION = ON forces the even older CE 70 (legacy / pre-2014) model. It changes only the cardinality estimation component - all other CL 160 behavior (IQP, optimizer transformations, parser features) stays active. So it is broader than a single query hint (it affects every query in the database) but narrower than a full compatibility-level rollback.
    Use case (when to reach for it). Many queries regress after moving to CL 160, plan comparisons show the difference is in row estimates, and testing with FORCE_LEGACY_CARDINALITY_ESTIMATION on a few of them restores performance. Use it as a temporary bridge to keep CL 160 in production while you fix root causes.

    Consequences / trade-offs.

    • Database-wide blunt instrument - every query now estimates with the old model, including ones that were faster under the new CE. You may fix 20 queries and slow down 5 others.
    • You lose newer CE improvements - better handling of modern workloads, some IQP features rely on good estimates.
    • It hides the real problem - usually stale statistics, missing/multi-column stats, skew, or non-SARGable SQL. Legacy CE just papers over the estimate.
    • Not a permanent answer - Microsoft guidance is to stay on the current CE and remediate; treat this as a stabilizer while you do targeted work (Query Store hints, plan forcing, rewrites).

    Prefer narrower scope first. Before flipping the whole database, force legacy CE on just the offending queries via a Query Store hint (SQL 2022, no code change) or OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')). Only go database-wide if regressions are widespread.

    How to troubleshoot further.

    • Compare CE 130 vs CE 160 estimates on the regressed queries and confirm the gap is in estimated rows.
    • Fix inputs to the CE first: UPDATE STATISTICS ... WITH FULLSCAN, add missing/multi-column statistics, remove implicit conversions and functions on columns (SARGability).
    • Use Query Store to force last-good plans or apply per-query legacy-CE hints instead of the DB-wide switch.
    • Re-test with legacy CE OFF after fixing stats/SQL; the goal is to remove the bridge.
    -- Database-wide (blunt) - keeps CL 160, forces the OLD CE 70 model everywhere:
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

-- Narrower: single query only (no app change needed on SQL 2022 via Query Store hint)
SELECT ... OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

    Which one, and in what order?

    1. Diagnose first. Confirm the layer: server config, blocking/waits, statistics, CE estimates, parameter sniffing, or IQP. Do not toggle blindly.
    2. If regressed queries use scalar UDFs and plans show inlined logic → test DISABLE_TSQL_SCALAR_UDF_INLINING (per query first).
    3. If the gap is in row estimates across many queries → test FORCE_LEGACY_CARDINALITY_ESTIMATION (per query first).
    4. Narrow before broad. Query Store hint / statement hint → per-object → database-scoped configuration.
    5. Both are bridges. Keep CL 160, stabilize, then fix root causes (stats, SARGability, UDF rewrites) and remove the toggle.
    Golden rule: These two switches keep you on CL 160 while you fix the real problem - they are not a destination. Reverting the whole database to CL 130 should be the last resort, not the first.

    References

    Official and community sources aligned with this playbook.

    Script library

    Embedded SQL - view, copy, or download. Filter by tag or search by keyword.