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.
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
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.
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:
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.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
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
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
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.
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