Proposed SQL Server defaults: disable priority boost

(This post was updated on 5 February 2022.)

A few months ago I suggested that the following settings should be the default for most SQL Server instances:

• Set cost threshold for parallelism to 50
• Disable lightweight pooling if it is enabled
• Disable priority boost if it is enabled
• Set optimize for ad hoc workloads to enabled (read Don’t optimize for ad hoc workloads as a best practice)
• Set max server memory (MB) to a custom value consistent with Jonathan Kehayias’s algorithm
• Set backup compression default to enabled
• Set the power saving settings on Windows to high performance if possible
• Provide an option to flush the plan cache as needed

Over the next few posts I will dive into the why. This week we look at disabling priority boost.

What is priority boost?

A long time ago, in a galaxy far, far away, there was a company represented by the colour red that ruled relational database management systems. Everyone bought their products in the way that people used to buy IBM. It was considered a safe bet.

Then a young upstart called Microsoft entered the market after purchasing a product called Sybase SQL Server, and they began to compete against Big Red. As these two companies went head-to-head, they used external benchmarks to pit their products against each other.

It turns out that on one of the benchmarks, Microsoft was able to achieve higher throughput by setting the SQL Server process to HIGH_PRIORITY_CLASS, and the thread priority level over and above that to THREAD_PRIORITY_HIGHEST. While it was extremely helpful to beat artificial benchmarks, it came at the cost of giving SQL Server higher execution context on the CPU than almost every other process on Windows.

Unfortunately, the vast majority of processes in Windows run at the normal execution context, including video drivers, keyboard input, mouse drivers, storage drivers, network drivers, and so on.

The article on the SetThreadPriority() API function does not mince words:

Use extreme care when using the high-priority class, because a high-priority class application can use nearly all available CPU time.

When should I use priority boost?

Don’t. It was officially deprecated in SQL Server 2008 R2, which means that it will be removed in a future version of SQL Server. Here’s what the official documentation says:

This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.

Additionally Microsoft states:

Raising the priority too high may drain resources from essential operating system and network functions, resulting in problems shutting down SQL Server or using other operating system tasks on the server.

That’s blue-screen territory. While Microsoft suggests that it might help under rare conditions, there’s no conceivable scenario in my mind that warrants this configuration option being enabled, that cannot be otherwise achieved through standard performance tuning options.

Off by default

The good news is, like its cousin lightweight pooling, this feature is off by default. Assuming that the instance has not been upgraded from an ancient version of SQL Server (i.e. before SQL Server 7.0), there is only a very small chance that it is enabled. Nevertheless, it is one of the first things I check when configuring an instance, because someone somewhere may have enabled it without understanding the implications.

Share your dubious performance tuning efforts in the comments below.