The following sample shows sp_sysmon output for the “Lock Management” categories.
============================================================================= Lock Management --------------- Lock Summary per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- Total Lock Requests 0.2 6.5 26 n/a Avg Lock Contention 0.0 0.0 0 0.0 % Deadlock Percentage 0.0 0.0 0 0.0 % Lock Detail per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- Table Lock Hashtable Lookups 0.0 0.5 2 n/a Avg Chain Length n/a n/a 0.00000 n/a Spinlock Contention n/a n/a n/a 0.0 % Exclusive Table Total EX-Table Requests 0.0 0.0 0 n/a Shared Table Total SH-Table Requests 0.0 0.0 0 n/a Exclusive Intent Total EX-Intent Requests 0.0 0.0 0 n/a Shared Intent Granted 0.0 0.5 2 100.0 % Waited 0.0 0.0 0 0.0 % ------------------------- ------------ ------------ ---------- ---------- Total SH-Intent Requests 0.0 0.5 2 7.7 % Page & Row Lock HashTable Lookups 0.1 3.5 14 n/a Avg Chain Length n/a n/a 0.00000 n/a Spinlock Contention n/a n/a n/a 0.0 % Exclusive Page Total EX-Page Requests 0.0 0.0 0 n/a Update Page Total UP-Page Requests 0.0 0.0 0 n/a Shared Page Granted 0.1 3.5 14 100.0 % Waited 0.0 0.0 0 0.0 % ------------------------- ------------ ------------ ---------- ---------- Total SH-Page Requests 0.1 3.5 14 53.8 % Exclusive Row Total EX-Row Requests 0.0 0.0 0 n/a Update Row Total UP-Row Requests 0.0 0.0 0 n/a Shared Row Total SH-Row Requests 0.0 0.0 0 n/a Next-Key Total Next-Key Requests 0.0 0.0 0 n/a Address Lock Hashtable Lookups 0.1 2.5 10 n/a Avg Chain Length n/a n/a 0.00000 n/a Spinlock Contention n/a n/a n/a 0.0 % Exclusive Address Granted 0.0 1.0 4 100.0 % Waited 0.0 0.0 0 0.0 % ------------------------- ------------ ------------ ---------- ---------- Total EX-Address Requests 0.0 1.0 4 15.4 % Shared Address Granted 0.1 1.5 6 100.0 % Waited 0.0 0.0 0 0.0 % ------------------------- ------------ ------------ ---------- ---------- Total SH-Address Requests 0.1 1.5 6 23.1 % Last Page Locks on Heaps Total Last Pg Locks 0.0 0.0 0 n/a Deadlocks by Lock Type per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- Total Deadlocks 0.0 0.0 0 n/a Deadlock Detection Deadlock Searches 0.0 0.0 0 n/a Lock Promotions Total Lock Promotions 0.0 0.0 0 n/a Lock Timeouts by Lock Type per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- Total Timeouts 0.0 0.0 0 n/a
“Lock Promotions” does report detail rows if there were no occurrences of them during the sample interval. In this sample report, “Deadlocks by Lock Type” is one example.
