KB2926217 - FIX: Performance problems occur when database lock activity increases in SQL Server

Symptoms

Assume that you run an instance of Microsoft SQL Server 2014, SQL Server 2012, SQL Server 2008, or SQL Server 2008 R2 on a computer that contains many processors. When the number of locks (resource type = DATABASE) for a specific database exceeds a certain threshold, you experience the following performance problems:

• 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

  • sp_reset_connection

  • Transactions

Note To locate the list of locks (resource type = DATABASE) on a given database, see the "More Information" section. The threshold value varies by environment.

Resolution

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:

http://support.microsoft.com/contactus/?ws=supportNote 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.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

More Information

When an application makes a connection to SQL Server, it first establishes a database context. By default, the connection will try to obtain a DATABASE lock in SH mode. The SH-DATABASE lock will be released when the connection is stopped or database context is changed during the lifetime of the connection. If you have many active connections that use the same database context, you can have many locks of the DATABASE resource type for that specific database.

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 ON
CREATE TABLE #spinlock_stats([CaptureTime] datetime,[name] nvarchar(512),[collisions] bigint,
[spins] bigint,[spins_per_collision] real,[sleep_time] bigint,[backoffs] int)
DECLARE @counter int = 1
WHILE @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
END
SELECT * FROM #spinlock_stats ORDER BY [CaptureTime]
DROP TABLE #spinlock_stats For more information about diagnosing and resolving spinlock contention on SQL Server, go to the following document:

Diagnosing and Resolving Spinlock Contention on SQL ServerNote Although this document is written for SQL Server 2008 R2, the information is still applicable to SQL Server 2012.

References

For more information about trace flags in SQL Server 2012, go to the following TechNet website:

Information about trace flags in 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