Starting with Windows Server 2008 R2, Microsoft has introduced option for customizing the CPU Power Plan. By default, Windows Server 2008 R2 or higher comes with Balanced Power plan which basically means, that the processor will be using less power thus the performance is slower for regular workloads on the server. When ever there is higher work load on the system, the processor increases its speed, but this change from lower speeds to higher speeds is not very fast, thus can cause problems for applications which immediately require high speed immediately. There is another power plan which is “High Performance” which basically run the processor with high speed and with high performance all the time, so there is no need to switch the processor speed from lower to higher.
SQL Server is an application which relies heavily on server resources like CPU, Memory, Disk IO and Network. SQL Server generally benefits with servers running with “High Performance” power plan, so that when the SQL Server work load increases there is no issues noticed.
Although this setting looks simple, it is very effective and can dramatically show the improvement in performance and I have seen this on multiple servers and multiple environments. Most of the Article, Blogs, MVPs suggest to set CPU power plan to “High Performance”. On servers where High CPU usage is noticed, it is important to make sure this option is set to “High Performance”. Also, if you notice queries taking more time during high load and queries showing waits related to CPU, then it is good to check this setting and set it to “High Performance”.
There are various places where we may need to change the power plan settings to “High Performance” for it to take effect.
– In Windows Server 2008 R2, you can find the Power plan Options from Control Panel or by typing powercfg.cpl from Run window. There are three default power plans namely Balanced, Power saver, and High performance. By default, Windows Servers are set to Balanced, but as mentioned before the default Balanced power plan is not the best choice for database server performance. So, we need to change the power plan to “High Performance” from here.
– There may be servers which have power plan at BIOS level and it take precedence If Power options are enabled at BIOS level, then work with System Administrators and either disable this from BIOS or turn this on from BIOS.
– On Virtual servers, VMWare or Hyper-V, it is important that this Power plan setting to changed at individual guest VM systems and also at the ESX host server hosting the VMs. Also, important to note that this may need to be changed from control panel and also from BIOS on VM Guest and Host servers.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.