Quick Scan Report – Priority Boost

What this check looks for

sys.configurations where name is priority boost and either value or value_in_use is 1. Both columns are checked, because value is what has been set and value_in_use is what is running, and they disagree when somebody has changed the setting but not restarted. Either one being 1 is worth reporting: one means it is active, the other means it will be after the next restart.

Why it matters

The name is the problem. It sounds like the setting everybody has been looking for, and it is the opposite.

What it actually does is raise the SQL Server process’s Windows scheduling priority from normal to high. That does not make SQL Server faster. It makes SQL Server preferred over everything else on the machine, including the parts of the machine SQL Server depends on:

  • Network stack components, so a busy SQL Server can starve the very code that delivers its own client connections.
  • The cluster service, which is the well known failure. A node under load stops responding to cluster heartbeats in time, the cluster concludes the node is dead, and it fails over. The resulting outage looks like a hardware or network fault.
  • Whatever you need to run to diagnose the problem, which is now competing with a higher priority process that is using every core.

Microsoft’s own documentation warns that raising the priority can drain resources from essential operating system and network functions, resulting in problems shutting down SQL Server or using other operating system tasks. The setting has been deprecated and is documented as having no effect in current versions, which makes it doubly not worth keeping: on an older instance it causes real harm, and on a newer one it is a confusing leftover that implies somebody was tuning by folklore.

There is no workload for which this is the right answer. A server where SQL Server needs more CPU is a server that needs fewer other things running on it, or more CPU.

How to confirm it yourself

SELECT [name],
       [value],
       [value_in_use],
       [is_dynamic],
       [description]
  FROM sys.configurations WITH (NOLOCK)
 WHERE [name] = 'priority boost';

value and value_in_use disagreeing means a change is pending a restart.

While you are looking, the settings that are usually wrong alongside it:

SELECT [name], [value], [value_in_use]
  FROM sys.configurations WITH (NOLOCK)
 WHERE [name] IN ('priority boost', 'lightweight pooling', 'affinity mask',
                  'affinity I/O mask', 'max worker threads', 'set working set size')
 ORDER BY [name];

Those tend to travel together, because they come from the same era of tuning advice.

How to fix it

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'priority boost', 0;
RECONFIGURE;

The change requires a service restart to take effect. Until then value reads 0 and value_in_use still reads 1, and the setting is still active.

Two things to do while you are there, because you are arranging a restart anyway:

  • Check lightweight pooling. Fiber mode is from the same body of advice, causes its own problems, and has its own check in this report.
  • Find out why it was set. It was turned on to solve something, and if that something was genuinely CPU pressure then it is still there and still unaddressed. Look at what else runs on the machine and at max server memory, which is the setting people reach for this one instead of.

If this instance is clustered or in an Availability Group, treat it as a priority. The heartbeat interaction is not theoretical, and an unexplained failover in the history of this cluster may well be explained by it.

How long it takes

About an hour, nearly all of it waiting for a window to restart the service.

Report Why you would go there
Configuration Values Every setting against its default, which is where the rest of these live.
Server Overview What else is running on this machine and competing for CPU.
Memory Whether max server memory is the setting that should have been changed.
Failover Compatibility Cluster and Availability Group settings, given the heartbeat risk.
SQL CPU Schedulers Whether there is real CPU pressure behind the original decision.
Check
Lightweight pooling The other setting from the same advice, with its own problems.
Max server memory The setting that usually should have been changed instead.
Not using all cores Real CPU limits, as opposed to imagined ones.
Max degree of parallelism Parallelism settings, also frequently wrong on the same instances.

Frequently asked questions

It has been on for years with no problem. Then the machine has not been under enough load to provoke it, or a failover has been explained away as something else. The setting provides no benefit in exchange for that risk.

The documentation says it has no effect now. Why report it? Because it tells you something was configured by folklore, and it is almost never the only such setting. It also still has an effect on older versions, which is where it is mostly found.

Do I really need a restart? Yes. It is not a dynamic setting. value_in_use tells you what is running.

Is there any case for enabling it? No. There is no supported scenario in which this is recommended.