SQL Server – performance and other stories: SQL Server, CPU and Power Policy

Monday, November 4, 2013

SQL Server, CPU and Power Policy – save the civilization

From the dawn of time, power has been linked with human history; in the past, present and future. The advancement of our species, the Homo sapiens, has been one of the most successful organisms on Earth to this day. The lighting of fire had fueled the creation for a modern world and that is what separated our evolutionary abled bodies from the ape species which existed before.

We produce power and consume it every nanosecond to continue through our daily lives. We also have concerns about using unnecessary power and we encourage others to reduce power so that we can save our planet and preserve it for our future generation. This makes sense.

We need to think carefully about how to use power and consume it effectively in SQL Server OLTP implantation.

OLTP and Windows Power Policy:

In cases, especially in OLTP and CPU-intensive application where concurrency is high, we want to make sure that the database server receives enough power to process each instruction without any latency. Saving some power in such cases is not an option as the power consumption directly affects CPU, which brings CPU latency and increases application response time.

Windows Power Policy:

In Windows 2008, there are three power consumption options (power plan), where “Balanced” is set to default and many SysAdmin or DBA never think to change to high performance mode. As a result, performance hurts and the overall performance degrades dramatically which can’t be understood the usual way. As per different leading experts research, “High Performance” mode will provide 10% to 30% overall performance improvement.

However, just enabling “High Performance” mode does not guarantee that Windows will be able to consume power uninterruptedly. To make this Windows configuration effective, we also need to configure server BIOS power management to “OS Control” mode. Without this configuration, Windows or ESX will not operate as desired.

Virtualization:

The populate virtualization application VMWare also recommends using “OS Control“ in hardware BIOS level and configure “High performance” mode in ESXi power Management. This configuration is also recommended in Microsoft Hyper-V implementation.

Power and CPU correlation Testing:

There is a tool which is known as “Geekbench” which can be used to test how power consumption affects the CPU Performance. You can find this tool at http://www.primatelabs.com/geekbench/. Geekbench is widely used by many industry experts as a CPU stress testing tool.

Figure: HP power management