The SQL Server Setup program offers two options that affect the priority at which SQL Server runs (system behavior). These two options are listed under the Set server options portion of Setup and are called Dedicated multiprocessor performance and Boost SQL Server priority.
In SQL Server 6.x, the Dedicated multiprocessor performance Setup option has been replaced by the sp_configure advanced option called SMP CONCURRENCY. For more information about this option, see SQL Server 6.5 Books Online.
Selecting Dedicated multiprocessor performance on a symmetric multiprocessor (SMP) computer increases the scalability improvement that multiple CPUs have on SQL Server's performance. Selecting this option is not necessary for SQL Server to benefit from multiple CPUs, but it does increase the amount of improvement. A side effect of selecting the option is that it causes SQL Server to run at a priority of 15. This helps minimize scheduling overhead and obtain maximum benefit from SMP.
Selecting Boost SQL Server priority is possible on either a uniprocessor or SMP computer. When selected on a uniprocessor, SQL Server runs at priority 15, which is high but within the variable class of priorities that range from 1 through 15. When selected on an SMP computer, SQL Server runs at priority 24, which is midway into the real-time class of priorities that range from 16 through 31. The following table displays the priority at which SQL Server will run depending on the configuration.
Priority | Multiprocessor (MP) support | Boost | SMP computer |
7 | N/A | OFF | No |
15 | N/A | ON | No |
7 | OFF | OFF | Yes |
24 | OFF | ON | Yes |
15 | ON | OFF | Yes |
24 | ON | ON | Yes |
With the previous in mind, a number of behaviors concerning SQL Server and the Windows NT thread scheduler are more understandable. One of these concerns when SQL Server is performing a CPU-intensive operation such as some types of joins. Depending on the exact operation and the amount of memory available, the operation could be done entirely in cache—effectively becoming CPU-bound. During this period, SQL Server (even when running at the default priority of 7) could degrade interactive performance. The conditions necessary for this behavior to occur do not happen often.
Another situation where this may occur is if a large single transaction or a large number of smaller transactions needed processing during start-up recovery, as would be the case if the server was abruptly shut down while these transactions were in progress. Depending on the amount of SQL Server cache buffer memory and the exact disk subsystem used, a lengthy recovery can take place mostly or entirely in cache. Because of this, it would be CPU-bound and could degrade interactive performance during this interval.
On a computer dedicated mainly as a database server, interactive console performance is usually not a priority. However, if necessary, SQL Server can be started from the command prompt in the IDLE priority class with a command such as:
start /low sqlservr -c -dc:\sql\data\master.dat
Another example is when SQL Server does a large disk initialization. In this case, the operation is extremely I/O-bound, and the lack of any scheduler I/O quotas causes behavior similar to a CPU-bound operation.
Using Boost SQL Server priority on an SMP computer dedicated to SQL Server can improve performance in limited situations. Boost SQL Server priority results in SQL Server running at priority 24, which is in the real-time class. Use of this option is not recommended outside of highly specialized conditions.
If the SQL Server computer is used for purposes other than a pure online transaction processing environment—for example, for ad hoc or decision support queries, as a file server or non-SQL application server, or for interactive use from the console—it is generally best to not use Boost SQL Server priority nor the SMP concurrency setting of -1. SQL Server version 6.0 will automatically use multiprocessor support on computers with three or more processors. Using either Boost priority, or SMP concurrency of anything other than 0 (autoconfigure) can significantly affect Windows NT console responsiveness and overall system behavior in a non-OLTP environment. The default settings for SQL Server version 6.5 provide the best overall behavior across the widest variety of circumstances and should be used in most cases.