Configuring the Maximum Degree of Parallelism
A commonly altered setting is Maximum Degree
of Parallelism (MAXDOP), which controls the maximum number of CPUs that
can be used in executing a single task. For example, a large query may be
broken up into different parts, with each part executing threads on separate
CPUs. Such a query is known as a parallel query.
When considering a typical OLTP system consisting of lots of
short, simple transactions, multiple CPUs are valuable in their ability to
service lots of simultaneous threads from multiple users. In contrast, a
typical OLAP (reporting / data warehouse) application consists of a smaller
number of much larger queries. It follows that splitting up a query into
multiple chunks, with each chunk running on a separate CPU is more suited to an
OLAP system whereby large amounts of resources can be used in speeding up the
execution time of large queries.
During query compilation, SQL Server will decide whether or not
to use a parallel query if the MAXDOP setting allows and the estimated cost of
executing the query in a serial manner on a single CPU exceeds a cost
threshold. By default, MAXDOP is 0, meaning that SQL Server is left to decide
on the appropriate number of CPUs to use. This value can be set to 1,
effectively disabling parallel queries, or a specific number which limits the
amount of CPUs that can be used.
In OLTP systems, using a maximum MAXDOP setting of 8 is
recommended in most cases, including those with access to more than 8 CPU
cores. The effort to split and rejoin a query across more than 8 CPUs often
outweighs the benefits of parallelism
In some cases, parallel queries in an OLTP environment are chosen
by SQL Server to circumvent poor design and maintenance practices, most often
as a result of missing indexes or out of date statistics. For example, SQL
Server may decide to perform an index scan rather than a lookup, in which case
it may parallelize the query. A high incidence of parallel queries is typically
accompanied by a large number of CXPACKET waits.
In typical OLTP environments, MAXDOP is often set to 1 to limit
the CPU and memory impact from parallel queries. In such cases, the question
needs to be asked as to why SQL Server is choosing
parallel queries, that is, are the indexes being designed and maintained appropriately?
One of the downsides from setting MAXDOP to 1 is that certain
operations, such as index rebuilds, benefit greatly from parallelism, but are
unable to do so with a MAXDOP 1 setting. In such cases, the MAXDOP setting can
be specified at a statement level. For example, the CREATE INDEX command, an
example of which is presented here, accepts a MAXDOP parameter:
– Use a MAXDOP hint to override the
default server MAXDOP setting
CREATE NONCLUSTERED INDEX [IX_Address_StateProvinceID]
ON [Person].[Address] ([StateProvinceID] ASC)
In this example, we specify MAXDOP = 0 to override the instance
default MAXDOP setting, and thereby permit the index creation to be
parallelized if SQL Server decides that is the best approach.
Like all configuration options, Max Degree of Parallelism can be
set using sp_configure, or via the Advanced page of a Server’s properties
window in SQL Server Management Studio, as shown in figure 1.
Figure 1: The Advanced tab of the Server Properties
window allows changes to Server configuration settings such as Max Degree of
Parallelism and the Cost Threshold
If MAXDOP is left at the default value, or set to a number more
than 1, the threshold at which SQL Server will consider a parallel plan can be
set through the Cost Threshold for Parallelism option.
This value, specified in seconds, represents the estimated time the query would
take if executed serially on a single CPU. Queries estimated to take longer
than this will be considered for parallelism. The default value for this
setting is 5.
In some cases, increasing this value is a better alternative to
setting MAXDOP to 1 when dealing with a large number of (unwanted) parallel queries.
This article is excerpted from the upcoming book SQL Server 2008 Administration in Action by Rod Colledge and published by Manning Publications. It
explains how to configure settings to limit the number of processors used in
parallel query execution. For the table of contents, the Author Forum, and
other resources, go to http://manning.com/colledge/.