The Monty Hall Problem That Made Me a Better DBA

I used to think probability puzzles were just math nerd bait. The Monty Hall problem, in particular, seems engineered to make me feel dumb. But the more time I spent looking at SQL Server execution plans, the more familiar it started to feel. It turns out Monty Hall and SQL Server have something in common: both make you think you’re getting a fair choice, when really the odds were skewed before you ever played.

What’s Monty Hall again?

You’re on a game show. There are three doors. Behind one: a car. Behind the other two: goats. You pick a door. Monty, the host, opens one of the remaining doors to reveal a goat. He offers you a choice: stick with your original door or switch.

Your instinct says, “It’s 50/50 now.” But it’s not. The math says switching gives you a 2/3 chance of winning. Staying only gives you 1/3. It’s weird, but it’s true, they even proved it on MythBusters.

SQL Server does the same thing

The over simplified version of this: It picks a door (an execution plan) based on what it knows at compile time. It makes a choice. Then it shows you a partial result: “Hey, here’s the plan I came up with.”

But the plan it picked might be based on a goat. Or a rare parameter. Or out-of-date stats. SQL Server isn’t lying to you. It’s just optimizing based on incomplete information.

Let’s demo it.

Demo: Parameter Sniffing and Bad Optimizer Assumptions

Fire this up in a test database.

-- Setup
CREATE DATABASE MontyDB
GO

DROP TABLE IF EXISTS dbo.MontyTest;

CREATE TABLE dbo.MontyTest (
	Id INT IDENTITY PRIMARY KEY
	,Category VARCHAR(20)
	,SomeData CHAR(100)
	);

-- Insert skewed data: 1 very common value, 2 rare values
INSERT INTO dbo.MontyTest (
	Category
	,SomeData
	)
SELECT CASE 
		WHEN n <= 1000
			THEN 'RareA'
		WHEN n <= 2000
			THEN 'RareB'
		ELSE 'Common'
		END
	,REPLICATE('x', 100)
FROM (
	SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
	FROM sys.all_objects a
	CROSS JOIN sys.all_objects b
	) AS x;
-- (100000 rows affected)

-- Create index to make plan choices interesting
CREATE NONCLUSTERED INDEX IX_Monty_Category ON dbo.MontyTest (Category);

Step 1: Create a stored procedure to trigger plan reuse

-- Clean cache and create procedure
DBCC FREEPROCCACHE;
GO

DROP PROCEDURE IF EXISTS dbo.SniffMonty;
GO
CREATE PROCEDURE dbo.SniffMonty @Category VARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM dbo.MontyTest WHERE Category = @Category;
END;
GO

Step 2: Run it with the rare value first

-- This execution generates the cached plan
EXEC dbo.SniffMonty @Category = 'RareA';

Now that a plan is cached for ‘RareA’, we’ll run it again but with ‘Common’. Below is the execution plan I received.

-- Run with common value — this is the “wrong” plan reused
EXEC dbo.SniffMonty @Category = 'Common';

What’s happening here is SQL Server saw ‘RareA’ first, built a plan that works great for a small result set, and then stuck with it, just like most contestants do on Monty Hall. When we run it again with ‘Common’, it reuses that same plan, even though it now has to scan almost the whole table. You end up with nested loops doing thousands of key lookups, and performance falls apart. It’s not technically wrong, it’s just running on an old guess that no longer fits.

SQL Server granted an execution plan that estimated only 1,000 rows, when in fact it needed to return 98,000 rows.

Step 3: Re-run it and reverse the order.

-- Clear cache
DBCC FREEPROCCACHE;
GO

-- Now run with the common value first
EXEC dbo.SniffMonty @Category = 'Common';
-- Then rare
EXEC dbo.SniffMonty @Category = 'RareA';

It’s not about right vs wrong. It’s about how SQL Server makes its decision before you know what door it opened.

The optimizer’s “door” isn’t random, it’s conditional. This is the Monty Hall connection. In the game show, Monty never opens the winning door. Up to this point I’ve been referring to this as luck, but really it’s knowledge. Once he opens a goat door, the odds change.

In SQL Server, the optimizer sees parameter values, stats, and cost estimates which then opens one plan door. What it shows you isn’t 50/50. It’s based on what it thinks is most likely. And that cached assumption sticks around until you force it to change.

So what do we do?

Sometimes we stick with the plan and get a goat. Unfortunately there isn’t a perfect solution to this problem, but there are a few strategies to mitigate it.

Use OPTION (RECOMPILE) to force a new plan every time. This is like switching doors every time you play Monty Hall. It avoids the problem of reusing a bad plan, but it can be expensive if the procedure is called frequently.

-- Force recompile per-execution
EXEC dbo.SniffMonty @Category = 'Common' WITH RECOMPILE;
EXEC dbo.SniffMonty @Category = 'RareA' WITH RECOMPILE;

Other options:

• Use OPTION (OPTIMIZE FOR UNKNOWN) if you want SQL Server to make a generic plan.
• Use OPTIMIZE FOR (‘Common’) if you know most values will be skewed that way. This option does not scale well, however.
• Consider splitting procedures for hot vs cold values, or use dynamic SQL to avoid plan reuse where it hurts.

Final thought: Don’t trust the first plan.

The optimizer’s job is to make a bet. Your job is to figure out whether that bet makes sense. Sometimes it doesn’t. Sometimes it’s based on outdated stats, parameter sniffing, or just plain bad luck.

When you find yourself staring at a bad execution plan, don’t assume it’s fair. Ask what it was built for. Ask whether the system showed you a goat. And when it does… switch doors.