Blitz Result: Priority Boost
Priority Boost sounds like a button that magically engages turbo mode on your server. Because that’s an exciting prospect, people enable the feature without fully understanding the consequences.
What PRIORITY BOOST SOUNDS LIKE IT DOES
WHAT PRIORITY BOOST ACTUALLY DOES
With Priority Boost enabled, SQL Server runs at a priority base of 13 in Windows (instead of the normal 7). Microsoft explicitly states this may not end well:
But What About My Other Applications?
Instead of trying to make sure SQL Server is running at a high priority, reduce the number of other processes competing with SQL Server for resources. Get the most out of your expensive SQL Server licenses by using this server exclusively for SQL Server.
In short, make sure your SQL Server isn’t a clown car.
HOW TO FIX THE PROBLEM
Schedule a maintenance window when you can restart your SQL Server instance. Turn off Priority Boost using one of the methods below, then restart to make the change take effect.
How to Turn Off Priority Boost
You can do this in the Server Properties window of SQL Server Management Studio, or through scripting. Whichever option you choose, the change will not take effect until you restart your SQL Server.
Option 1: Graphical Mode
connect to the instance in Object Explorer, right click the instance name, and select ‘Properties’. Then click on the ‘Processors’ tab:
Option 2: T-SQL
Here’s the script version to turn off Priority Boost:
/* Check for any pending configurations before you start. */
/* RECONFIGURE applies everything pending. */
WHERE value <> value_in_use;
EXEC sys.sp_configure N'show advanced options', N'1';
EXEC sys.sp_configure N'priority boost', N'0'