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)
11 Comments. Leave new
I believe this can be set with a group policy object to save steps during deployment.
Good idea Don.
Hello Pinal,
Do you accept questions specific to problems people are having with SQL? I have a specific scenario that I am trying to resolve. If you are available to field a question let me know and I will send you the details.
In the mean time I will continue to try to come up with a solution on my own.
Thank you,
James Lloyd
[personal information removed]
James – you can post the question and I can see if I find some time to answer. Else you can also use MSDN SQL Forums to get more experts looking at your question.
This is a great tip! By the way, I have found that the “Optimize for background processes” setting along with the power options shown above work magic on a server that just runs SQL Server and nobody ever logs into (If you do login to such a server, obviously the UI interactions will be slower than expected).
Agree.
2 questions:
1. Does this setting have any meaning in a virtual environment?
2. What is the power usage difference between these 2 modes? Why should the high performance mode be used only when necessary?
1. Don’t know.
2. Don’t know. You need to test it.
Excellent contribution!
I will be conducting tests on my development SAP servers!
I name this setting ‘the performance-kill switch’. And it regularly happens to be the silver bullet to solve performance problems.
There is also a setup in some BIOS’es which seems to take precedence over the Windows setting.
Very True @Alain