Optimizing Failover Cluster Performance

SQL Server 2008 R2

To optimize performance when using failover clustering, consider the following issues.

If your disk controller is not external to your clustered computer, you must turn off write caching within the controller to prevent data loss during a failover.

Write-back caching cannot be used on host controllers in a cluster without hindering performance. However, if you use external controllers, you continue to provide performance benefits. External disk arrays are not affected by failover clustering and can synchronize the cache correctly, even across a SCSI bus.

Do not use the same drive for file shares and clustered SQL Server databases. Using this drive impacts recovery time and can cause a failover of the cluster group in cases of a resource failure.

Failover clustering performance benefits from generous memory allocation. The theoretical maximum memory is the total memory available, less memory for the operating system and the other cluster and local-to-node installed resources, divided by the instances of Microsoft SQL Server installed. Determine and set your maximum memory size using this formula. We recommend that you set your working maximum memory to 80 to 90 percent of this amount, and then gather performance baseline statistics to further optimize the setting.

Minimum memory should be set where multiple SQL Server instances exist and maximum memory settings are in place. Setting the minimum memory size reduces failover time.

CPU affinity default settings are preferred in most situations. However, if your installation is exceeding the maximum recommended performance thresholds, set cluster CPU affinity using an N-1 scenario.

Use fixed IP ports and fixed memory settings.

Community Additions