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.

Max MAXDOP?

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)

WITH (MAXDOP=0)

GO

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

Cost Threshold for Parallelism

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.




Array

One Response to “Configuring the Maximum Degree of Parallelism”

  1. So are you suggesting to set a max to 8 (if applicable) for OLAP dbs?

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |