Enabling Priority Boost does not mean that you get performance from your SQL Server. It may be quite opposite. Learn it with the help of the official documentation in this SQL in the Sixty Seconds video.
If you look at the term priority boost, you may think this feature may be related to improving the SQL Server Performance but I can tell you confidently from my experience of Comprehensive Database Performance Health Check that honestly instead of enabling when I disable it, we get necessary performance.
Here is the official Microsoft documentation which describes that the Priority Boost feature will be not supported in the future and it is a good idea to disable it on the server. Microsoft documentation also suggestions that we should not use this feature in the future as well.
In this SQL in the Sixty Seconds video, we discuss how we can check the current status of the configuration and how we can disable priority boost for our server.
If you are looking for the script used in this demonstration, you can easily find them over in the following blog posts.
- SQL SERVER – Turning Off Priority Boost Server Configuration Option on SSMS 18 Onwards
- SQL SERVER – Priority Boost and SSMS 18
- SQL SERVER – Boost SQL Server Priority and SSMS 18
How to Check and Turn Off Priority Boost Safely
The name makes it sound like a free speed button, but it only raises the Windows scheduling priority of the SQL Server process. When SQL Server grabs the CPU first, the operating system and other important services can struggle to get their share, and that can lead to odd problems, especially on clustered servers.
Because it is an advanced option, turn on show advanced options before you change it. To see your current setting, run SELECT name, value_in_use FROM sys.configurations WHERE name = 'priority boost'. A value of 1 means it is on. After you change it to 0 with sp_configure and RECONFIGURE, remember that the new value takes effect only after the SQL Server service restarts, so plan the change for your next maintenance window. After the restart, run the same query again to confirm the value in use is now 0.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
1 Comment. Leave new
@pinal – I am not sure if you experience this behavior or not. After we set the priority boost to false for the SQL Server Advance Property, we are seeing a rise in slow queries when a full backup is scheduled. We have four node SQL servers in HA/DR. The primary node is responsible for the full backup of the user database.