SQL Server Problems You Can Reproduce, Measure and Fix: A Free Demo Library

A note on how this was made: I built these demos and explainers with the help of AI. Every script runs against real SQL Server instances, checks its own predictions, and is re-run by continuous integration on SQL Server 2019, 2022 and 2025, so the numbers here come from those runs.

Most SQL Server advice arrives as a rule: don’t use NOLOCK, rebuild indexes over 30% fragmentation, make every string nvarchar(max) because storage is the same anyway. Some of those rules are right, some are half right, and a few are wrong in ways that only show up under load.

I wanted to stop repeating rules and start showing evidence. So I built SQL Server Demos: a free, open-source library of runnable demos. Each one reproduces a real production problem on purpose, measures every common fix, and checks its own predictions. Every topic also has a short explainer here on the blog, in the SQL Server Demos library.

What’s in the library

The demos are organised around the symptom you’re seeing, not the feature name, because that’s how problems arrive in production:

• “It’s fast sometimes and slow other times”: parameter sniffing, slow in the app, fast in SSMS, catch-all queries, Query Store plan regressions
• “This query reads far more than it should”: non-SARGable predicates, implicit conversions, key lookups and the tipping point, partition elimination
• “The estimates are nowhere near the actual rows”: the ascending-key problem, correlated predicates, statistics sampling
• “Everything is blocked” or “we get deadlocks”: blocking chains, deadlock patterns, lock escalation, RCSI and snapshot isolation
• “The server is slow and I don’t know where to start”: wait stats triage, parallelism and CXPACKET
• “The transaction log is full”: why is my transaction log full?, minimal logging, backup and restore
• What’s new in SQL Server 2022 and 2025: parameter-sensitive plans and feedback, optimized locking, optional parameter plan optimization

The full list is on the library page.

A few things the demos measured

The point of running the demos rather than reading about them is that the numbers sometimes disagree with the folklore:

• NOLOCK read a balance that never existed. The writer rolled back, and the reader had already returned the uncommitted value. RCSI returned the last committed value with no wait at all. (RCSI and snapshot isolation)
• A 99.97% fragmented index did exactly the same reads as a clean one, to the page. Half-empty pages, at 0.12% fragmentation, doubled the reads. (index fragmentation)
• varchar(4000) holding 17-character names was granted 1.4 GB of memory for a sort that used 24 MB. varchar(50) was granted 68 MB. (memory grants)
• Page 1,000 with OFFSET ... FETCH took 159,530 reads. Keyset paging took 217, on every page. (pagination)
• IF EXISTS ... INSERT produced 598 primary key violations in 800 upserts under concurrency. UPDLOCK, SERIALIZABLE produced none. (upsert race conditions)
• On SQL Server 2025, the classic catch-all search dropped from 3,001,752 reads to 16,286 at compatibility level 170, but two optional parameters can still reuse a bad plan. (optional parameter plans)

How the demos are built

Every topic follows the same shape, so once you’ve run one you can run them all:

1. 01-setup.sql builds the data, usually in a database of its own.
2. 02-demo.sql runs the whole demonstration unattended and captures its own evidence.
3. 03-cleanup.sql puts back anything setup changed and drops everything.

Each demo ends with a result set listing anything that didn’t behave as predicted. Continuous integration runs every topic on SQL Server 2019, 2022 and 2025 on every push, so a passing run means the behaviour described really happened on that version, not just that the script finished.

They’re also written to be safe on a shared instance. Nothing flushes the whole plan cache or clears instance-wide wait stats, and setup records every setting it changes so cleanup can restore it. They’re still demos, though: run them on a scratch instance. A docker-compose.yml in the repo starts SQL Server 2019, 2022 and 2025 side by side.

Each explainer is a one-page answer

The explainers are short reference pages, not essays. Each one covers:

• What you’ll see: the symptom, in the words you’d search for
• Why it happens
• What to do, and what doesn’t help
• Check your server: a read-only query to run on your own instance
• Run it yourself: a link to the demo on GitHub

Every page also has a short link, such as marlonribunal.com/go/parameter-sniffing, to paste into a ticket or a chat.

Try it

• Browse the explainers: marlonribunal.com/sqlserver-demos
• Get the scripts: github.com/MarlonRibunal/sqlserver-demos
• Found a demo that didn’t behave as described? Open an issue. That’s exactly the kind of report I want.

If the library helps you, a star on GitHub or a share with your team helps other people find it.

Frequently asked questions

Are the SQL Server demos free?

Yes. The scripts are MIT-licensed on GitHub, and the explainers are free to read.

Which SQL Server versions do the demos support?

Continuous integration runs every demo on SQL Server 2019, 2022 and 2025. Demos for newer features report SKIPPED on older versions instead of failing. Azure SQL Database and Managed Instance haven’t been tested.

Can I run the demos on a production server?

No. Several demos change database settings by design. Run them on a scratch instance; the repo’s docker-compose.yml starts one in a minute.

Do I need a sample database?

Only the parameter sniffing demo needs WideWorldImporters. The others create their own data.