Introduction

In situations where your tuned T-SQL

statements are pushing the limits of your CPUs, more processing power

may be needed. Deploying database servers on two, four or even eight SMP

systems is rather straightforward. SQL Server usually scales almost in a

linear fashion on up to eight processors. 

However, some SQL Server installations may require up

to 32 processors. In this kind of environment, configuration parameters

that are usually ignored in smaller configurations come into play and

can offer significant performance improvements. We will take a look at

the Maximum Degree of Parallelism (DOP) and see how and why it may make

sense to change its default setting. 

Parallel Queries Performance Limitations

When adding processors to SQL Server, the

database engine will evaluate how to best leverage the available

processors through internal algorithms. In essence, when receiving a SQL

statement to process, SQL Server determines which processors are

available, what the overall cost of the query is and executes the query

on as many processors as necessary if the cost of the query reaches a

configurable threshold. When 4 processors are available on a server, the

likelihood of SQL Server using all processors for a complex SELECT

statement is pretty high. 

The same holds true in larger environments. For

instance on 16 processors, SQL Server will frequently use 12 or more

processors to execute complex SELECT statements. This may turn out to be

an issue for a couple of reasons. First, using more processors means

managing more threads and requires more cache synchronization. System

-> Context Switches/Sec is a measure of this effort. The more

processors are used for a process, the higher this counter will be. In

addition, SQL Server has more coordination to perform since it needs to

slice and regroup the work spread over the processors. Since by default

SQL Server will use as many processors as it can, upgrading your SQL

Server from 8 to 12 processors may actually degrade the overall

performance of your database. Although there are no golden rules, it

appears that in most cases using more than 8 processors for a SELECT

statement can degrade performance (although this may vary greatly by

system).

Enforcing a Maximum DOP

The DOP can be set in two ways. The first

way is to include the OPTION (MAXDOP n) keyword in your T-SQL statement.

For example, the following query will execute with a maximum of 4

processors, regardless of how many processors have been allocated to SQL

Server:

SELECT * FROM master..sysprocesses OPTION (MAXDOP4)

The other approach is to set the maximum DOP at the

database instance level, hence limiting the maximum number of CPUs to be

used for any given query. To set this option at the system level, run

the following command from Query Analyzer:

EXEC sp_configure 'show advanced option', '1'
RECONFIGURE
GO
sp_configure 'max degree of parallelism', 0
RECONFIGURE
GO

Note that this can be set differently for each

instance of SQL Server. So if you have multiple SQL Server instances in

the same server, it is possible to specify a different Maximum DOP value

for each one.

On large SMP systems, setting the maximum DOP to 4 or

8 is not unusual. The default value for this parameter is 0, which

allows SQL Server to use all allocated processors. The following test

shows the Context Switches/Sec and average response time of a T-SQL

statement running off a few million records. The server utilized for

this test was loaded with the /PAE boot.ini option, 16 processors and

8GB of RAM. The statement is as follows (the statement itself is of

little importance, but notice the OPTION keyword):

Select (UnitPrice - UnitCost) * TotalUnitsSold
        FROM Salesdb..salesdata (NOLOCK)
        WHERE
        SalesYear = 2000
        GROUP BY UPC
        ORDER BY 1
        OPTION (MAXDOP 2)

This statement was loaded 500 times in a table in a

format that Profiler could understand. Then four Profilers were loaded

on that same server, each running the content of the same table. So SQL

Server was receiving four select statements at once. Note the (NOLOCK)

hint that forces SQL Server to read the data without generating any

locks.

The results are as follows:

As more processors are added to the query (by using

the MAXDOP option), the Context Switches/Sec increases up to 13,000,

which is expected behavior. This is really a low number, considering

that we are only executing 4 statements at any single point in time.

This graph shows that starting at 12 processors, the

execution time degrades. Although it takes 12 seconds to execute this

statement on 2 processors, it takes about 6 seconds on eight CPUs.

However, we see that setting the DOP to 12 or 16 degrades the overall

performance of our query when compared to a DOP of 8.

Leaving the default Maximum Degree of Parallelism

value of 0 would yield the same result as the DOP of 16 in our test.

Hence, changing the DOP to 8 in our scenario would provide a 30% 

performance improvement over a DOP of 0 (or 16).

Enforcing a system-wide Maximum DOP is a good practice

since this allows you to control the maximum number of processors SQL

Server will use at any given time, regardless of the statement, as long

as the MAXDOP is not used in the query (which would override the global

Maximum DOP setting).

Conclusion

SQL Server has many parameters that give

you more control on the performance of your databases. Understanding how

SQL Server behaves on servers with 8 processors or less gives a strong

understanding of the capabilities of SQL Server. However, SQL Server

offers specific configuration parameters that may give you extra

performance on larger systems.

The Maximum Degree of Parallelism is a key parameter

for environments with 8 or more processors, and allows you to gain

control on the maximum number of processors used for a query. When

deciding which DOP you should use, careful evaluation of your

environment is needed. Certain queries may perform better with a DOP of

4, or even 1. Testing your environment with multiple DOPs should give

you the answer. In cases where your database environment functions in

OLTP and OLAP mode (for live reporting), you may consider setting a

default DOP for SQL Server that works best for your OLTP system and use

the OPTION keyword for your OLAP T-SQL to use the DOP that works best

for these queries.

Finally, SELECT statements are not the only types of

statements that can take advantage of the DOP, specially if your action

queries use correlated queries (in which a SELECT statement is found

inside an UPDATE statement for example). The Maximum DOP is an advanced

setting, and as such it is wise to test it thoroughly before making a

decision in your production environment.