A lot of times, I have seen DBA’s and administrators have their own startup tasks that they perform when rebuilding or bringing a new server online. Some of these practices are powerful and are mean to enhance performance of the box that you have bought. Recently, I was at a customer location looking at what a typical Admin was doing and this step of his caught my attention. I couldn’t stop myself from blogging this because it was a simple, powerful and yet less appreciated setting available on the Server. Let us learn Power Plan for SQL Server.
On Windows Server 2008 and above, set the “High Performance” power plan in Control Panel -> Power Options -> OK. By default, Windows Server sets the “Balanced” power plan, which enables energy conservation by scaling the processor performance based on current CPU utilization. From Intel X5500 and other last-generation CPUs, the clock is throttled down to save power (Processor P-state), and only increases when CPU utilization reaches a certain point. The Minimum and Maximum Processor Performance State parameters are expressed as a percentage of maximum processor frequency, with a value in the range 0 – 100.
If a server requires ultra-low latency, invariant CPU frequency, or the very highest performance levels, such as a database servers like SQL Server, it might not be helpful that the processors keep switching to lower-performance states. As such, the High Performance power plan caps the minimum processor performance state at 100 percent.
The typical setting looks like:
At this point, thought it would be helpful in bringing out what these plans are for my readers. These are the built-in power plans and their common use case scenarios:
| Plan | Description | Common applicable scenarios | Implementation highlights |
| Balanced | The default setting. Highest energy efficiency with minimum performance impact. | General computing. | Matches capacity to demand. Energy-saving features balance power and performance. |
| High Performance | Increases performance at the cost of high energy consumption. Should not be used unless absolutely necessary. | Low latency.Application code sensitive to processor frequency changes. | Processors are always locked at the high performance state. |
I hope most of you are aware and are using these settings on your servers too. Do let me know some of the settings that you use as part of your daily environment. I am sure there will a few things I can learn from you too. Do drop a line as part of comments for the benefit of all.
Reference: Pinal Dave (https://blog.sqlauthority.com)
