Click on the banner to learn about and purchase my database training on Azure

SQL Server - How to speed up your queries by changing server power setting

Reading Time: 6 minutes

Speak guys!

In this post I would like to share with you how to speed up your queries by changing the server power setting

Introduction

If you follow the IT technical community, especially SQL Server, you certainly know the Fabrício LimaCEO of Power Tuning and one of the pioneers when it comes to blogging about SQL Server in Brazil.

A few years ago he reported a very interesting case in the article “Everyday Cases: Bought a better server and SQL Server is slower! How can???” where a customer buys a much better server than the old one and queries end up being much slower than on the old server because of the Windows power plan.

Since then, I always analyze this when I go to access a new environment. Although I believe all the references he cites in his post (and I also believe him lol), I wanted to test this in my own environment and draw my conclusions about it.

The test environment

For the tests of this post, I will use the following table:

The query I will use to test performance on different power plans will be this:

The tool I will use to run several queries simultaneously and measure execution times is SQLQueryStress, which I already talked about in the article SQL Server - How to use the SQLQueryStress tool.

To manage my power plans, enable/disable Turbo Boost and manage my processor frequencies, I use the tool quick cpu, which I think is the best available for Windows for processor management.

Finally, the processor used in this test is an Intel i9 9900K with 8 cores and 16 logical cores, with Turbo Boost 2.0 enabled, where I set a maximum frequency of up to 4.7 GHz.

The tests

Using the SQLQueryStress tool, I'm going to run the query I shared above (which forces to use all 16 cores) in 10 iterations, running 2 times simultaneously on each iteration.

I added a 2 second wait time between each query, to let the processor “rest” and reduce the clock.

And the result was this one:

Using the Energy Saver power plan:
59.21 seconds total runtime, 3.91 seconds average runtime, and 18.5 seconds CPU (parallelism) on each iteration

Using the High Performance power plan:
43.64 seconds total runtime, 2.33 seconds average runtime, and 10.56 seconds CPU (parallelism) on each iteration

As you can see, on the High Performance power plan, we had a reduction in total duration of 21%, reduction in average execution time of 40.4% and reduction in CPU time of 43%.

What is happening?

To try to understand what could be causing such an absurd difference in performance due to a simple Windows power plan configuration, let's understand how these power plans work. According to official documentation, we have 3 power plans by default in Windows:

  • Balanced: This plan gives you full performance when you really need it. This plan saves energy during periods of inactivity.
  • Energy saving: This plan saves energy by reducing system performance. It can help mobile PC users get the most out of a single battery charge.
  • High performance: This plan maximizes system responsiveness and performance. Mobile PC users may find that the battery charge doesn't last as long when they use this plan.

To understand exactly what these Windows power plans are different from each other, we can view the advanced settings for each power plan:

Power Savings (minimum CPU usage at 5%, maximum usage at 100% and passive cooling)

Balanced (minimum CPU usage at 5%, maximum usage at 100% and active cooling)

High Performance (minimum CPU usage at 100%, maximum usage at 100% and active cooling)

As we can see, the biggest difference is regarding the cooling (active/passive) and the minimum CPU usage. While in “Balanced” and “Power Saving” modes, the minimum usage is 5% and the CPU clock frequency will vary from 5% to 100% of the maximum capacity, depending on usage, in “High Performance” power mode , Windows will always use maximum CPU capacity at all times.

In “Balanced” and “Power Saving” power modes, if the bank server has periods where it has little activity and it suddenly gets some heavy queries, it will end up having to ramp up the processor speed when that heavy query arrives and then After running, it will automatically reduce clock to save power.

In my tests with Turbo Boost enabled, the “Power Saver” power plan only ramped the processor frequency up to the processor's rated maximum (3.6 GHz) and hit lows of 1.4 GHz. The “Balanced” power plan reached 4.7 GHz when I ran some queries and after running it, it was varying between 3.5 GHz and 4.5 GHz. In the “High Performance” plan, the clock ranged between 4.5 and 4.7 GHz.

This is what makes the difference in the “real world” when using a power plan other than “High Performance”

You can also use Quick CPU to analyze power plans in more detail:

Important: Remember to check if the “Turbo Boost” option is enabled (if your processor is Intel), as it increases the CPU clock frequency a lot automatically, increasing the speed of queries. This feature is native to the processor and is not considered overclocking, as it is done by the CPU itself, together with the motherboard.

How to identify and change the current power plan

The easiest way to identify and change your current power plan is using the Windows Power Plan Choice screen. To open this screen, open the DOS Prompt screen or the Run window (Windows + R) and type this:

You can also type “power plan” in the start menu

You can access the power options from the Control Panel as well:

Or even, using the Quick CPU tool

How to identify and change current power plan by SQL Server

If you do not have RDP access to the server and need to identify using only SQL Server, you can use the query below:

Result:

And to change the power plan also through SQL Server (the service user must have permission for this), you can use the command below:

References

And that's it folks!
I hope you enjoyed this post and see you next time!