Module 5 Quiz: SQL Server Locking & Concurrency
1. Which SQL Server session runs by default and continuously captures deadlock graphs?
A) default_trace
B) system_health
C) AlwaysOn_health
D) None run by default
Show Answer
Answer: B
system_health is an always-on Extended Events session that captures deadlock graphs (and more) without any setup, retrievable after the fact via its ring buffer target.
2. In a deadlock graph’s XML, what does the victim-list element tell you?
A) Nothing useful
B) Which process SQL Server chose to kill to break the deadlock
C) The server’s IP address
D) The backup schedule
Show Answer
Answer: B
Cross-referencing the victim against the survivor’s SQL text is how you confirm the actual access-order conflict.
3. What do locks primarily protect?
A) Physical memory pages
B) Logical data consistency across a transaction
C) Network packets
D) CPU scheduling
Show Answer
Answer: B
Locks are held for the duration of the transaction to protect logical consistency (isolation).
4. What do latches primarily protect, and for how long are they typically held?
A) Logical data, for the whole transaction
B) Physical in-memory structures, typically for microseconds
C) Network connections, indefinitely
D) User permissions
Show Answer
Answer: B
Latches are a much shorter-duration, lower-level mechanism protecting physical page access, not transactional consistency.
5. “Gotcha”: A DBA sees high PAGELATCH_EX waits and shortens application transactions to fix it. Why is this the wrong fix?
A) Shortening transactions always fixes everything
B) Latch contention is about physical page access, not transaction duration — a different problem needing a different fix
C) PAGELATCH_EX doesn’t exist
D) This is actually the correct fix
Show Answer
Answer: B
Confusing latch contention for lock contention leads to solving the wrong problem — the lock-blocking playbook doesn’t address physical page contention.
6. What’s the classic latch contention pattern on a table with an ever-increasing IDENTITY key under heavy concurrent inserts?
A) No contention is possible
B) All sessions race to insert into the same last physical page, causing PAGELATCH_EX waits
C) It only affects SELECT queries
D) It causes data corruption automatically
Show Answer
Answer: B
Ever-increasing keys concentrate all inserts on the same “hot” last page — a well-known latch contention scenario.
7. What do GAM, SGAM, and PFS pages track in tempdb?
A) User permissions
B) Space allocation — which pages/extents are free or in use
C) Query execution plans
D) Backup history
Show Answer
Answer: B
These are allocation-tracking pages every session touches when claiming space for temp tables/table variables.
8. What’s the standard fix for tempdb allocation page contention?
A) Reduce tempdb to a single file
B) Multiple equally-sized tempdb data files, spreading round-robin allocation
C) Disable tempdb entirely
D) Increase the transaction log size only
Show Answer
Answer: B
Multiple files spread contention across separate allocation page sets — a well-established, standard configuration recommendation.
9. “Gotcha”: Why might adding tempdb data files of unequal size fail to help?
A) Unequal sizes are always fine
B) Proportional-fill allocation favors the file with the most free space, defeating round-robin distribution
C) SQL Server ignores extra files
D) It always improves performance regardless of size
Show Answer
Answer: B
Equal file sizes are essential for the round-robin allocation benefit to actually spread load evenly.
10. Which DMV/view shows what a currently-waiting session is actually waiting on, including its resource_description?
A) sys.dm_exec_query_stats
B) sys.dm_os_waiting_tasks
C) sys.dm_db_index_usage_stats
D) sys.dm_exec_cached_plans
Show Answer
Answer: B
sys.dm_os_waiting_tasks is the live, real-time view of exactly what each session is currently blocked on.
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.