I just had a very interesting conversation with my client who runs heavy workload in the technical education ecosystem. They were running SQL Server for over 10 years and they had some real performance problems with their system but instead of investing time in tuning their server they kept on upgrading hardware and finally they reached out to the point where they realize that they need to look at their server’s health check and we finally started to work together on Comprehensive Database Performance Health Check. While working together we figured out one of the many issues was the priority boost server configuration. Let us talk about it a bit more in detail.
Symptoms of Performance Issues
As soon as we started to work together, it was very clear that they were having a problem with the configuration settings in SQL Server. During the busy times of the day, their system’s response was very erratic. One moment everything is fine and suddenly everything gets slower to just speed up in the next few seconds. The first step was to do checks on their Memory, CPU, IO, and Network. Very quickly we realized that there is nothing abnormal in that area.
The next thing was to look at the server’s sudden behavior and its history. What I learned from the client is that they had changed the hardware multiple times and also upgraded SQL Server from version 2012 to 2014 and now to 2017. No matter which version they use they are facing exactly the same issues with a performance where their server suddenly gets slower to just speed up again after a few moments. Sometimes the slowness lasted for minutes to disappear for hours.
After looking at my own diagnosis and the checking history of the server, it was pretty clear to me that it might be one of the 10 performance killer configurations. I started to look at each of them and within few minutes ended up on Priority Boost.
Priority Boost Server Configuration
While going over my personal checklist when I ran the following query, I got the result of the query as 1.
SELECT name, value FROM sys.configurations WHERE name = 'priority boost' GO
It was very clear that my client had this particular configuration turned on in their server properties. Though the name of the configuration looks like that turning it on it may speed up the server’s performance. However, in reality, it actually gives exactly the opposite results.
I have seen many of my client’s struggling with this configuration and when it is turned off, they finally take get the desired performance from their system.
Note: Microsoft says this will be deprecated so stop using this feature as soon as possible.Â
Once we know this configuration was on, we immediately jumped to SQL Server Management Studio to turn it off but we realized that the client is using SSMS 18.2 which actually does not show this feature at all. On the one side it is good that now people will not turn this configuration on accidentally but on the other side it is very bad that if someone turns this one on, not everyone who just uses SSMS will know that this feature is on in their system.
I have previously blogged about this topic over here: SQL SERVER – Priority Boost and SSMS 18
Sometimes human needs visual clues start thinking and find creative solutions.
Solution – Priority Boost Server Configuration
Once we know what was the problem it was easy to provide the solution. We ran the following script to turn off the priority boost.
EXEC sys.sp_configure N'priority boost', N'0' GO RECONFIGURE GO
During the evening client restarted the SQL Services so this particular setting can take effect. Today is the 8th day since we did this exercise and the client just called me up that they have not seen a single performance issue since we deploy the fix of turning off priority boost.
Prediction
I personally believe they will not face any further issues for a while as they have already upgraded their hardware more than they needed. Unfortunately due to the server configuration issues, they were not able to get the necessary performance. Now we went through various exercises while working together on Comprehensive Database Performance Health Check and I am confident that they are going to observe superfast performance for a significant while.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
