Power Settings are important to SQL Server Performance!

Not long ago we had a client that was constantly having a problem with a secondary replica server that couldn’t keep up with the traffic from the primary. It would work fine for a few days, and then suddenly it was hours behind and never catching up. When looking at Availability Group redo rates, one server would be redoing at 45Mb/sec and the other only 25Mb/sec. We tried trace flag 9592 to no avail. We looked at EVERYTHING we could think of but found nothing that would indicate it was a SQL Server problem.

After looking at the OS level, we could see that the resources just weren’t performing as well as they should be, mainly the NIC, processor and storage. While the Windows Server OS was set to High Performance in the Power Plan, it turns out that the server was a VM on Hyper-V and the host was set to a Balanced plan in the BIOS! This meant that the host started to see periods of lower consumption and was reducing the power to the hard resources…but the VM never tried to push past a threshold to tell the host that it needed to boost it back up.

So this is a reminder to always request that your infrastructure partners check the hosts that are running your SQL Servers to make sure they meet your requirements. They should always be set to High Performance at the BIOS level (which overrides all other downstream settings) when you have SQL Server workloads running.

For more in-depth reading, please see a post from MSSQLTips.com where they explain the issue and do great benchmark testing.