Summary Information |
|
Default value |
2048 |
Range of values |
1–2147483647 |
Status |
Static |
Display Level |
Comprehensive |
Required Role |
System Administrator |
The lock hashtable size parameter specifies the number of hash buckets in the lock hash table. This table manages all row, page, and table locks and all lock requests. Each time a task acquires a lock, the lock is assigned to a hash bucket, and each lock request for that lock checks the same hash bucket. Setting this value too low results in large numbers of locks in each hash bucket and slows the searches.On Adaptive Servers with multiple engines, setting this value too low can also lead to increased spinlock contention. You should not set the value to less than the default value, 2048. lock hashtable size must be a power of 2. If the value you specify is not a power of 2, sp_configure rounds the value to the next highest power of 2 and prints an informational message.The optimal hash table size is a function of the number of distinct objects (pages, tables, and rows) that will be locked concurrently. The optimal hash table size is at least 20 percent of the number of distinct objects that need to be locked concurrently. See “Lock management” on page 269 of the Performance and Tuning: Monitoring and Analyzing for more information on configuring the lock hash table size.
However, if you have a large number of users and have had to increase the number of locks parameter to avoid running out of locks, you should check the average hash chain length with sp_sysmon at peak periods. If the average length of the hash chains exceeds 4 or 5, consider increased the value of lock hashtable size to the next power of 2 from its current setting.
The hash chain length may be high during large insert batches, such as bulk copy operations. This is expected behavior, and does not require that you reset the lock hash table size.
