Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Windows Server Power Management Effect on SQL Server


By:   |   Read Comments (1)   |   Related Tips: More > Performance Tuning

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

I have a SQL Server 2012 installed on Windows Server 2008 R2 Enterprise Edition. The power plan of the Windows Server Operating System (OS) is left at its default power plan of Balanced.  Are there any benefits or performance gain in setting the power plan to High Performance even when the CPU is not under any pressure?

Solution

Online transaction processing applications are typically high throughput and insert or update-intensive. These applications are used concurrently by hundreds of users. By having the power plan left at Balanced, a server with low utilization can have the server component such as its CPU throttled back to save on power consumption.

The benefit to set the server power management to high performance is for SQL Server to have consistent, predictable and high performance. This additional processing capacity comes with higher power utilization. Hence, you will need to evaluate if your server requires very low response time and high throughput at any given time versus the increase of cost due to server power consumption when you select the High Performance power plan.

Windows Server Power Options Background

The Windows Server OS Power Options form is accessible from the Control Panel on Windows 2008 R2 and higher. The default power plan is Balanced by default.

Windows Server OS Power Options form is accessible from Control Panel

The server hardware used in my testing is a Dell PowerEdge R610 with dual socket Intel(R) Xeon X5650 CPUs @ 2367GHz.

I have set the server power management in the server BIOS to "OS Control". Basically this option will allow the server power management to be set from the Windows Server OS. Note that server BIOS Power Management setting overrides the Windows Server power plan. Should the server BIOS power management be set to Maximum Performance, then the Windows Server power plan of Balanced or High Performance would be irrelevant as it will always run in High Performance mode.

The server hardware used in my testing is a Dell PowerEdge R610 with dual socket Intel(R) Xeon X5650 CPUs @ 2367GHz.

Demonstration of Effects

I am running Windows Server 2008 R2 Enterprise Edition with the default power plan of Balanced.

To illustrate the impact of the power plan, I will be comparing the performance of a light-weight query on the server under both power plans against the AdventureWorks2012 database. In this demonstration, all hardware and software resources will remain constant with the only change made in each test being the Windows Server power plan setting.

The sample query used for this test is as below. The query will be executed a number of times under Windows Server OS power plan of Balanced and High Performance.

SELECT DISTINCT pp.LastName, pp.FirstName 
FROM AdventureWorks2012.Person.Person pp JOIN AdventureWorks2012.HumanResources.Employee e
ON e.BusinessEntityID = pp.BusinessEntityID WHERE pp.BusinessEntityID IN 
 (SELECT SalesPersonID 
 FROM AdventureWorks2012.Sales.SalesOrderHeader
 WHERE SalesOrderID IN 
  (SELECT SalesOrderID 
  FROM AdventureWorks2012.Sales.SalesOrderDetail
  WHERE ProductID IN 
   (SELECT ProductID 
   FROM AdventureWorks2012.Production.Product p 
   WHERE ProductNumber = 'BK-M68B-42')))
OPTION (MAXDOP 1);
GO

SET STATISTICS TIME will be used to display the number of milliseconds (ms) required to parse, compile, and execute the query.

A brief explanation of SET STATISTICS TIME is as below:

  • SQL Server parse and compile time
    • Parse time is the time spent during checking SQL statement for syntax errors, breaking the command up into component parts, and producing an internal execution tree.
    • Compile time is time spent during compiling an execution plan in cache memory from the execution tree that has just been produced.
  • SQL Server Execution Time
    • Execution time is the total time spent during execution of compiled plan.

Test 1- Windows Server power plan with the Balanced Power Option

For additional information, I have downloaded CPUID to check the CPU specification on the server. Under the Balanced power plan, the CPU core speed is 1.2GHz. This is where the CPU power saving kicks in by reducing the frequency multiplier (Core Multiplier) and the voltage (Core VID) when the server CPU is not under load. The combination of both multiplier and voltage is known as performance states (p-states) and this is what makes up a server power management profile.

Test 1- Windows Server power plan under Balanced

The result for the first query execution (Run 0) is discarded as it is meant to warm up the cache. The query is then subsequently executed 7 more times with results captured as below

  SQL Server parse and compile time SQL Server Execution Time
  CPU time elapsed time CPU time elapsed time
Run 1 172 175 16 10
Run 2 171 184 16 10
Run 3 188 193 15 10
Run 4 218 230 16 16
Run 5 181 181 0 9
Run 6 187 187 0 9
Run 7 194 194 0 10

Test 2- Windows Server power plan under High Performance Power Option

When the Windows Server power plan is changed to High Performance, the CPU core speed now ramp up to 3.3GHz. You can see that CPU Clocks Multiplier is now at its maximum and the Core VID has also increased.

When the Windows Server power plan is changed to High Performance

The same query is run again and query parse and compile time is recorded as below

  SQL Server parse and compile time SQL Server Execution Time
  CPU time elapsed time CPU time elapsed time
Run 1 109 110 0 6
Run 2 115 115 0 6
Run 3 109 110 0 6
Run 4 120 120 0 6
Run 5 109 113 16 6
Run 6 108 108 0 6
Run 7 109 114 0 6

Conclusion

Under Windows OS High Performance power plan, the results are more consistent with the SQL Server parse and compile time closer to around 110ms compared to between 180ms and 200ms under the Balanced power plan. The overall SQL Server Execution Time has also improved from around 10ms to a more consistent 6ms. You may say there is 40% improvement, but mileage varies and this percentage is not linear where you expect a process that runs for 60 minutes would complete in 36 minutes. Assuming all other factors are constant, the power plan might reduce an hour process to 50 minutes for instance.

If power saving is a higher priority than fast and predictable performance, you might leave the power plan as Balanced. There could be an applicable scenario where a server is not used frequently and is left under the default Balanced power plan to conserve energy.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Simon Liew Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Masterís Degree in Distributed Computing.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, November 06, 2014 - 10:11:31 AM - Adam S Back To Top

If you want to set your power plan via powershell try this...

<# Select current powerplan #>
Get-WmiObject -Namespace root\cimv2\power -Class win32_PowerPlan | Select-Object -Property ElementName, IsActive | Format-Table -Property * -AutoSize;

<# Set powerplan to high performance #>
Try {$HighPerf = powercfg -l | %{if($_.contains("High performance")) {$_.split()[3]}}; $CurrPlan = $(powercfg -getactivescheme).split()[3]; if ($CurrPlan -ne $HighPerf) {powercfg -setactive $HighPerf}} Catch {Write-Warning -Message "Unable to set power plan to high performance"};

<# Select current powerplan #>
Get-WmiObject -Namespace root\cimv2\power -Class win32_PowerPlan | Select-Object -Property ElementName, IsActive | Format-Table -Property * -AutoSize

 

Learn more about SQL Server tools