SQL Bad Practices

On occasion I have seen Database Administrators enabling the SQL Server “Boost SQL Server priority” option. This option is available on the Server Properties Window under Processors:

If you enable this option, SQL Server will run the sqlservr.exe process and threads as High Priority instead of its usual Normal priority. Hence, when SQL Server service will request CPU, other processes in need of CPU time won’t be prioritized. In some scenarios it can lead to problems and most of the time it won’t bring any benefit. Microsoft do not recommend to enable this feature, see this Microsoft Support article (search for Priority Boost).

Production server generally falls into one of these two categories:

1. One server box handling everything. For example, SQL Server is installed on the same machine as a web server running a web application with perhaps Analysis Services or SISS processes that runs once in a while. This is the worst scenario for enabling the priority boost because you need to do several jobs at the same time therefore you want all your process to run smoothly together. One example would be to launch a CPU-intensive query within SSIS while computing complex business rules or building Data Mining models. You certainly do not wish SQL Server to take all CPU resources here because other computations are running simultaneously.

2. Dedicated SQL Server server. If SQL Server is the only service running then there won’t be other processes fighting for CPU time so enabling the “Boost SQL Server priority” feature brings no benefit. Plus, you could get some unpredictable results because core processes such as Windows processes or device drivers may not get enough resources when SQL Servers runs CPU-intensive queries (for example several queries performing hash joins between large tables).

To be fair, Microsoft mentions in this Microsoft Connect article that you might see some performance improvements in “high-end servers primarily with OLTP workloads”. My opinion is that even in the very rare occasions that you get small improvement for SQL Server, your overall system performance may worsen.

Here’s an example demonstrating that the option is very dangerous. Do not try this if you are connected to a local SQL Server instance because your system will become completely unresponsive and you will have to manually shut down your computer. Do NOT try this on a production server…

1. Configure the priority boost Option (see How to: Configure the priority boost Option for the steps) and restart SQL Server as indicated.

2. Open a new query window in Management Studio and run the following script:

DECLARE @I INT
SELECT @I = 1
FROM sys.objects a
CROSS JOIN sys.objects b
CROSS JOIN sys.objects c
CROSS JOIN sys.objects d
CROSS JOIN sys.objects e
CROSS JOIN sys.objects f
CROSS JOIN sys.objects g

3. Repeat step #2 until the system is totally unresponsive…

4. Cancel all query executions and the system will come back to its normal state.

Nice article on the priority boost option: http://blogs.msdn.com/b/arvindsh/archive/2010/01/27/priority-boost-details-and-why-it-s-not-recommended.aspx.

UPDATE: MSDN states in the SQL Server 2008 R2 documentation that the priority boost option will be removed in future versions of SQL Server: http://msdn.microsoft.com/en-us/library/ms180943.aspx .