Article ID: 2926217 - View products that this article applies to.
Notice
To enable the fix, you have to start Microsoft SQL Server by adding trace flag 1236 to startup parameters.
Expand all | Collapse all

On This Page

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

Collapse this imageExpand this image
assets folding start collapsed
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:
Collapse this imageExpand this image
assets folding end collapsed

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=support
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 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 Server
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:

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


Properties

Article ID: 2926217 - Last Review: June 30, 2014 - Revision: 12.0
Applies to
  • 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, when used with:
    • Microsoft SQL Server 2012 Developer
    • Microsoft SQL Server 2012 Enterprise
    • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2014 Developer
  • Microsoft SQL Server 2014 Enterprise
  • Microsoft SQL Server 2014 Standard
  • Microsoft SQL Server 2008 Service Pack 3, when used with:
    • Microsoft SQL Server 2008 Developer
    • Microsoft SQL Server 2008 Enterprise
    • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 R2 Service Pack 2, when used with:
    • Microsoft SQL Server 2008 R2 Developer
    • Microsoft SQL Server 2008 R2 Enterprise
    • Microsoft SQL Server 2008 R2 Standard
Keywords: 
kbqfe kbfix kbsurveynew kbexpertiseadvanced kbautohotfix kbhotfixserver KB2926217

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com