How to Maximise CPU Performance for SQL Server on Windows

In my previous posts How to Maximise CPU Performance for the Oracle Database on Linux and Testing C-State Settings and Performance with the Oracle Database on Linux I described the relation of C-States and P-States to database CPU performance on Linux.  For these I use an easy Oracle PL/SQL based test to measure performance and I have been asked if I use an equivalent T-SQL approach for SQL Server. The answer is yes so in this post I cover how to quickly verify that you are getting the maximum CPU performance for SQL Server from your system.

Firstly if you are not familiar with P-States and C-States or the importance of the correct BIOS settings then the posts referenced above provide the technical details relevant to both Linux and Windows and you should be able to translate the details between the two operating systems. For example in regards to Linux I reference the Powersave, Ondemand and Performance frequency scaling governors, whereas in Windows you have the Power Saver, Balanced and High Performance Power Plans. The similarity is not coincidental and nor is the fact that the default plans are Ondemand and Balanced in Linux and Windows respectively.   Similarly for Linux I use the PowerTop utility to observe current C and P-States. In Windows if you run the command “powercfg –energy” you can observe on up to date systems an “Idle State Type of ACPI Idle (C) States” and “Performance Controls Type ACPI Performance (P)” confirming that the operating system is taking advantage of the same underlying technology.

With a common background established you should be aware that the use of Turbo Boost Technology is going to play just as important a factor on Windows as it does on Linux for performance and therefore the next place to look is Find your CPU model from your system information (such as right-clicking on “This PC”) and look up the specifications, here is the example from my test system:


Even though this CPU is detailed as intel ® Core ™ i7-4770K CPU @ 3.50GHz in certain circumstances this CPU can run at 3.9GHz and therefore the next step is to identify the correct utilities that can show the active frequencies whilst noting that with multiple cores the individual cores can and will be able to run a different frequencies from the cores in the same and different sockets in the same server.  Within the updated Task Manager with Windows 8 and Windows Server 2012 the Turbo Boost frequency is shown, with previous and current versions of Windows the downloadable utility CPU-Z will also show actual frequencies therefore I will use this tool for measuring SQL Server performance.

Moving over to SQL we want a simple T-SQL routine that will run on a single core or thread using maximum frequency to test the CPU configuration. Such a routine written in PL/SQL is described for Oracle and therefore an equivalent port of this routine to SQL Server is shown below:

USE [tpcc]








@n numeric(16,6) = 0,




@f int

SET @f = 1


WHILE @f <= 10000000


SET @n = @n % 999999 + sqrt(@f)

SET @f = @f + 1






Being close to the PL/SQL original here the routine completes in similar times to the Oracle based original on the same system and therefore also provides a first test as an indication of the potential performance gains compared to databases running on RISC based architectures.

To create the procedure enter the T-SQL above in a query window specifying the database in which to create the routine (In this case [tpcc]) and click on Execute.  This creates a stored procedure called dbo.CPUSIMPLE in your database.  To run the stored procedure right click on the procedure name and select “Execute Stored Procedure …” This brings up a New Query with the following and begins to Execute.

USE [tpcc]


DECLARE       @return_value int

EXEC   @return_value = [dbo].[CPUSIMPLE]

SELECT 'Return Value' = @return_value


When complete, if successful under Results it shows a Return Value of 0 and under Messages it shows the result and completion time, for example:

Timing = 6500

Res = 873729.721235

(1 row(s) affected)

The Result should always be the same and it is the completion time we are interested in – in this case 6.5 seconds.  This may vary slightly but only by a narrow margin.

We can now modify the settings in the Power Plan (accessed under Control PanelHardware and SoundPower Options) and observe the impact on SQL Server performance when running the routine by using Tasking Manager and or CPU-Z.  By default on the test system it is running the Balanced power plan and at idle the frequency (identified under Clocks (Core #0) noting that this is only measuring the first core) is lower than the nominal frequency.  From the previous posts we can identify that under the Balanced plan this is the impact of EIST (Enhanced Intel SpeedStep Technology) dynamically adjusting processor voltage and frequency.  In the example below the Reference or Base Clock (BLCK) is set at 100MHz (On K series processors as with this example this can be modified however on production based systems this  will be fixed) and the multiplier has been dynamically reduced to x 8 .0 resulting in a core frequency of 800 MHz.


CPU-Z reports that the multiplier can be increased dynamically to x 39.0 which not surprisingly corresponds to a core frequency of 3.9GHz (which is the Max Turbo Frequency).  The actual multiplier will depend upon the number of active cores and in this example is x 37.0 for 4 active cores, x 38.0 for 3 active cores and x 39.0 for both 2 and 1 active cores and therefore depending on the overall processor load the peak turbo frequency will adjust dynamically above the maximum non turbo ratio which is x 35.0.

Given our T-SQL test running on a single core we can expect EIST to dynamically increase the multiplier and voltage (given power and temperature constraints) and as shown this increases up to the maximum Turbo Frequency of 3.9GHz


This confirms that the current BIOS and operating system settings are correctly using Turbo Boost with a result in this case as follows:

Timing = 6406

Res = 873729.721235

(1 row(s) affected)

If we set the Power Plan to High Performance then the operating system requests that the CPU runs at the maximum frequency. The impact that this has on performance depends on the Processor, BIOS and Operating system.  Even if the full Turbo Mode frequency is reported by CPU-Z, all active states above the standard rated frequency are hardware controlled and C-States will still be entered when idle.  As this processor is based on the Haswell Microarchitecture with enhanced power management features it is not surprising that the results on this system are similar to that experienced with the Balanced Power Plan.

Timing = 6496

Res = 873729.721235

(1 row(s) affected)

Nevertheless by switching between Balanced and High Performance Power Plans you can run this routine and observe the impact on performance and determine what is best for your configuration.

As a further illustration in this example running the Power Saver Plan limits the processor to a maximum of 1Ghz and the stored procedure takes almost 28 seconds to complete:

Timing = 27950

Res = 873729.721235

(1 row(s) affected)

Clearly the impact on SQL Server performance is severe and therefore running in Power Saver mode is not advisable.  Instead testing between the Balanced and High Performance settings will indicate the optimal settings.  If on the other hand with this test you are not seeing the performance that you would normally expect (and typically with a modern processor this would be around 10 seconds, but could take longer depending on the CPU in question) then the next step is to troubleshoot the system BIOS settings.

After maximising your single threaded configuration you can then look to maximising CPU performance for multi-threaded workloads.  Typically with a scalable database such as SQL Server running a scalable database application will mean that the SQL Server workloads benefit in the same way that Oracle does from enabling Hyper-Threading  and therefore enabling Hyper-Threading should be the default option for scalability.  When enabling Hyper-Threading especially on systems with large core counts it is important to note that Windows Server 2008 and prior supported up to 64 CPUs only. Windows Server 2008 R2, 2012 and 2012 R2 supports up to 256 processors however divides these processors up into processor groups of 64 processors with handling of processor groups improving with each release of Windows.  For example on a test system with E7-4890v2 providing 120 logical processors Windows Server 2012 R2 presents these CPUs as 2 Processor Groups of 60 logical processors each.  SQL Server is able to operate across multiple Processor Groups however it is always a prudent approach with highly scalable workloads to ensure that sessions are evenly distributed across available CPU cores to ensure maximum performance.  For example the following screen taken from the metrics monitor of HammerDB while running a SQL Server workload illustrates a desirable distribution.


For further troubleshooting I find these Open Source SQL Server Scripts useful.  Primarily designed to find I/O bottlenecks they are nevertheless useful to identify other resource bottlenecks as well. If you monitor a workload and these scripts report high wait events on the resource_type  of CPU under the resource_category of Threading and CPU then  it is worth investigating your Server Properties under processors to ensure that the workload is correctly balanced. Typically an automatic setting will be optimal however testing can confirm this for you.


In this post I have looked at CPU single and multi-threaded performance applying some of the knowledge from Oracle on Linux workloads to ensure that SQL Server is similarly optimized to take advantage of the available CPU resources.