BIOS and OS Power Settings to enhance SQL Server Performance

Performance is always a most prioritized task for any DBA and every DBA always prefer to go for a proactive approach and keep your system’s performance on top rather than reactive when it comes to addressing user complaints. SQL Server is a complex system and due to the continuous increasing size of data and multiple business logic, getting to the root of performance problems can be difficult. There are a lot of different factors that can impact SQL Server performance, varying from hardware and system configuration settings to poor T-SQL query practices.

Today, we will be discussing BIOS and OS Power Settings to SQL Server Performance boost. BIOS configurations play a very important role in server performance and need coordination with DBA & Server engineer for proper configuration.

BIOS Power Settings (C-State and P-State):

P-states (Performance State)

P-States used by the system for Reducing Power Consumption Without Impacting Performance. P-states are operational states. C-State (C0 – Active Mode) where the code is executed, in this C-state the P-States are relevant.

In C0 State when the code is executing, the operating system and CPU can optimize power consumption through different p-states (performance states). P-States are different frequencies to operate CPU, P0 is the highest frequency (with the highest voltage).

Core C-States – The Details

C-states are idle power saving states. In a P-state, the processor is still executing instructions, whereas, during a C-state (other than C0), the processor is idle, meaning that nothing is executing.

In P-State Cores are executing code, System just changes the CPU cores frequency as per requirements reduce voltage and energy consumption. But In C-State CPU Cores are idle and not executing anything. To simplify, If CPU is idle why to keep the unused circuits powered up and consuming energy. It should be good to Shut them down and save energy.

• C0 – Active Mode: Code is executed, in this state the P-States (see above) are also relevant.

• C1 – Auto Halt

• C1E – Auto halt, low frequency, low voltage

• C2 – Temporary state before C3. Memory path open

• C3 – L1/L2 caches flush, clocks off

• C6 – Save core states before shutdown and PLL off

• C7 – C6 + LLC may be flushed

• C8 – C7 + LLC must be flushed

C-States can be further divided into core C-states (CC-states), package C-states (PC-states) and logical C-states.

A CC-State (Core C-state) is a hardware C-state. One Processor has multiple cores and each of these cores has its own idle state. This is logical as one core might be idle while another is hard at work on a thread. So, the Idle state of one of those cores is C-state.

A PC-State (Processor C-state) is related to a core C-state. At some point, cores share resources, like L2 cache or the clock generators. The processor can only enter a PC-state if all cores of processor enter CC-State (Core C-state).

An LC-State (Logical C-state) is an OS’s view of the processors’ C-states. In Windows OS, a PC-State (Processor C-state is pretty much equivalent to a core C-state. OS manages when the core needs to move to CC-State.

How to Change C-State from BIOS (Hardware Level)?

We can disable C-State from BIOS and this will keep unused circuits powered up and keep performance boost.

How to Change OS Power Settings?

By default, most systems come with a balanced power setting, which means that the system will attempt to conserve power by cutting CPU processing speed. The balanced setting can reduce available processing power to the significant level and impact performance for a production SQL Server system. Balanced is not recommended for the server running SQL Server instance, we should change it to High Performance. To change OS power settings, go to Control Panel > Hardware > Power Option > Choose Power Plan > Select High Performance.