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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE [dirceuresende] GO IF (OBJECT_ID('dbo.TesteCPU') IS NOT NULL) DROP TABLE dbo.TesteCPU SELECT TOP(10000) MyInt = CONVERT(BIGINT, o1.[object_id]) + CONVERT(BIGINT, o2.[object_id]) + CONVERT(BIGINT, o3.[object_id]) INTO dbo.TesteCPU FROM sys.objects o1 JOIN sys.objects o2 ON o1.[object_id] < o2.[object_id] JOIN sys.objects o3 ON o1.[object_id] < o3.[object_id] |
The query I will use to test performance on different power plans will be this:
|
1 2 3 4 5 6 |
SELECT SUM(CONVERT(BIGINT, o1.MyInt) + CONVERT(BIGINT, o2.MyInt)) FROM dbo.TesteCPU o1 JOIN dbo.TesteCPU o2 ON o1.MyInt < o2.MyInt OPTION(MAXDOP 16) -- Forçar usar todos os cores do meu servidor |
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:
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:
|
1 |
%windir%\system32\control.exe /name Microsoft.PowerOptions |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DECLARE @value VARCHAR(64) EXEC master.dbo.xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = 'SYSTEM\CurrentControlSet\Control\Power\User\PowerSchemes', @value_name = 'ActivePowerScheme', @value = @value OUTPUT; SELECT (CASE WHEN @value = '381b4222-f694-41f0-9685-ff5bb260df2e' THEN '(Balanced)' WHEN @value = '8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c' THEN '(High performance)' WHEN @value = 'a1841308-3541-4fab-bc81-f71556f20b4a' THEN '(Power saver)' END) |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
DECLARE @Fl_Xp_CmdShell_Ativado BIT = (SELECT (CASE WHEN CAST([value] AS VARCHAR(MAX)) = '1' THEN 1 ELSE 0 END) FROM sys.configurations WHERE [name] = 'xp_cmdshell') IF (@Fl_Xp_CmdShell_Ativado = 0) BEGIN EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE WITH OVERRIDE; END -- 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c (High Performance = Alta Performance) -- 381b4222-f694-41f0-9685-ff5bb260df2e (Balanced = Balanceado) -- a1841308-3541-4fab-bc81-f71556f20b4a (Power saver = Economia de Energia) EXEC sys.xp_cmdshell 'powercfg.exe /setactive 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c' -- High Performance IF (@Fl_Xp_CmdShell_Ativado = 0) BEGIN EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE WITH OVERRIDE; EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE WITH OVERRIDE; END |
References
- Everyday Cases: Bought a better server and SQL Server is slower! How can???
- Windows PowerPlans and CPU Performance
- PowerPlans and Windows Server 2008 R2
- SQL Server on Power-Saving CPUs? Not So Fast.
And that's it folks!
I hope you enjoyed this post and see you next time!
Good afternoon Dirceu… congratulations for the blog and the post, I started following you a few days ago and I'm finding the contents very interesting. I tried to implement the script for power change by SQL Server, I even managed to make the query and see what power is being used by windows, but changing the plan gave me that I don't have permission. I searched a lot on the internet to see if I could find what permission I need to have to make this change, but to no avail. Can you help me with this part? If the permission has to be the login or it has to be the windows permission? Thank you very much in advance.
very good, I'll do it on dev for testing!