Customer Focused Testing: Ten steps to improved SQL Server 2005 replication (5697-7434, March 2008)

When examining the impact of HBA queue length in the mirroring solution, it is important to gauge the
performance of both the Principal and the Mirror servers. In this configuration, the Mirror server is
involved in most of the strenuous disk operations; therefore it was more important to monitor the disk write
response time on the Mirror than on the Principal server. For example, definite benefitscanbeseenwhen
increasing the execution throttle on the Mirror server, potentially resulting in a noticeable difference in the
transaction per second processed by the solution.
NOTE:
In the mirroring solution, there are more disk I/Os queued at the HBA on the Mirror server than the
Principal server. This is due to the different write patterns between the two server roles. The redo queue
on the Mirror server is processed consistently from the transaction log to the data files and this is done
sequentially. For this reason, queues are expected to be larger than that of typical random disk I/Os
from client updates which normally are received by the Principal server.
NOTE:
You may be tempted to increase the I/O buffer settings on multiple servers on a SAN to aid server
performanceingeneral.IftoomanyservershavealargeI/ObuffersettingontheirHBAsaccessinga
common SAN, this will be at the detriment of the unmodified servers and the general SAN performance
as the modified server HBA will take precedence on the SAN and could dominate the resources.
6 Tune the SQL Server Checkpointing
Customize the SQL Server checkpointing process to improve data-writing response time and performance
when using either Continuous Access replication or SQL Server database mirroring for both synchronous
and asynchronous modes. In synchronous replication, customizing the checkpointing process improves
response times for Continuous Access and delivers improved transactional performance for both
Continuous Access and SQL Server database mirroring. In asynchronous replication, it improves
transactional performance for both Continuous Access and SQL Server database mirroring. This
improvement becomes more significant as the workload increases.
Microsoft SQL Server uses a recovery interval to determine when to issue a checkpoint for a database
in order to speed the recovery process after a database has had an outage. Effectively, the recovery
interval setting is the default method of controlling the checkpointing, but the checkpoint command also
can be executed manually or via a script. By configuring the checkpoint duration parameter, it is possible
to control the elapsed time of the checkpoint operation. By designating a specific length of time, it is
possible to manage the pattern of write operation trafficontheISL.
The checkpointing operation causes significant write operations over the ISL at the beginning of its cycle,
but these operations decrease to near zero at the end of each cycle. The ability to distribute the data
more evenly over the complete period of the checkpoint will provide a more even disk response time,
resulting in more consistent SQL Server transactional performance.
When you are replicating or mirroring data between two sites over an ISL, it is worthwhile to experiment
with the checkpointing, and specifically with the duration times. However, extending the checkpoint
intervals and durations even further in order to smooth out the ISL traffic and achieve better performance
will increase the database recovery time. These parameters provide a compromise for balancing
acceptable disk write response time and acceptable recovery time.
The reason for the performance improvement is different for the Continuous Access solution. Both
solutions rely on data being written to disk on both the primary and secondary site storage systems.
Since the Continuous Access solution replicates all disk writes (data and transaction log), it generates
a lot of write activity; therefore, the ISL becomes the bottleneck. Checkpointing customization helps
relieve this ISL bottleneck.
As the SQL Server database mirroring solution simply copies the transaction log updates, the actual
amount of data being written to disk and subsequently sent over the ISL is relatively low. However,
4