Windows Server Power Management Effect on SQL Server

By: Simon Liew   |   Updated: 2014-11-06   |   Comments (1)   |   Related: > Performance Tuning

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.

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.

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.

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

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.

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

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

• Degraded overall performance on Windows Server 2008 R2
• Poor virtual machine application performance may be caused by processor power management settings (1018206)
• Getting IO and time statistics for SQL Server queries

About the author

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips