Skip to content

About

Runnable SQL Server demos, one folder per problem: parameter sniffing, blocking, deadlocks, wait stats, the transaction log and more. Each demo checks its own predictions, and CI runs them on 2019, 2022 and 2025.

Topics

Resources

Stars

1 star

Watchers

1 watching

Forks

Latest commit

 

History

83 Commits

Folders and files

NameName
Last commit message
Last commit date
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

SQL Server demo scripts

demos License: MIT

Self-contained, runnable demonstrations of SQL Server behaviour — one folder per topic. Each folder has its own README with prerequisites, run order, and what to expect in the output.

Every topic also has a short explainer on my blog: what the problem looks like, why it happens, what fixes it, and a query to check your own server. Browse them all at SQL Server Demos on marlonribunal.com.

Built with AI assistance. The scripts, READMEs and explainers were written with the help of AI. Every result in them comes from actually running the demos on SQL Server, not from the AI. See how this was made.

Start from the symptom

You probably arrived with a problem, not a feature name. Find it below; each link is a topic that reproduces that problem on purpose and measures the fixes.

"It's fast sometimes and slow other times"

"This query reads far more than it should"

  • a function or expression on the column → non-SARGable predicates
  • the parameter's type doesn't match the column → implicit conversions
  • thousands of key lookups, or a scan where you expected a seek → the tipping point
  • a scalar function in the query → scalar UDF inlining
  • a date-partitioned table and the query reads every partition → partition elimination
  • a heap that keeps getting slower to scan → forwarded records
  • a columnstore table that isn't as fast or small as it should be → the delta store
  • a join to a table the query takes no columns from, or a filter a CHECK constraint rules out, still reads the table → untrusted constraints
  • a columnstore range query that reads every rowgroup: ORDER, MAXDOP, and loads after the build (SQL Server 2022, 2025) → ordered columnstore
  • filtering on a property inside JSON, or a JSON index that isn't used (SQL Server 2025) → the json data type
  • REGEXP_LIKE that scans, misestimates, or quietly misses rows (SQL Server 2025) → regular expressions
  • a nearest-neighbour search over embeddings that reads every row, or a vector index that misses rows (SQL Server 2025) → vector search
  • finding misspelt names: EDIT_DISTANCE, Jaro-Winkler, SOUNDEX (SQL Server 2025) → fuzzy string matching
  • || returned NULL, 'total=' || 1 + 2 failed, or a product came out as 3628800.0000000084 (SQL Server 2025) → new T-SQL syntax traps

"The estimates in the plan are nowhere near the actual rows"

"Half the transaction was saved" / "the job succeeded but the work is missing"

  • XACT_ABORT, TRY/CATCH, doomed transactions, errors CATCH can't see, RAISERROR vs THROW, a trigger written for one row → error handling

"The totals are off by a cent" / "the report is missing a day" / "the dates are swapped"

  • integer division, float, decimal and money rounding, strings cut short, datetime ranges, date formats, trailing spaces, identity overflow → data type traps

"The server is slow and I don't know where to start" / "what are we waiting on?"

  • elapsed = CPU + waits, per-session and live waits, WRITELOG, PAGEIOLATCH, LCK_M_*, SOS_SCHEDULER_YIELD, and the instance-wide numbers' noise → wait stats triage
  • thousands of single-use plans in the plan cache, high compilations → plan cache bloat

"Queries wait for memory" (RESOURCE_SEMAPHORE)

"CXPACKET is my top wait" / "small queries eat all the CPU"

  • a parallel query no faster than serial, skew, the cost threshold, why a plan stays serial → parallelism

"Everything is blocked" / "we get deadlocks"

"Duplicate rows" / "primary key violations under load" / "the counter is wrong"

"Page 500 of the results takes forever" / "users see the same row on two pages"

"The database is too big" / "should we turn on compression?"

  • ROW vs PAGE vs columnstore, the CPU it costs, data that won't compress, heaps, big documents → data compression

"Should I rebuild / add this index?"

"The rollback is taking forever" / "recovery after a restart takes hours"

"A tenant can see (or write) another tenant's rows" / "the admin report comes back empty"

  • writes past the filter, a spoofable SESSION_CONTEXT, leaks through unique keys and identities, the predicate that scans → row-level security

"Someone deleted the wrong rows" / "will our backups actually restore?"

  • point-in-time restore to a copy, the tail-log backup, COPY_ONLY and broken differentials, checksums → backup and restore
  • backups too big or too slow, and ZSTD compression on SQL Server 2025 → ZSTD backup compression

"The transaction log is full" / "the log file won't stop growing"

"The load is slow and the log is huge"

  • bulk inserts that should be minimally logged → minimal logging
  • tempdb contention, or when to use a #temp table → tempdb
  • one query fills tempdb for everyone: cap it per workload group (SQL Server 2025) → tempdb space governance

Topics

Topic Sample database Min. version Explainer
Parameter sniffing, RECOMPILE, memory grant feedback WideWorldImporters 2017 (2022+ for all six scenarios) Parameter sniffing
tempdb: when to reach for a #temp table none — creates its own 2016 tempdb and #temp tables
Index fragmentation: what it costs, and what it doesn't none — creates its own 2016 Index fragmentation
The ascending-key problem: estimates for rows statistics haven't seen none — creates its own 2016 SP2 The ascending-key problem
Implicit conversions: when a type mismatch costs a scan none — creates its own 2016 Implicit conversions
Non-SARGable predicates: which functions on a column cost a seek none — creates its own 2016 Non-SARGable predicates
Lock escalation: the threshold, and what does and doesn't avoid it none — creates its own 2016 Lock escalation
Heaps: forwarded records, and the pages a heap keeps none — creates its own 2016 Heaps and forwarded records
Key lookups and the tipping point none — creates its own 2016 Key lookups and the tipping point
Scalar UDF inlining: what SQL Server 2019 changed, and what it didn't none — creates its own 2019 Scalar UDF inlining
Columnstore: the delta store, and the rowgroups a query can skip none — creates its own 2016 SP1 Columnstore and the delta store
Ordered columnstore indexes: what ORDER buys a range query, and when it stops none — creates its own 2022 Ordered columnstore indexes (SQL Server 2022 and 2025)
Missing-index suggestions: what the DMVs ask for, and what to build instead none — creates its own 2016 Missing-index suggestions
"Slow in the app, fast in SSMS": SET options and the plan cache none — creates its own 2016 Slow in the app, fast in SSMS
Minimal logging: when bulk inserts really are cheap none — creates its own 2016 Minimal logging
Correlated predicates: when two columns aren't independent none — creates its own 2016 SP1 Correlated predicates
Memory grants sized by declared width none — creates its own 2016 (2019+ for C, 2022 for D) Memory grants sized by declared width
Catch-all queries: optional parameters, five ways none — creates its own 2016 Catch-all queries
Partition elimination traps none — creates its own 2016 SP1 Partition elimination
Statistics sampling and skew none — creates its own 2016 SP1 CU4 Statistics sampling and skew
RCSI and snapshot isolation: readers, writers, and the version store none — creates its own 2016 RCSI and snapshot isolation
Blocking chains: finding the head none — creates its own 2016 Blocking chains
"Why is my transaction log full?" none — creates its own 2016 SP2 Why is my transaction log full?
Query Store: catching a plan regression, forcing the fix, and watching it break none — creates its own 2016 (2022 for E) Query Store plan regressions
Upsert race conditions: IF EXISTS, MERGE, and the locks that make them safe none — creates its own 2016 SP1 Upsert race conditions
Deadlock patterns: making them on cue, fixing them, and reading the graphs none — creates its own 2017 Deadlock patterns
Parallelism: what it buys, what it costs, and when it goes wrong none — creates its own 2019 Parallelism and CXPACKET
Pagination: OFFSET vs keyset none — creates its own 2017 Pagination: OFFSET vs keyset
Data compression: ROW, PAGE, columnstore, and COMPRESS() none — creates its own 2016 SP1 Data compression
Accelerated Database Recovery: instant rollback, and what it costs none — creates its own 2019 Accelerated Database Recovery
Backup and restore: the restores you'll need, practised none — creates its own 2019 Backup and restore
Data type traps: wrong answers without an error none — creates its own 2016 Data type traps
Row-level security: what the filter stops, and what it doesn't none — creates its own 2016 SP1 Row-level security
MSTVFs and table variables: the fixed guesses, and what replaced them none — creates its own 2019 MSTVFs and table variables
Wait stats triage: where the time went none — creates its own 2017 Wait stats triage
Untrusted constraints: enabled, but not trusted, and the plans that costs none — creates its own 2016 Untrusted foreign keys and CHECK constraints
Filtered indexes: when they're used, when they're ignored, and the writes they break none — creates its own 2016 Filtered indexes: why yours isn't used
Plan cache bloat: single-use plans, and what each fix leaves behind none — creates its own; changes optimize for ad hoc workloads while it runs 2017 Plan cache bloat from ad hoc queries
Error handling in T-SQL: XACT_ABORT, TRY/CATCH, and what an error leaves behind none — creates its own 2017 T-SQL error handling: XACT_ABORT and TRY/CATCH
SQL Server 2022 query processing: plans that fix themselves, and how to tell none — creates its own 2022 SQL Server 2022 query processing
Optimized locking: fewer locks, less blocking, and what still waits none — creates its own 2025 Optimized locking (SQL Server 2025)
Optional parameter plans: SQL Server 2025's answer to the catch-all query none — creates its own 2025 Optional parameter plans (SQL Server 2025)
The json data type and JSON indexes: what SQL Server 2025 changes, and what it doesn't none — creates its own 2025 The json data type (SQL Server 2025)
Regular expressions in SQL Server 2025: REGEXP_LIKE against LIKE none — creates its own 2025 Regular expressions (SQL Server 2025)
Vector search in SQL Server 2025: exact, approximate, and the catches none — creates its own 2025 Vector search (SQL Server 2025)
Fuzzy string matching in SQL Server 2025: EDIT_DISTANCE, Jaro-Winkler, and SOUNDEX none — creates its own 2025 Fuzzy string matching (SQL Server 2025)
ZSTD backup compression in SQL Server 2025: smaller, often faster, and one trap none — creates its own 2025 ZSTD backup compression (SQL Server 2025)
Blocking a query with ABORT_QUERY_EXECUTION: SQL Server 2025's kill switch none — creates its own 2025 Blocking a query with ABORT_QUERY_EXECUTION (SQL Server 2025)
New T-SQL syntax in SQL Server 2025: ||, PRODUCT, and the traps in them none — creates its own 2025 New T-SQL syntax traps (SQL Server 2025)
tempdb space governance in SQL Server 2025: capping tempdb per workload group none — creates its own; changes Resource Governor while it runs 2025 Capping tempdb with Resource Governor (SQL Server 2025)

Min. version is the oldest version on which every scenario runs. Where only some scenarios need a newer version, the column says which. On an older instance the scripts still run without error, and each scenario they can't run is reported as SKIPPED rather than failing.

What has actually been tested. Every topic was developed on SQL Server 2022 CU25, and CI runs every topic on 2019, 2022 and 2025 on each push. A minimum of 2016 or 2017 comes from when the feature was introduced, according to Microsoft's documentation. Nothing here has been run on 2016 or 2017.

Azure SQL Database and Azure SQL Managed Instance are not tested. Many demos would need changes there. Most create and drop their own database, and several take backups to local disk. Azure SQL Database allows neither.

By version

If you're here to see what a specific release changed:

Running them

The fastest way to a scratch instance is the docker-compose.yml in this repo. It runs SQL Server 2019, 2022 and 2025 Developer edition side by side.

cp .env.example .env              # then choose your own MSSQL_SA_PASSWORD
docker compose up -d sql2022      # or sql2019, sql2025
tools/run-topic.sh tempdb         # one topic, end to end
tools/run-all.sh                  # every topic

tools/run-topic.sh runs a topic exactly as its README says: setup, the demo, the demo again (every demo must be re-runnable without re-running setup), then cleanup. It reports PASS, WARN or FAIL:

  • FAIL: a script raised an error, or the demo's final "anything that did not behave as predicted" result set has a row in it.
  • WARN: the only rows there are documented environment caveats, such as INCONCLUSIVE, INTERMITTENT or SKIPPED.

Every topic carries its own predictions and checks them, so a PASS means the behaviour the README describes really happened on that instance. It isn't just "the script ran". It defaults to the sqldemo-2022 container. Pass another container name as a second argument, or set SQL_CONTAINER.

  • The sa password comes from MSSQL_SA_PASSWORD, or failing that from the container's own environment.
  • A passing run deletes its output. Set KEEP_OUTPUT=1 to keep it. A WARN or FAIL always keeps it, and prints where it is.
  • Exit status: 0 passed (with or without warnings), 1 failed, 2 skipped (.ci-skip), 3 could not run (no container, no password, instance not ready).

tools/run-all.sh [container] runs every topic in alphabetical order and ends with a one-line tally: passed, passed with warnings, skipped, failed. It exits non-zero only if a topic failed.

Prefer SSMS or Azure Data Studio? Connect to localhost on the container's host port, then open each script and run it whole (F5). Every README says what to expect.

Service Container Host port
sql2019 sqldemo-2019 14319
sql2022 sqldemo-2022 14322
sql2025 sqldemo-2025 14325

Nothing in the containers is persisted. docker compose down throws the instance away, which is what these demos assume.

Platform notes

  • Apple Silicon (M-series Macs). The SQL Server images are x64 only. In Docker Desktop, turn on Use Rosetta for x86_64/amd64 emulation on Apple Silicon (Settings → General). The demos run under emulation, but more slowly, so timings and some CPU-based numbers will differ from the READMEs. None of the demos has been tested under emulation.
  • Windows. The tools/ scripts are bash. Run them from WSL 2, with Docker Desktop's WSL integration on, or from Git Bash. Or skip the scripts and run each .sql file from SSMS.
  • Memory. Give each container about 2 GB. Running all three side by side takes about 6 GB of Docker's memory allowance.
  • Your own instance. Not using Docker? Run the scripts in order from SSMS, Azure Data Studio or sqlcmd -I (QUOTED_IDENTIFIER ON, which several demos need). tools/run-topic.sh only drives containers.

Continuous integration

.github/workflows/demos.yml runs every topic through tools/run-topic.sh on SQL Server 2019, 2022 and 2025, on every push to main, on pull requests, and on demand. Every topic runs on every version, including versions older than it needs, so CI also checks that a version-gated topic reports SKIPPED cleanly instead of failing. A topic that needs something the CI containers don't have has a .ci-skip file saying so. Today that's only parameter sniffing, which needs WideWorldImporters.

How these are built

Every topic follows the same shape, so once you've run one you know how to run the rest.

Numbered scripts, run in order.

01-setup.sql     builds objects and loads data. Runs no demo of its own.
02-demo.sql      the demonstration. Run the whole file — never step through it.
03-cleanup.sql   restores anything setup changed, then drops everything.

Setup and demo are always separate, so it's never ambiguous which script to run when, and so you can re-run a demo without rebuilding its data.

Demos run unattended. A demo script captures its own evidence — into a results table, an Extended Events session, or both — and prints a summary at the end. You should not have to sit there reading execution plans between executions to see the point.

Everything lives in a Demo schema so cleanup can drop it wholesale without touching the sample database. A topic that needs no sample database may create its own database instead and drop it in cleanup — that is preferred where it works, since it puts the demo's blast radius at zero.

Setup records what it changes. If a script alters a compatibility level, a scoped configuration, a database setting such as Query Store, or a server setting, it saves the original value first and cleanup puts it back. Each topic README states plainly what its setup mutates — including when the answer is "nothing", which is worth stating explicitly rather than leaving to inference.

Prefer a scenario that needs no setting change at all. Where two scenarios teach the same lesson, the one that does not touch engine configuration wins, even if it takes more setup data to build.

Setup changes settings, never data it did not create. Turning Query Store on for a demo is fair; clearing the query history that was already in it is not. Where a demo could take a destructive shortcut, it takes the longer route and says why in comments.

Object-scoped cache operations only — sp_recompile, or DBCC FREEPROCCACHE with a single plan handle (plan cache bloat removes its own plans that way), never a bare DBCC FREEPROCCACHE. Nothing here flushes an entire instance's plan cache.

Nothing runs against production. Several topics change database-level settings by design, and tempdb space governance changes Resource Governor for the whole instance while it runs, then puts it back; plan cache bloat does the same with optimize for ad hoc workloads. Use a scratch instance.

Adding a topic

  1. Create <topic-name>/ — kebab-case, named for the behaviour being demonstrated, not the fix.
  2. Add 01-setup.sql, 02-demo.sql, 03-cleanup.sql following the conventions above. Split further only if a topic genuinely needs it (02a, 02b).
  3. Write <topic-name>/README.md covering: prerequisites, run order with rough timings, what each scenario shows, what setup changes on the database, and how to read the output.
  4. End 02-demo.sql with a result set headed === N. ANYTHING THAT DID NOT BEHAVE AS PREDICTED === that lists every check whose verdict was wrong. That is what tools/run-topic.sh and CI read.
  5. If a scenario needs a newer version than the oldest CI runs, check the version first and report it as SKIPPED there. Don't let it error.
  6. Run tools/run-topic.sh <topic-name> until it reports PASS, on every version the topic claims to support.
  7. Add a row to the Topics table above, an entry under Start from the symptom, and, if it shows a version-specific feature, a link under By version.
  8. Note any client-specific requirements (SSMS settings, sqlcmd fallbacks) in the topic README — not everyone runs SSMS.

Sample databases

Topics use Microsoft's published sample databases rather than synthetic data where possible. Individual READMEs link to the download and give the restore command.

Database Source
WideWorldImporters sql-server-samples releases

Backups are gitignored — download them yourself rather than expecting them here.

Where a sample database won't reproduce the behaviour on its own (WideWorldImporters is generated with near-uniform distributions, so it can't show data skew), the setup script manufactures the conditions deliberately and says so in comments. A demo that admits what it constructed is worth more than one claiming to have found it in the wild.

Found a problem?

Open an issue. A demo that doesn't behave as its README says is exactly what's worth reporting. Please include:

  • the output of SELECT @@VERSION;
  • the topic, and the PASS/WARN/FAIL line from tools/run-topic.sh if you used it
  • the rows of the demo's final "ANYTHING THAT DID NOT BEHAVE AS PREDICTED" result set. With run-topic.sh, a WARN or FAIL keeps the full output in the folder it prints.
  • anything unusual about the instance: core count, memory, non-default settings, Docker on Apple Silicon

How this was made

I built this repository with the help of AI: an AI coding assistant (Claude) helped write the scripts, the READMEs and the explainer pages, under my direction.

What keeps it honest is that nothing here is taken on the AI's word:

  • every demo runs against a real SQL Server instance and ends by listing anything that did not behave as its README predicts
  • continuous integration re-runs every topic on SQL Server 2019, 2022 and 2025 on every push
  • every number in a README or explainer was copied from a real run, and each README names the build it was measured on

If you find something that's wrong anyway, please open an issue.

Disclaimer

These scripts are provided as-is, for demonstration and educational purposes only. They have been tested in a limited, controlled environment — the specific SQL Server versions and configurations named in each topic README, and nowhere else. They are not production-ready, and they have not been validated against any other version, edition, configuration, or workload.

Run them only on a scratch instance you can afford to lose. Read every script before you run it. You are solely responsible for reviewing, testing, and deciding whether to run anything here.

To the fullest extent permitted by law, the author provides this repository WITHOUT WARRANTY OF ANY KIND, express or implied, including but not limited to the warranties of merchantability, fitness for a particular purpose, and non-infringement. In no event shall the author be liable for any claim, damages, data loss, service interruption, or other liability, whether in an action of contract, tort, or otherwise, arising from, out of, or in connection with these scripts or their use.

License

MIT. The as-is and no-liability terms in the licence apply in addition to the disclaimer above.

About

Runnable SQL Server demos, one folder per problem: parameter sniffing, blocking, deadlocks, wait stats, the transaction log and more. Each demo checks its own predictions, and CI runs them on 2019, 2022 and 2025.

Topics

Resources

Stars

1 star

Watchers

1 watching

Forks

Releases

Packages

Contributors

Languages