Compat 160 is slow after a 2016 to 2022 restore

/*
================================================================================
SQL Server Database Compatibility Level Audit
================================================================================
Description:
    Flags databases that are not running on the instance's default compatibility
    level and checks for auto_close, auto_shrink, and orphaned database owners.
    Running at a lower compatibility level prevents access to new optimizer
    improvements and query store features.

Output:
    List of databases with their current vs. instance-default compatibility level,
    along with misconfigured settings (auto_close, auto_shrink) and owner info.

Action:
    For databases with "WARNING: Below instance level", test and upgrade the
    compatibility level during a maintenance window:
        ALTER DATABASE [DBName] SET COMPATIBILITY_LEVEL = <instance_default>;
    For auto_close or auto_shrink enabled, disable immediately:
        ALTER DATABASE [DBName] SET AUTO_CLOSE OFF, AUTO_SHRINK OFF;
    For orphaned owners, run:
        EXEC [DBName].dbo.sp_changedbowner @loginame = N'sa';

Parameters:
    @DatabaseList - comma-separated database names or NULL for all user DBs

Criticality: Medium
Author:        Ravi Sharma
================================================================================
*/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;

DECLARE @DatabaseList NVARCHAR(MAX) = NULL;
DECLARE @InstanceCompat INT =
    COALESCE(
        CAST(SERVERPROPERTY(N'ProductMajorVersion') AS INT),
        CAST(PARSENAME(CAST(SERVERPROPERTY(N'ProductVersion') AS NVARCHAR(128)), 4) AS INT)
    ) * 10;

SELECT
    d.name AS [Database_Name],
    d.compatibility_level AS [Compatibility_Level],
    @InstanceCompat AS [Instance_Default_Compat_Level],
    d.collation_name,
    d.is_auto_close_on,
    d.is_auto_shrink_on,
    SUSER_SNAME(d.owner_sid) AS [Database_Owner],
    CASE
        WHEN d.compatibility_level < @InstanceCompat THEN N'WARNING: Below instance level'
        WHEN d.compatibility_level > @InstanceCompat THEN N'REVIEW: Above instance level'
        ELSE N'OK'
    END AS [Compat_Status],
    CASE
        WHEN SUSER_SNAME(d.owner_sid) IS NULL THEN N'CRITICAL: Orphaned database owner'
        WHEN SUSER_SNAME(d.owner_sid) = N'sa' THEN N'INFO: Owner is sa'
        ELSE N'OK'
    END AS [Owner_Status]
FROM sys.databases AS d
WHERE d.database_id > 4
  AND d.state = 0
  AND (
        @DatabaseList IS NULL
        OR d.name IN (
            SELECT LTRIM(RTRIM(value))
            FROM STRING_SPLIT(@DatabaseList, N',')
            WHERE LTRIM(RTRIM(value)) <> N''
        )
      )
ORDER BY d.compatibility_level, d.name;

You restore a SQL Server 2016 database onto SQL Server 2022. The application is fine at compatibility level 130. You flip to 160 and latency spikes.

That pattern is usually Cardinality Estimator and optimizer plan change, not a broken migration. Compatibility 160 turns on a newer CE, different transformations, and Intelligent Query Processing features that can reshape joins, grants, and parallelism for the same T-SQL.

Do this first

1. Leave production at 130 until Query Store holds a golden plan set
2. Capture 3–7 days of plans under real load
3. Reproduce at 160 in UAT with the same workload
4. Force last-good plans or apply scoped CE bridges for top regressions
5. Refresh statistics (FULLSCAN on hot tables) and align MAXDOP / CTFP / TempDB
6. Cut over only after rollback to 130 is rehearsed

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

Full playbook

The handbook article walks the phased path and links the interactive guide plus the SQL script package:

• Post-migration Compat 160 tweaks
• Interactive playbook
• 2016 → 2022 migration checklist

Why compatibility-level upgrades deserves a method

The central principle is that moving the engine binaries and changing database compatibility are separate changes with different risks. A single metric, recommendation, or successful command is not enough. The decision has to connect an observed symptom to a plausible cause, a controlled change, and a result that can be verified under the workload that matters.

Evidence to collect

Use evidence from the same time window whenever possible:

• Query Store baselines before the change
• deprecated features and breaking-change assessment
• cardinality-estimator and plan changes
• critical workload results and runtime distribution

A practical workflow

1. Upgrade the engine while retaining the current compatibility level.
2. Stabilize operations and capture a representative baseline.
3. Test compatibility level 160 on a restored production-like database.
4. Change compatibility deliberately and monitor regressions.

Common mistakes

Avoid these shortcuts:

• changing engine and compatibility level in one unmeasured step
• testing only that queries compile
• ignoring Query Store and rollback criteria

Verification and completion

Define completion before making the change. Confirm correctness first, then compare latency, throughput, reads, CPU, waits, storage behavior, or recovery time as appropriate for Compat 160 is slow after a 2016 to 2022 restore. Test negative and boundary cases, not only the path expected to succeed. Watch the system long enough to cover the relevant workload pattern.

Download and run the diagnostic

Before you run it

• Run the script first on a non-production or read-only connection when possible.
• Review the complete SQL file before execution and confirm that the connected instance is the intended target.
• Required access: VIEW ANY DATABASE or equivalent visibility into sys.databases.
• Open SQL Server Management Studio or Azure Data Studio, connect to the instance, open the downloaded file, and select Execute. Do not run diagnostic scripts through an application login.
• Save the result grid with the server name, collection time, and incident window. DMV output changes over time and often resets after a SQL Server restart.

What the script does

Lists compatibility level, expected instance level, collation, AUTO_CLOSE, AUTO_SHRINK, owner, and status for each online user database. The download is intended for diagnosis and does not apply an automatic remediation. Read every statement anyway, especially when adapting the script for automation or a different SQL Server version.

How to read the output

A below-instance compatibility warning is a testing decision, not an instruction to change immediately. Review Auto_Close and Auto_Shrink independently. An orphaned owner is a security and operational issue that should be corrected through an approved owner account.