FIX: SQL Server service performs more I/O operations on the mirror server than on the principal server

Symptoms
Assume that you have a database mirroring setup between a principal server and a mirror server and the principal and mirror servers are running Microsoft SQL Server 2005, Microsoft SQL Server 2008, or Microsoft SQL Server 2008 R2. When you upgrade the servers to SQL Server 2012 or SQL Server 2014, the mirror server performs more I/O requests in SQL Server 2012 or SQL Server 2014 than in earlier versions of SQL Server, and the mirror server performs much more I/O requests than the principal server.

Note This issue exists in versions of SQL Server that are older than SQL Server 2012. Fixes for other versions will be released later.
Cause
When the log blocks are processed on the mirror server, the database mirroring threads issue read-ahead requests to fetch changed database pages from disk to the buffer pool.

In versions earlier than SQL Server 2012, a request that obtains an 8-kilobyte (KB) database page upgrades to a 64-KB I/O request. Therefore, you have to fetch more pages from disk to the buffer pool in fewer I/O requests.

In SQL Server 2012 and SQL Server 2014, the 8-KB database pages are fetched from disk to the buffer pool by using individual 8-KB I/O requests. Therefore, you find the I/O system is saturated with too many 8-KB I/O requests.
Resolution

Cumulative Update information

After you apply the cumulative update, you will notice that the read-ahead requests for single 8KB database pages upgrade to 64 KB I/O requests.
The issue was first fixed in the following cumulative update of SQL Server. Recommendation: Install the latest cumulative update 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. We recommend that you download and install the latest cumulative updates for SQL Server:

Workaround
To work around this issue, you can disable read-ahead for the database pages on the mirror server by using the Trace Flag 652. However, we don't recommend this method. It can affect query performance when the mirror server encounters a failover and assumes the principal role.
More information
  • The same problem exists in SQL Server 2012 and SQL Server 2014 for log shipping secondary servers. However, this Cumulative Update doesn't address the problem for log shipping scenarios.
  • This problem doesn't exist in SQL Server 2012 and SQL Server 2014 for Always-On Availability Group secondary replicas.
  • This Cumulative Update only fixes excessive read requests that occur on the mirror server. If you encounter excessive write requests on the mirror server, you can consider using Trace Flag 3499 to avoid writes to database pages for every change that's applied to the database page.
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Properties

Article ID: 3103472 - Last Review: 12/22/2015 02:45:00 - Revision: 2.0

Microsoft SQL Server 2012 Service Pack 2, Microsoft SQL Server 2014 Service Pack 1, Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Standard

  • kbqfe kbfix kbsurveynew kbexpertiseadvanced KB3103472
Feedback