Power Saving Options on SQL Server
Power Saving Options on SQL Server
Windows 2008 and Windows 2008 R2 have settings for “Power Options”. Windows 2008 R2 has additional power improvements related to “core parking” (temporarily suspending certain cores) and “tick skipping” (extended idle and sleep states). The default power setting for Windows 2008 and 2008 R2 is “Balanced Power” which means that when the system isn’t “busy” various components, such as the CPU and storage, are throttled back. This only happens when the box has CPUs and a BIOS that supports the ACPI specification for power that allows these options to take effect. Newer model CPUs are more likely to support this specification. Database systems that don’t have high sustained CPU usage are probably going to perform better with “High Performance”. Most people size their SQL Server boxes so that the CPUs run at a fairly lower percentage of total available so it’s pretty easy to drop down to the slower CPU speeds and get slow, inconsistent performance. There are many documented cases of “Balanced Power” hampering SQL Server performance. For a SQL Server box if you want consistent, predictable, high performance you either need to fine tune the power setting parameters for each individual workload and/or for different times of day or just set the power options to high performance. If power savings are more important than fast, predictable performance then you can leave it at “Balanced Power”. As a side note that isn’t directly related to SQL Server but may affect virtualized SQL Servers and/or application servers that connect to SQL Server, VMWare has a doc on the need for “High Performance” power settings on the VM host.
SQL
· SQL Server on Power-Saving CPUs? Not So Fast. https://www.brentozar.com/archive/2010/10/sql-server-on-powersaving-cpus-not-so-fast
· Are your CPUs running slowly? Tool tip and survey https://www.sqlskills.com/BLOGS/PAUL/post/Are-your-CPUs-running-slowly-Tool-tip-and-survey.aspx
· Windows OS Power Saving Feature Directly Affects CPU Performance https://www.bradmcgehee.com/2010/07/windows-os-power-saving-feature-directly-affects-cpu-performance/
Virtualization
· Poor virtual machine application performance may be caused by processor power management settings https://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1018206
General
· Degraded overall performance on Windows Server 2008 R2 https://support.microsoft.com/kb/2207548
“applications or scripts that intensively use resources (primarily processor and memory) may exhibit the problem”
· Improve Energy Efficiency and Manage Power Consumption with Windows Server 2008 R2 https://download.microsoft.com/download/5/B/D/5BD5C253-4259-428B-A3E4-1F9C3D803074/WS08%20R2%20Power%20Savings%20White%20PaperTDM.docx
“The “High Performance” mode may be appropriate for servers that run at very high utilization and need to provide maximum performance, regardless of power cost.”
· Performance Tuning Guidelines for Windows Server 2008 https://download.microsoft.com/download/9/c/5/9c5b2167-8017-4bae-9fde-d599bac8184a/Perf-tun-srv.docx
“However, Balanced might not be appropriate for all customers. For example, some applications require very low response times or very high throughput at high load. Other applications might have sensitive timing or synchronization requirements that cannot tolerate changes in processor clock frequency. In such cases, changing the power plan to High Performance might help you to achieve your business goals. Note that the power consumption and operating cost of your server might increase significantly if you select the High Performance plan.”
To view/set
· Powercfg.cpl
· Powercfg.exe - Configure Power Plans https://technet.microsoft.com/en-us/library/dd744398(WS.10).aspx
· Ensuring Maximum CPU Performance via Group Policy https://greg.blogs.sqlsentry.net/2011/01/ensuring-maximum-cpu-performance-via.html
PBM Policy / WMI query to see which CPUs are in power-saving mode https://www.sqlservercentral.com/scripts/CPU/72156/
· From Powershell: gwmi -class win32_Processor | SELECT CurrentClockSpeed, MaxClockSpeed