Module 5 Exercises: SQL Server Locking & Concurrency Labs
5 guided labs, 3 challenge scenarios, and 2 break-it labs.
Guided Labs
Guided1. Pull the most recent deadlock graph from system_health on your instance (there may be none if the instance is quiet — that’s a valid result too).
Guided2. Query sys.dm_os_waiting_tasks and sys.dm_os_wait_stats, and classify all current wait types as LCK, LATCH, or neither.
Guided3. Query sys.dm_db_file_space_usage for tempdb and check how many data files currently exist.
SELECT * FROM tempdb.sys.database_files WHERE type = 0;Guided4. In a test/dev instance only, add a second equally-sized tempdb data file and confirm both are the same size.
Guided5. Open two SSMS query windows and manually reproduce a simple deadlock (opposite update order on two rows), then pull the resulting graph from system_health.
Challenge Scenarios
Challenge6. A support ticket says “the database is locking up” during a bulk insert job. Using this module’s DMVs, determine whether this is lock contention, latch contention, or something else entirely.
Challenge7. A high-throughput OLTP table using an ever-increasing IDENTITY key shows growing PAGELATCH_EX waits as load increases. Propose two different structural fixes and their trade-offs.
Challenge8. A server shows heavy tempdb PAGELATCH contention but already has 4 tempdb files of visibly different sizes. Diagnose why the existing files aren’t helping.
Break-It Labs
Break-It9. In a disposable test database, deliberately create a table-scan-vs-targeted-update deadlock (not the classic two-row-swap kind) and capture its graph.
Break-It10. Deliberately induce tempdb contention: run many concurrent sessions each creating/dropping local temp tables in a tight loop against a single-file tempdb, observe PAGELATCH waits climb, then add tempdb files and re-measure.
Enjoyed this?
Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.
📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook
Want the full structured course with quizzes and hands-on labs? Check out SQL Server Performance Tuning, coming soon on this site.