Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Managing Max Degree of Parallelism

By Herve Roggero,

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.

Total article views: 30102 | Views in the last 30 days: 37
 
Related Articles
FORUM

The maximum recursion 100 has been exhausted before statement completion

How To Over Come This Error : The maximum recursion 100 has been exhausted before statement completi...

FORUM

Maximum row size in SQL Server 2000.

Maximum row size in SQL Server 2000.

FORUM

MAXIMUM instance

MAXIMUM instance

FORUM

Maximum amount of "If" statements and "Select CASE"

What is te maximum allowed nested "If" statements and max allowed "CASE" in "Select CASE"

FORUM
Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones