Managing Max Degree of Parallelism (펌)

Managing Max Degree of Parallelism

By Herve Roggero, 2003/06/23

Total article views: 24782 | Views in the last 30 days: 229

  Rate this |   Join the discussion |   Briefcase |   Print

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.

By Herve Roggero, 2003/06/23

Total article views: 24782 | Views in the last 30 days: 229

원본출처 : http://www.sqlservercentral.com/articles/Configuring/managingmaxdegreeofparallelism/1029/#