Like any good mystery, some SQL Server problems look spooky on the surface but have a very simple culprit hiding in the shadows. Every good Scooby-Doo episode starts with something spooky: flickering lights, creepy footsteps, and someone yelling, “There’s a ghost in here!” In the world of SQL Server, one of the most common “ghosts” I run into? Forgotten database compatibility modes.
The Setup: A Creepy Little Detail
Here’s the deal: when you create a new database, it gets the compatibility mode that matches the engine level at that time. Sounds fine, right? But let’s say you spun up an Azure SQL Database years ago—chances are it’s still running at compat 140 or lower, even though the platform itself has moved on.
The same thing happens on-premises. I see customers upgrade their engine version or migrate to instances running on newer versions of SQL Server, but never update the compatibility mode for their user databases. The result? They’re running on yesterday’s features while today’s engine is sitting under the hood, waiting to be used. They are left wondering why their queries behave like they’ve been cursed. While you get some of the benefits on the new version, many performance and other enhancements are locked behind newer compatibility levels.
The Mystery: “Zoinks! Why Is This Acting So Weird?”
This is when I usually get called in—like Scooby and Shaggy tiptoeing through a haunted castle with a candle. A client will say: “This one database is acting so strange… queries don’t match the others, workloads feel unpredictable, and nothing makes sense.”
And sure enough, once we pull off the mask—ruh-roh—it’s the compatibility mode hiding underneath.
The Clues: How Compat Mode Haunts You
Compatibility mode might look innocent, but it can cause some truly spooky effects:
Query Optimizer behavior: You’re stuck with outdated cardinality estimators, leading queries down the wrong dark hallway.
New T-SQL features: Syntax and functions available in newer versions simply won’t work.
Performance tuning: Memory grant feedback, adaptive joins, table variable optimizations—features that help queries perform better—won’t kick in. They seemed to have vanished like ghosts.
Execution plans: The same query can behave completely differently depending on the compat mode. It might act perfectly fine in one database and monstrously bad in another.
Application surprises: Queries tuned in with one compat level may act completely differently when deployed to prod on another.
Ruh-Roh! The Risks of Upgrading Compatibility Modes
Now before you go racing off in the Mystery Machine flipping every database to the latest compat mode, let’s talk about the possible “ruh-rohs.” The risks are way smaller these days, but like any haunted house, there are still a few traps to watch out for.
Ghostly Query Plan Changes (Cardinality Estimator)
The biggie is still the Cardinality Estimator. Each new compat mode brings tweaks to how SQL Server guesses row counts. Most of the time, that means better performance—but sometimes a query that’s been coasting along suddenly takes a wrong turn down.
Execution Plan Regressions
Because of those changes, you might see a query suddenly slow down after the upgrade. The good news? Query Store is like Velma’s flashlight—it lets you see what changed and even force a “good” plan if the new one’s a dud.
Feature or Behavior Shifts
Every once in a while, new compat modes change the rules a bit. Implicit conversions, string handling, or old T-SQL quirks may act differently. Think of it like a hidden trap door—rare, but it can still catch you.
Application Surprises
Sometimes it’s not SQL Server that’s the problem—it’s the app. ORMs or homegrown code may rely on behaviors from older compat levels. When you bump the mode, those assumptions can come back to haunt you.
Testing Overhead
Like Shaggy creeping through a spooky hallway, you can’t just charge ahead. You’ve got to test your workloads. Most regressions get caught here—if you skip it, that’s when the monsters pop out.
The best news? These ghosts aren’t nearly as scary as they used to be. With Query Store, plan forcing, and smaller CE changes in recent versions, upgrading compat mode is safer than ever.
So yes, there are a few “ruh-rohs” lurking in the shadows—but with the right clues and a little testing, you’ll unmask the villain and walk out of the haunted house with better performance and modern features.
Solving the Mystery
Like every haunted house, compat mode upgrades can seem scary at first. But just like Velma’s flashlight, you’ve got tools to shine a light on the truth.
Query Store shows you what changed, plan forcing keeps you safe if regressions appear, and thorough testing lays out a trail of Scooby Snacks so you don’t get lost.
And when you finally unmask the villain? It’s not a ghost at all—it’s just a forgotten compat mode keeping your queries stuck in the past.
So gang, grab your flashlight, keep Scooby well-fed (and maybe a sandwich for Shaggy), and don’t let compat mode haunt your workloads. Because the real mystery isn’t if they’re haunting your workloads—it’s how long they’ve been hiding under the mask.
“And I would’ve gotten away with it too… if it weren’t for those meddling DBAs!”
Bonus Clue: A Job to Unmask Compatibility Modes
Since we’ve unmasked compat mode as one of the sneakiest villains in SQL Server, here’s a simple job you can use to check your user databases and make sure they’re running at the expected level. This Agent Job will bark (send email/alert) whenever it finds a database lagging behind. That way, you’ll catch ghosts before they start haunting your queries.
USE msdb;
GO
-- -- Scooby Snack Script: Find those sneaky compat modes
------------------------------------------------------------
-- 1. Create a job to check database compatibility levels
------------------------------------------------------------
IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N'Check Database Compatibility Levels')
BEGIN
EXEC sp_delete_job @job_name = N'Check Database Compatibility Levels';
END
GO
EXEC sp_add_job
@job_name = N'Check Database Compatibility Levels',
@enabled = 1,
@description = N'Checks user database compatibility levels against the instance version and sends email if lower.';
GO
------------------------------------------------------------
-- 2. Add a job step
------------------------------------------------------------
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'
DECLARE @InstanceVersion INT;
-- Get major version number of SQL Server
SELECT @InstanceVersion = CAST(SERVERPROPERTY(''ProductMajorVersion'') AS INT);
;WITH DBs AS (
SELECT
d.name,
d.compatibility_level,
@InstanceVersion AS InstanceVersion
FROM sys.databases d
WHERE d.database_id > 4 -- exclude system DBs
AND d.state = 0 -- online
)
SELECT name, compatibility_level, InstanceVersion
INTO #Results
FROM DBs
WHERE compatibility_level < (InstanceVersion * 10);
IF EXISTS (SELECT 1 FROM #Results)
BEGIN
DECLARE @body NVARCHAR(MAX) =
N''The following databases are running below the instance compatibility level:''
+ CHAR(13) + CHAR(10);
SELECT @body = @body +
''Database: '' + name +
'', Compatibility Level: '' + CAST(compatibility_level AS VARCHAR(10)) +
'', Instance Version: '' + CAST(InstanceVersion AS VARCHAR(10)) + CHAR(13) + CHAR(10)
FROM #Results;
EXEC msdb.dbo.sp_notify_operator
@name = N''The DBA Team'',
@subject = N''Databases with Lower Compatibility Level Detected'',
@body = @body;
END
';
EXEC sp_add_jobstep
@job_name = N'Check Database Compatibility Levels',
@step_name = N'Check Compatibility',
@subsystem = N'TSQL',
@database_name = N'master',
@command = @sql;
GO
------------------------------------------------------------
-- 3. Add a schedule (Weekly, Sunday at 2 AM)
------------------------------------------------------------
EXEC sp_add_schedule
@schedule_name = N'Weekly - Sunday 2AM',
@freq_type = 8, -- weekly
@freq_interval = 1, -- Sunday
@active_start_time = 20000; -- 2 AM
EXEC sp_attach_schedule
@job_name = N'Check Database Compatibility Levels',
@schedule_name = N'Weekly - Sunday 2AM';
GO
------------------------------------------------------------
-- 4. Add the job to SQL Agent
------------------------------------------------------------
EXEC sp_add_jobserver
@job_name = N'Check Database Compatibility Levels';
GO
The post The Case of the Forgotten Compatibility Mode – Scooby Dooing Episode 3 first appeared on A Shot of SQLEspresso.
The post The Case of the Forgotten Compatibility Mode – Scooby Dooing Episode 3 appeared first on A Shot of SQLEspresso.