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/.