Sunday, 5 February 2006

Improving the performance by using 'Boost SQL Server Priority' option

Boost SQL Server Priority is one of the many options that we can configure at SQL Server instance level in order to improve the performance of the database engine by changing its priority running on Microsoft Windows Server.

By default, the value of this option is 0 (zero) which says to Windows Server to execute SQL Server under normal priority either running on one processor or symmetric multiprocessor environment. On the other hand, if we configure this value to 1, SQL Server will be executed with a major priority which might improve the performance of a database server. This option works very well on servers which are only dedicated to SQL Server. In contrast, it is not highly recommended using it when there are more applications running on the same server.

'Boost SQL Server Priority' option can be configured via either SQL Management Studio or T-SQL. In this post we are going to see how to configure it via the second way. Because this option is an Advanced Option, we firstly need to enable the possibility of accessing to it, which means that we have to enable 'show advanced options' option:
sys.sp_configure 'show advanced options', 1
Shortly after that, we must confirm the change without restating the database engine:
RECONFIGURE
GO
Having done that, we move on configuring 'Boost SQL Server Priority'.
EXEC sys.sp_configure N'priority boost', N'1'
GO
Now we need to commit and override the change.
RECONFIGURE WITH OVERRIDE
GO
That is all we need to do in order to enable this option. It couldn't have been simpler.
To sum up, at this point I need to ask you to work with a lot of cautiousness as this configuration might impact on the whole database engine performance. If there were many SQL Server database engines running on the same server and this option was enabled only for some of them, the rest of them might be impacted detrimentally. Nevertheless, I personally do not recommend enabling it on machines running on only one processor or many database engines (or other applications). Thanks for reading. Stay tuned.