FIX: High CPU usage on Primary when SQL Service on Readable Secondary is turned off in Availability Group in SQL Server 2014

Applies to: SQL Server 2014 DeveloperSQL Server 2014 EnterpriseSQL Server 2014 Enterprise Core

Symptoms


Assume that you use Availability Groups (AG) in Microsoft SQL Server 2014. When the SQL Service on Readable Secondary in AG is turned off, you may notice that the CPU usage on the Primary increases exponentially. Additionally, there is also an increase in Log bytes flushed/sec, Active sessions and signal waits.

Cause


The reason for the higher CPU usage on the Primary is due to the buildup of ghost records.

Status


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

Resolution


This issue is fixed in the following cumulative update for SQL Server:

Note This fix will implement the option for more aggressive ghost cleanup by reducing the ghost cleanup interval from 30 minutes to every 5 minutes
The following Trace Flag (TF) 12302 needs to be enabled as a SQL startup parameter or via DBCC to put the fix into effect.

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:

References


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