FIX: Performance problems occur when database lock activity increases in SQL Server
- Elevated values occur for LOCK_HASH spinlock count.
Note See the "More Information" section for information about how to monitor this spinlock.
- Queries or operations that require database locks take a long time to be completed. For example, you may notice the following performance delays:
- SQL Server logins
- Linked server queries
Cumulative update informationThe issue was first fixed in the following cumulative update of SQL Server.
About cumulative updates for SQL Server
Hotfix information A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing this specific problem.
If the hotfix is available for download, there is a "Hotfix Download Available" section at the top of this Knowledge Base article. If this section does not appear, submit a request to Microsoft Customer Service and Support to obtain the hotfix.
Note If additional problems occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and problems that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, visit the following Microsoft website: Note The "Hotfix Download Available" form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.
On the computer that has 16 or more CPUs, only table objects use a partitioned lock scheme. However, the database locks are not partitioned. Therefore, the larger the number of database locks, the longer it takes for SQL Server to obtain a lock on the database. Most applications do not experience any problems that are caused by this design. But as soon as the number exceeds a certain threshold, additional work and time is required to obtain the lock. Although the cost is only micro seconds for each additional lock, the total time can quickly increase because the lock hash buckets are protected by using a spinlock. This causes additional CPU cycles and waits for additional workers to obtain the lock.
This hotfix introduces DATABASE lock partitioning when trace flag T1236 is enabled at startup. Partitioning the DATABASE lock keeps the depth of the lock list manageable in each local partition. This significantly optimizes the access path that is used to obtain a DATABASE lock.
To monitor the LOCK_HASH spinlock, you can use the following query.
SET NOCOUNT ONCREATE TABLE #spinlock_stats([CaptureTime] datetime,[name] nvarchar(512),[collisions] bigint,[spins] bigint,[spins_per_collision] real,[sleep_time] bigint,[backoffs] int)DECLARE @counter int = 1WHILE @counter < 100 BEGIN INSERT INTO #spinlock_stats SELECT GETDATE() as "CaptureTime" , * FROM sys.dm_os_spinlock_stats WHERE [name] = 'LOCK_HASH' WAITFOR DELAY '00:00:05' SET @counter +=1 ENDSELECT * FROM #spinlock_stats ORDER BY [CaptureTime]DROP TABLE #spinlock_statsFor more information about diagnosing and resolving spinlock contention on SQL Server, go to the following document:
Note Although this document is written for SQL Server 2008 R2, the information is still applicable to SQL Server 2012.
For more information about how to find the number of database locks in user per database, use the following query to calculate this value:
select Resource_database_id, resource_type, request_mode, request_status, count (*) 'LockCount' from sys.dm_tran_locks group by Resource_database_id, resource_type, request_mode, request_status
Article ID: 2926217 - Last Review: 12/03/2015 05:56:00 - Revision: 14.0
- kbqfe kbfix kbsurveynew kbexpertiseadvanced kbautohotfix kbhotfixserver KB2926217