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:
DOP | Context Switches/Sec | Avg Execution Time |
2 | 4280 | 12 |
4 | 5700 | 7.5 |
8 | 10,100 | 6 |
12 | 11,200 | 8.5 |
16 | 13000 | 9 |
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.