FIX: High CPU usage causes performance issues in SQL Server 2016

Si applica a: SQL Server 2016 DeveloperSQL Server 2016 EnterpriseSQL Server 2016 Enterprise Core

Symptoms


When you use either a new instance of Microsoft SQL Server 2016 or an instance of SQL Server 2016 that's upgraded from an earlier version of SQL Server, you experience performance issues because of high CPU usage. Additionally, if you query the sys.dm_os_spinlock_stats table, you notice a high value for the spins column (compared to other rows in the table) that corresponds to the SECURITY_CACHE (Security Cache) and CMED_HASH_SET (Metadata Cache). For example, you notice the following values.

error

Cause


This issue occurs because of high spin lock contention in a highly concurrent system. In these operating systems, multiple CPU threads are competing for resources and spinning for an extended interval while they run in a loop periodically to determine whether the resource is available instead of immediately yielding. When multiple CPU threads keep spinning (in a while loop) for a resource instead of yielding, this causes high CPU usage that cause performance issues.

Resolution


This problem was fixed in the following cumulative updates for SQL Server:

Note After you apply SQL Server 2016 Cumulative Update 2 (CU2), the SECURITY_CACHE and CMED_HASH_SET names are changed to LOCK_RW_SECURITY_CACHE and LOCK_RW_CMED_HASH_SET, respectively. After CU2 is applied, the values appear as follows.

Updated

Status


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

References


Learn about the terminology that Microsoft uses to describe software updates.

The white paper Diagnosing and Resolving Spinlock Contention on SQL Server discusses issues and resolutions related to spinlock contention in detail.