The throughput for in-memory databases may produce contention for the lock manager hashtable spinlock ratios. The table lock hashtable, and the page and row lock hashtable spinlocks may contribute considerably to the contention.
The Lock Management section of sp_sysmon shows the percentage of contention for the spinlocks that govern hash buckets for these hashtables:
Lock Management --------------- Lock Summary per sec per xact count % of total ------------------------- ------------ ------------ -------- ------------ Total Lock Request 285063.3 43.0 17103795 n/a Avg Lock Contention 1857.3 0.3 111435 0.7 % Cluster Locks Retained 0.0 0.0 0 0.0 % Deadlock Percentage 0.1 0.0 8 0.0 % Lock Detail per sec per xact count % of total ------------------------- ------------ ------------ -------- ------------ Table Lock Hashtable Lookups 130160.4 19.6 7809622 n/a Avg Chain Length n/a n/a 0.00000 n/a Spinlock Contention n/a n/a n/a 4.6 % [...] Page & Row Lock HashTable Lookups 268968.1 40.6 16138085 n/a Avg Chain Length n/a n/a 1.03330 n/a Spinlock Contention n/a n/a n/a 4.8 %
Generally, if this contention is more than approximately 4 percent, consider reducing the ratio of these spinlocks to hash buckets. The default value for the configuration option controlling the number of hash buckets controlled by one spinlock is:
lock table spinlock ratio with a default value of 20 affects the spinlock contention on the Table Lock Hashtable output.
lock spinlock ratio with a default of 85 affects the spinlock contention on the Page & Row Lock Hashtable output.
When the spinlock contention is significant, reducing the values for lock table spinlock ratio and lock spinlock ratio may improve run-time performance. The additional memory overhead of more spinlocks controlling fewer hash buckets is not significant. Initially, reduce the configuration parameters by one half their values. In cases of extreme spinlock contention (in excess of 10 percent), reducing the appropriate configuration option to a very small value (say, .3 – 5), may help remove performance bottlenecks from spinlock overheads.
