SQL SERVER – Priority Boost and SSMS 18

Just the other day, I had a very interesting experience while using the latest SQL Server Management Studio 18.0 preview 7 (SSMS 18.0) during one of the Comprehensive Database Performance Health Check. My client said they have realized that SQL Server does not have settings of Priority Boost settings available in SQL Server anymore. Actually, this is not true, let us quickly understand the same.

Let us connect to SQL Server 2017 edition, this is true with SQL Server 2019 CTP as well with SSMS 17.9.1 and also with SSMS 18.0 with the latest version. When I was writing this blog post the latest preview version 7. Now navigate to the server settings and go to processors. Under here you will notice the following image.

In SQL Server 17.9.1 we do see the option of Priority Boost and Windows Fibers. However, when going to the same settings in SQL Server 18.0, we do not see that option. My client was interpreting that in the latest version of SQL Server it was removed but the reality was the feature was not visible on the latest version of SSMS.

Solarwinds

SQL SERVER - Priority Boost and SSMS 18 boostsqlserverpriority

When we ran following query in SSMS 17.9.1 and SSMS 18.0 we were able to see the settings just fine in the results set.

SELECT *
FROM sys.configurations
WHERE name='priority boost'

I personally, do not recommend you to enable this setting on your SQL Server and suggest you run the following commands to disable it.

EXEC sys.sp_configure N'priority boost', N'0'
GO
RECONFIGURE
GO

SQL SERVER - Priority Boost and SSMS 18 boostsqlserverpriority1

You may need to restart your SQL Server services after making changes to the settings.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , ,
Previous Post
SQL SERVER – Default Worker Threads Per Number of CPUs
Next Post
Business Secrets in SQL Server Performance Tuning Practical Workshop for EVERYONE

Related Posts

Leave a Reply

Menu