High Performance CPU Power Plan for SQL Server for Better Performance

SQLServerF1

It is common to determine and implement best practices while setting up new servers or while trying to identify and fix performance problems with SQL Server running on Windows Server 2008 R2 or higher. Some of these practices are effective and are meant to upgrade the hardware of the server if the server is not performing well as expected. However there are may tips and tricks which can be followed to improve the performance of the server instead of just upgrading the hardware to improve the performance. One of the common issues we generally see is high CPU usage on server running SQL Server instance.

On Windows Server 2008 or more, set the “High Performance” power plan in Control Panel from Power Options. Of course, Windows Server sets the “Adjusted” force arrangement, which empowers vitality protection by scaling the processor execution in view of current CPU use. From Intel X5500 and other last-era CPUs, the clock is throttled down to spare force (Processor P-state), and just increments when CPU use achieves a certain point. The Minimum and Maximum Processor Performance State parameters are communicated as a rate of greatest processor recurrence, with a quality in the reach 0 – 100.

In the event that a server obliges ultra-low inertness, invariant CPU recurrence, or the extremely most astounding execution levels, for example, a database servers like SQL Server, it may not be useful that the processors continue changing to lower-execution states. In that capacity, the High Performance force arrangement tops the base processor execution state at 100 percentage.

Also, it is important to keep in mind that changing this setting alone from control panel does not help, as some servers have this enabled at BIOS level, thus need to make the change at BIOS level too if enabled in BIOS. Also for VM systems, it is important to enable this at guest and host level and on BIOS on host server. Although this is a simple change, but shows greater improvement in performance of busy servers running SQL Server.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

CPU Power Plan Setting Best Practices for Servers Running SQL Server instances

SQLServerF1

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.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.