Today we are going to discuss how we can change the configuration value of the Boost SQL Server Priority with SSMS version 18 onwards. Recently during one of the Comprehensive Database Performance Health Check while using SSMS 18.5.1 I realized that now it is very easy to access Priority Boost configuration.
In the earlier blog posts, I have written about how SQL Server 2018 is missing the option to turn off the settings for Boost SQL Server Priority you can read about that here. If you are interested to read why I am asking one should disable Priority Boost settings, you should my blog post here. Once you read both the blog posts, now it is time to read about the new enhancement in SQL Server Management Studio 18.5.1.
SSMS and Boost SQL Server Priority
The biggest issue which I faced since the release of SQL Server 18 was there was no dedicated place in SSMS I can point to my client and show that they should have not enabled this configuration as it actually creates the performance issues. You can read all about that under the Symptoms of Performance Issues. Every single time, I had to run into this issue, I had to write a T-SQL script to find the value of the setting and then change it via T-SQL.
Fortunately, now the latest SSMS update has this settings surfaced under SQL Server Properties.
First, go to your SQL Server node (which is usually the very first line in the object explorer) and right-click on it. Next, go to the properties and select the page Advanced. Next, go to Miscellaneous and set the value for the settings Boost SQL Server Priority to False.
That’s it. You are done. If you are wondering why I am suggesting that you should set this configuration setting to false, I suggest you read the blog post earlier mentioned in the second paragraph of this blog post. I consider myself fortunate that I get to learn a new thing every single day when I am tuning SQL Server Performance.
Reference: Pinal Dave (https://blog.sqlauthority.com)