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

Notice
By default, Service Pack 1 for SQL Server 2014 and Service Pack 3 for SQL Server 2012 include this fix and you do not have to add any trace flags to enable the fix. To enable the fix after you install one of the cumulative updates in the Resolution section, you have to start Microsoft SQL Server by adding trace flag 1236 to startup parameters.
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

Cumulative update information

The issue was first fixed in the following cumulative update of SQL Server.

Cumulative Update 13 for SQL Server 2008 R2 SP2

Cumulative Update 17 for SQL Server 2008 SP3

Cumulative Update 1 for SQL Server 2014

Cumulative Update 9 for SQL Server 2012 SP1

About cumulative updates for SQL Server

Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest 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.

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 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_stats
For 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.
References
For more information about trace flags in SQL Server 2012, go to the following TechNet website:


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


Properties

Article ID: 2926217 - Last Review: 12/03/2015 05:56:00 - Revision: 14.0

Microsoft SQL Server 2012 Enterprise, Microsoft SQL Server 2012 Developer, Microsoft SQL Server 2012 Standard, Microsoft SQL Server 2012 Express, Microsoft SQL Server 2012 Web, Microsoft SQL Server 2012 Service Pack 1, Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Standard, Microsoft SQL Server 2008 Service Pack 3, Microsoft SQL Server 2008 R2 Service Pack 2

  • kbqfe kbfix kbsurveynew kbexpertiseadvanced kbautohotfix kbhotfixserver KB2926217
Feedback