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:
NOLOCKread 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 ... FETCHtook 159,530 reads. Keyset paging took 217, on every page. (pagination) IF EXISTS ... INSERTproduced 598 primary key violations in 800 upserts under concurrency.UPDLOCK, SERIALIZABLEproduced 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:
01-setup.sqlbuilds the data, usually in a database of its own.02-demo.sqlruns the whole demonstration unattended and captures its own evidence.03-cleanup.sqlputs 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.
The post SQL Server Problems You Can Reproduce, Measure and Fix: A Free Demo Library first appeared on SQL, Code, Coffee, Etc..