Boost SQL Server Priority to Increase Server Performance

You can enable the "Boost SQL Server Priority" option, to allow SQL Server threads to run in the real time priority
class. When running at this priority level, SQL Server threads will be executed before all other process threads
running in the lower variable priority class. This implies that on single processor machines under heavy load and not
dedicated to SQL Server, other processes may not get enough attention.
However, if the system is dedicated to SQL Server and disk I/O activity tends to be heavy, then you should enable
this option to get substantial performance gains

Priority boost should be used only on a computer dedicated to SQL Server, and with a symmetric
multiprocessor (SMP) configuration. SMP (ie, multiple processors)

!!

Use the priority boost option to specify whether Microsoft® SQL Server™ should run at a higher Microsoft Windows
NT® 4.0 or Windows® 2000 scheduling priority than other processes on the same computer. If you set this option to
1, SQL Server runs at a priority base of 13 in the Windows NT 4.0 or Windows 2000 scheduler. The default is 0, which
is a priority base of 7.
priority boost should be used only on a computer dedicated to SQL Server, and with a symmetric multiprocessor
(SMP) configuration.

Caution Boosting the priority too high may drain resources from essential operating system and network functions,
resulting in problems shutting down SQL Server or using other Windows NT 4.0 or Windows 2000 tasks on the
server.
In some circumstances, setting priority boost to anything other than the default can cause the following
communication error to be logged in the SQL Server error log:
Error: 17824, Severity: 10, State: 0 Unable to write to ListenOn
connection '<servername>', loginname '<login ID>', hostname '<hostname>'
OS Error: 64, The specified network name is no longer available.

Error 17824 indicates that SQL Server encountered connection problems while attempting to write to a client. These
communication problems may be caused by network problems, if the client has stopped responding, or if the client
has been restarted. However, error 17824 does not always indicate a network problem. Check priority boost and
make sure that the option is set to the default. Deviating from the default may cause error 17824.
!!
That can let the core processes of the OS to starve for resources. It is suggested not to enable that
option.
Agreed. I've also seen cases where SQL Server's priority has exceeded the hardware manufacturer's driver priority,
and the drivers haven't been able to get the CPU time they need to keep up with SQL Server. Caused a bluescreen at
StackOverflow, believe it or not. – Brent Ozar Oct 18 '10 at 1:23

Configure the priority boost

Server Configuration Option
SQL Server 2012
Other Versions
This topic describes how to configure the priority boost configuration option in SQL Server 2012 by
using SQL Server Management Studio or Transact-SQL. Use the priority boost option to specify
whether Microsoft SQL Server should run at a higher Microsoft Windows 2008 or Windows 2008 R2
scheduling priority than other processes on the same computer. If you set this option to 1, SQL Server
runs at a priority base of 13 in the Windows 2008 or Windows Server 2008 R2 scheduler. The default is
0, which is a priority base of 7.

Important

This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new
development work, and modify applications that currently use this feature as soon as possible.
In This Topic
 Before you begin:
Limitations and Restrictions
Security
 To configure the priority boost option, using:
SQL Server Management Studio
Transact-SQL
 Follow Up: After you configure the priority boost option
Before You Begin
Limitations and Restrictions
 Raising the priority too high may drain resources from essential operating system and network
functions, resulting in problems shutting down SQL Server or using other operating system tasks
on the server.
Security
Permissions
Execute permissions on sp_configure with no parameters or with only the first parameter are granted
to all users by default. To execute sp_configure with both parameters to change a configuration
option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level
permission. The ALTER SETTINGS permission is implicitly held by the sysadmin andserveradmin fixed
server roles.
[Top]

Using SQL Server Management Studio

To configure the priority boost option
1. In Object Explorer, right-click a server and select Properties.
2. Click the Processors node.
3. Under Threads, select the Boost SQL Server priority check box.
4. Stop and restart SQL Server.
[Top]

Using Transact-SQL
To configure the priority boost option
1. Connect to the Database Engine.
2. From the Standard bar, click New Query.
3. Copy and paste the following example into the query window and click Execute. This example
shows how to usesp_configure to set the value of the priority boost option to 1.
Transact-SQL
USE AdventureWorks2012 ;
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE ;
GO
 EXEC sp_configure 'priority boost', 1 ;
GO
RECONFIGURE;
GO

For more information, see Server Configuration Options.

[Top]

Follow Up: After you configure the priority boost option

The server must be restarted before the setting can take effect.
[Top]

See Also
Reference
RECONFIGURE (Transact-SQL)
sp_configure (Transact-SQL)
Concepts
Server Configuration Options

Priority boost details – and why it’s

not recommended
★★★★★
★★★★
★★★
★★
★

January 26, 2010By Arvind Shyamsundar8

 0
 0
 0

Some times, we see customer has (accidentally or otherwise) enabled the option ‘boost priority’
for SQL Server worker threads. In general Microsoft does not recommend that you set this option.
Why?

First a bit of background. When we set the ‘priority boost’ option using sp_configure what is
happening is that after restart the SQL engine will call Win32 API SetPriorityClass() and passes in
HIGH_PRIORITY_CLASS (if you are debugger savvy, you can set breakpoints on these APIs and
check what is happening – that’s what I did, no source code is required to verify this). From MSDN:

HIGH_PRIORITY_CLAS Process that performs time-critical tasks that must be executed immediately.
S The threads of the process preempt the threads of normal or idle priority class
processes. An example is the Task List, which must respond quickly when
0x00000080
called by the user, regardless of the load on the operating system. Use extreme
care when using the high-priority class, because a high-priority class application
can use nearly all available CPU time.
It then proceeds to call SetThreadPriority() with priority as THREAD_PRIORITY_HIGHEST. For this
combination of Process Priority Class and Thread Priority Level, the base priority level of these
worker threads is 15. The only ones higher than this in the hierarchy of the OS are any threads
which have process priority class set to REALTIME_PRIORITY_CLASS (which should be a very rare
case for any application.) this means that many SQL worker threads are running at a priority level
which is close to the highest on the system. Hence, they will tend to be selected frequently by
kernel dispatcher to execute on the CPU.

So what is the effect?

There is clear precedent in the support teams of priority boost causing unresponsive servers.
Sluggish UI / mouse / keyboard movements are other common symptoms if this setting is
interfering with the capability of the OS to give (non-SQL) threads their desired quantum on the
CPU. On a cluster, having priority boosted SQL threads can cause other critical threads such as the
resource monitor’s IsAlive poll thread to timeout, thereby causing unwanted failover. Therefore we
do not recommend to set priority boost to 1,especially in clustered instances.

Reference links:

SetPriorityClass: http://msdn.microsoft.com/en-us/library/ms686219(VS.85).aspx

SetThreadPriority: http://msdn.microsoft.com/en-us/library/ms686277(VS.85).aspx

Effective Base Priority: http://msdn.microsoft.com/en-us/library/ms685100(VS.85).aspx

Windows Priority
levels: http://www.microsoft.com/mspress/books/sampchap/4354c.aspx andhttp://
www.microsoft.com/mspress/books/sampchap/4354d.aspx