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 2634.5 151.2 1580714 n/a
Avg Lock Contention 2.4 0.1 1436 0.1 %
Deadlock Percentage 0.0 0.0 1 0.0 %
Lock Detail per sec per xact count % of total
------------------------- --------- --------- ------- ----------
Table Lock Hashtable
Lookups 0.0 0.0 0 n/a
Spinlock Contention n/a n/a n/a 0.0 %
Exclusive Table
Granted 403.7 4.0 24376 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- --------- --------- -------
Total EX-Table Requests 0.0 0.0 0 0.0 %
Shared Table
Granted 325.2 4.0 18202 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- --------- --------- -------
Total SH-Table Requests 0.0 0.0 0 0.0 %
Exclusive Intent
Granted 480.2 4.0 29028 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- --------- --------- -------
Total EX-Intent Requests 480.2 4.0 29028 18.9 %
Shared Intent
Granted 120.1 1.0 7261 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- --------- --------- -------
Total SH-Intent Requests 120.1 1.0 7261 4.7 %
Page & Row Lock HashTable
Lookups 0.0 0.0 0 n/a
Spinlock Contention n/a n/a n/a 0.0 %
Exclusive Page
Granted 483.4 4.0 29227 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- --------- --------- -------
Total EX-Page Requests 483.4 4.0 29227 19.0 %
Update Page
Granted 356.5 3.0 21553 99.0 %
Waited 3.7 0.0 224 1.0 %
------------------------- --------- --------- -------
Total UP-Page Requests 360.2 3.0 21777 14.2 %
Shared Page
Granted 3.2 0.0 195 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- --------- --------- -------
Total SH-Page Requests 3.2 0.0 195 0.1 %
Exclusive Row
Granted 1.3 0.1 751 75.6 %
Waited 0.4 0.0 243 24.4 %
------------------------- --------- --------- ------- ----------
Total EX-Row Requests 1.7 0.1 994 0.1 %
Update Row
Granted 0.2 0.0 155 62.0 %
Waited 0.3 0.0 95 38.0 %
------------------------- --------- --------- ------- ----------
Total UP-Row Requests 0.4 0.0 250 0.0 %
Shared Row
Granted 1699.8 103.3 1019882 100.0 %
Waited 0.1 0.0 46 0.0 %
------------------------- --------- --------- ------- ----------
Total SH-Row Requests 1699.9 103.3 1019928 59.7 %
Next Key
Total Next-Key Requests 0.0 0.0 0 n/a
Address Lock Hashtable
Lookups 0.0 0.0 0 n/a
Spinlock Contention n/a n/a n/a 0.0%
Exclusive Address
Granted 134.2 1.1 8111 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- --------- --------- -------
Total EX-Address Requests 134.2 1.1 8111 5.3 %
Shared Address
Granted 959.5 8.0 58008 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- --------- --------- -------
Total SH-Address Requests 959.5 8.0 58008 37.8 %
Last Page Locks on Heaps
Granted 120.1 1.0 7258 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- --------- --------- -------
Total Last Pg Locks 120.1 1.0 7258 4.7 %
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.1 0.0 4 n/a
Searches Skipped 0.0 0.0 0 0.0 %
Avg Deadlocks per Search n/a n/a 0.00000 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 ------------------------- --------- --------- ------- ---------- Exclusive Table 0.0 0.0 0 0.0 % Shared Table 0.0 0.0 0 0.0 % Exclusive Intent 0.0 0.0 4 44.4 % Shared Intent 0.0 0.0 0 0.0 % Exclusive Page 0.0 0.0 0 0.0 % Update Page 0.0 0.0 1 11.1 % Shared Page 0.0 0.0 4 44.4 % Exclusive Row 0.0 0.0 0 0.0 % Update Row 0.0 0.0 0 0.0 % Shared Row 0.0 0.0 0 0.0 % Exclusive Address 0.0 0.0 0 0.0 % Shared Address 0.0 0.0 0 0.0 % Shared Next-Key 0.0 0.0 0 0.0 % ------------------------- --------- --------- ------- Total Lock Timeouts 0.0 0.0 9
“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.
