SQL Server performance – death by power options

Oopps! Upgrade your browser pretty please.Oopps! Upgrade your browser pretty please.

If your SQL server isn’t constantly busy you might experience unpredictible performance. Why? Well, you might have missed the small detail in Windows, the power options. The default setting for power option in Windows is ”balanced power”, and that setting is forcing the hardware, like CPU and storage to throttle down to save power when the system is less busy!

To make things even worse, if you are using Windows Server 2008R2, you have some new power saving options, “core parking” (temporarily suspending certain cores) and “tick skipping” (extended idle and sleep states).

If you care more about performance and less about the environment, you should change the power option to ”High performance”.

Why is the default setting, balanced power? I may only guess, but a default action when installing a SQL server should be changing the option, not only in Windows but also in BIOS if the BIOS have support for power saving.

  

You can read more about the power option death at:

http://www.brentozar.com/archive/2010/10/sql-server-on-powersaving-cpus-not-so-fast/

http://www.sqlskills.com/blogs/paul/post/are-your-cpus-running-slowly-tool-tip-and-survey.aspx

http://www.bradmcgehee.com/2010/07/windows-os-power-saving-feature-directly-affects-cpu-performance/

or if you are living in the virtual world, read this:

http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1018206