Everyone knows that I love my job and particularly solving SQL Server Performance Problems. Recently I ended up a very interesting scenario when working with my client on Comprehensive Database Performance Health Check. After a careful investigation into their system, we learned that this issue was with Power Options. Let us learn about that in this blog post.
Sudden Slow Performance
My client’s server was running absolutely fine for a while and it was not facing any performance issues. However, suddenly, without making any changes the performance of the server went down. They immediately called me up and as they were in the time zone of Australia and New Zealand, I was available to help them for before my USA clients come online.
We started systematically to check their SQL Server Performance and realized that they have no CPU, IO or memory issues. Additionally, all the maintenance jobs were running efficiently as well. This leads us to finally investigate into their windows system.
High-Performance Power Options
The biggest puzzle in front of us was a strange one. We had a query which was earlier running for 1 second now taking over 8 seconds. We also investigated over 10 different queries and found they all have been running slow.power
As all the benchmarks of my SQL Server Healthchecks were clean. I decided to look into the windows configuration. After looking at the anti-virus, I opened the Power Option to find out that it was changed from High Performance to Balanced.
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.
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. You can read more about it here: SQL SERVER – Using “High Performance” Power Plan for SQL Server.
Summary
Once we changed the Power Option to the High Performance, we were able to restore the system’s performance to earlier benchmarked performance.
I strongly recommend everyone who is running SQL Server on their windows to keep their Power Plan as a High-Performance Plan to avoid any un-intended performance degradation.
Reference: Pinal Dave (https://blog.sqlauthority.com)
2 Comments. Leave new
Hi Pinal, With new WinOS we have now Ultimate performance option which is hidden by default can be enabled using Powershell script. This might help to boost performance much more when required.
On Intel processors up to a certain point, I vaguely recall Xeon E5/7 v3 (Haswell), it is not the operation at lower frequency that impacts SQL performance. Rather it the process of changing frequency that causes many hidden operations (flushing cache?). Supposedly this was fixed in Xeon E5/7 v4 and later, this was fixed so that changing frequency in balanced power mode does not have high overhead, but I did not verify this for myself.
In case any one is curious, in database transaction processing – queries using an index to find few rows (not executed from a pre-planned pattern) is memory round-trip intensive, and hence insensitive to processor frequency. Hence, dialing down processor frequency to a very low value should not substantially impact performance – test this to be sure though.