https://sqlmaster.mssqlplanner.com

Tag: Locking and Concurrency

  • Capturing SQL Server Deadlock Graphs from system_health: A Real Diagnostic Walkthrough

    Capturing SQL Server Deadlock Graphs from system_health: A Real Diagnostic Walkthrough

    You already know what a deadlock is. Here’s how to actually retrieve the deadlock graph after the fact — without having set up a trace in advance — because system_health is running by default on every SQL Server instance.

    Anatomy of a deadlock cycle(the wait-for graph, drawn out)SPID 52UPDATE OrdersSPID 67UPDATE OrdersRow: OrderID 500X lock (exclusive)Row: OrderID 900X lock (exclusive)waits forheld bywaits forheld byDEADLOCK!one SPID becomes the victimCommon myth: the victim isn’t thetransaction that “started” the deadlock —it’s whichever is cheapest to roll back(least log written), by default. 📌

    Pulling Deadlock Graphs You Never Explicitly Captured

    SELECT CAST(event_data.value('(event/data/value)[1]', 'VARCHAR(MAX)') AS XML) AS deadlock_graph,
    event_data.value('(event/@timestamp)[1]', 'DATETIME2') AS event_time
FROM (
    SELECT XEventData.query('.') AS event_data
    FROM (
        SELECT CAST(target_data AS XML) AS TargetData
        FROM sys.dm_xe_session_targets st
        JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
        WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer'
    ) AS Data
    CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEventData)
) AS tab(event_data);

    Because system_health runs continuously by default, this query can retrieve deadlocks that happened before you even knew there was a problem — no advance trace setup required.

    Reading the Graph

    process-list Each <process> = one participant Includes the exact SQL text and waitresource resource-list Each resource = what’s being fought over Shows which process owns vs waits

    The victim-list element tells you which process SQL Server killed. Cross-reference the surviving process’s SQL text against the killed one’s — this is exactly how you confirm whether inconsistent access order (the classic cause) is really what happened.

    Beyond Theory: A Deadlock Involving a Table Scan

    Not every deadlock is the classic “two transactions, opposite order” case from Course 2. A single transaction doing a large table scan can deadlock against a small, targeted UPDATE if the scan acquires and holds shared locks across a wide range while the update needs an exclusive lock inside that range. The fix here isn’t reordering — it’s often reducing the scan’s lock footprint with a better index (tying directly back to Module 2 and 3).

    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.

  • Latches vs Locks in SQL Server: The Difference That Trips Up Even Experienced DBAs

    Latches vs Locks in SQL Server: The Difference That Trips Up Even Experienced DBAs

    Both sound like “something is blocking something.” They protect completely different things, and misdiagnosing one as the other sends you fixing the wrong problem.

    Locks vs latches: not the same fight(logical vs physical)LOCKSprotect LOGICAL data consistencyheld for the WHOLE transactionwait type: LCK_M_*LATCHESprotect PHYSICAL memory pagesheld for MICROSECONDSwait: PAGELATCH_*/PAGEIOLATCH_*VSThe most common latch-contention patternS1S2S3S4S5LAST PAGEIDENTITY columninsert herePAGELATCH_EX pileupCommon mistake: seeing PAGELATCH_EX andreaching for the LOCK playbook (shorter txns,isolation level) is the wrong fix. Latches needdifferent medicine: more files, hash keys. 📌

    Two Different Jobs

    Locks Protect LOGICAL data consistency Held for transaction duration Wait type: LCK_M_* Latches Protect PHYSICAL in-memory pages Held for microseconds, not transaction duration Wait type: PAGELATCH_*/PAGEIOLATCH_*

    Why the Distinction Matters in Practice

    A DBA seeing high PAGELATCH_EX waits and reaching for the usual lock-blocking playbook (shorten transactions, change isolation level) is solving the wrong problem — latch contention is about physical memory structure access, not logical transaction isolation. It needs a completely different fix.

    -- Distinguish the two directly from current waits
SELECT wait_type, COUNT(*) AS waiting_now
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'LCK%' OR wait_type LIKE '%LATCH%'
GROUP BY wait_type;

    The Most Common Latch Contention Pattern

    PAGELATCH_EX waits on the last page of a table with an ever-increasing key (like an IDENTITY column) under very high concurrent insert load is the single most common latch contention scenario — every session is racing to insert into the same physical page. This exact pattern sets up the tempdb contention lesson next, which is the same underlying phenomenon at a system-table level.

    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.

  • Diagnosing TempDB Contention in SQL Server: GAM, SGAM, PFS, and Multiple Data Files

    Diagnosing TempDB Contention in SQL Server: GAM, SGAM, PFS, and Multiple Data Files

    The previous lesson’s latch pattern shows up at system scale in one very specific, very common place: tempdb’s allocation pages, under heavy use of temp tables and table variables (yes — straight back to Course 2’s temp objects material).

    TempDB allocation-page pileup(GAM / SGAM / PFS contention)BEFORE: one tempdb fileGAM / SGAM / PFSsingle hot pageT1T2T3T4the fixAFTER: 4 equal-size filesFile 1gets: T1, T5, T9…own GAM/PFS pageFile 2gets: T2, T6, T10…own GAM/PFS pageFile 3gets: T3, T7, T11…own GAM/PFS pageFile 4gets: T4, T8, T12…own GAM/PFS pageround-robin: each new temp object grabs the next file in rotationCommon mistake: adding extra tempdb fileswithout matching their SIZE. Proportional-fillfavors whichever file has the MOST free space —unequal sizes defeat round-robin completely. 📌

    What’s Actually Being Contended

    Every tempdb data file has special allocation-tracking pages: GAM (Global Allocation Map), SGAM (Shared GAM), and PFS (Page Free Space). Every session creating a temp table or table variable must touch these pages to claim space — under high concurrency, many sessions latch-wait on the same few physical pages.

    Diagnosing It

    -- High PAGELATCH waits specifically on tempdb pages is the signature
SELECT wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGELATCH%'
ORDER BY wait_time_ms DESC;

-- Confirm it's tempdb specifically
SELECT session_id, wait_type, resource_description
FROM sys.dm_os_waiting_tasks
WHERE resource_description LIKE '2:%'; -- database_id 2 = tempdb

    The Standard Fix: Multiple Equally-Sized Data Files

    One tempdb data file All sessions fight over the SAME GAM/SGAM/PFS pages Multiple equal-size files Round-robin allocation spreads contention across separate page sets 

    -- Common starting guidance: one tempdb data file per CPU core, up to ~8, all EQUAL size
ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = 'D:tempdbtempdev2.ndf', SIZE = 1024MB, FILEGROWTH = 256MB);
-- Repeat with matching sizes for tempdev3, tempdev4...

    Equal size matters: SQL Server’s proportional-fill allocation favors the file with the most free space, so unequal files defeat the round-robin benefit entirely — a genuinely common mistake when adding files without matching existing sizes.

    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.

  • Module 5 Exercises: SQL Server Locking & Concurrency Labs (10 Hands-On Exercises)

    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.

  • Module 5 Quiz: SQL Server Locking & Concurrency (10 Questions)

    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.