MAXDOP stands for MAXimum Degree Of Parallelism. It's the upper limit on the number of CPUs the SQL Server instance will use for a parallel query. Great. What is a 'parallel query'? Basically, SQL will use multiple processors to speed up the execution of queries. See below for the official MSDN definition.
Parallel Query Processing
SQL Server provides parallel queries to optimize query execution and index operations for computers that have more than one microprocessor (CPU). Because SQL Server can perform a query or index operation in parallel by using several operating system threads, the operation can be completed quickly and efficiently.
During query optimization, SQL Server looks for queries or index operations that might benefit from parallel execution. For these queries, SQL Server inserts exchange operators into the query execution plan to prepare the query for parallel execution. An exchange operator is an operator in a query execution plan that provides process management, data redistribution, and flow control. The exchange operator includes the Distribute Streams, Repartition Streams, and Gather Streams logical operators as subtypes, one or more of which can appear in the Showplan output of a query plan for a parallel query.
After exchange operators are inserted, the result is a parallel-query execution plan. A parallel-query execution plan can use more than one thread. A serial execution plan, used by a nonparallel query, uses only one thread for its execution. The actual number of threads used by a parallel query is determined at query plan execution initialization and is determined by the complexity of the plan and the degree of parallelism. Degree of parallelism determines the maximum number of CPUs that are being used; it does not mean the number of threads that are being used. The degree of parallelism value is set at the server level and can be modified by using the sp_configure system stored procedure. You can override this value for individual query or index statements by specifying the MAXDOP query hint or MAXDOP index option.
Where can I find this setting?The SQL Server query optimizer does not use a parallel execution plan for a query if any one of the following conditions is true:
- The serial execution cost of the query is not high enough to consider an alternative, parallel execution plan.
- A serial execution plan is considered faster than any possible parallel execution plan for the particular query.
- The query contains scalar or relational operators that cannot be run in parallel. Certain operators can cause a section of the query plan to run in serial mode, or the whole plan to run in serial mode. (msdn 3)
There are two ways:
- With a UI, under Server Properties, click the Advanced group on the left hand vertical options menu. The last column will be 'Parallelism' and it is the third option.
- With SQL script:
EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure 'max degree of parallelism' GO
This is an interesting question that is highly debated at really nerdy happy hours. The first answer always given is the generic 'Well it depends on your situation'. This is very true even though it is a frustration answer for people to hear. Its more satisfying hearing a number 'commonly' used.
By default, SQL Server sets this value to 0 which means it is dynamic as there are no limits to how many CPUs SQL can spread out a query over (e.g all available processors on the SQL box).
For most large multi-user ERP systems, 1 is pretty common. Some people would get pissed about this value setting since it disables all query parallelism. Others would say that this good though as it eliminates the CXPACKET waits (a thread is waiting on a parallel thread to complete) associated with parallelism. Again the rebuttal is that CSPACKET waits are completely normal and expected behavior on a multiprocessor system and changing this value is not a solution as something else in the system isn't scaling properly. I'm not getting into this deeper but if its your thing, here is a good article I found: SQLBlog: Tuning ‘cost threshold for parallelism’ from the Plan Cache
According to Top Tips for Maximizing the Performance & Scalability of Dynamics AX 2009 systems on SQL Server 2008: "The AX perf team recommends that MAXDOP be set to 1 (again, this is the most common recommended setting for most large multi-user EPR systems). The reason is for this is for performance consistency. It is the most critical goal that all users 'see' the same basic resource use on a consistent basis. This ensures that all users share the resources in an equitable manner.
Since customer to customer setups can be so radically different, if the resources, know how, and time are available, it couldn't hurt to play with this setting and see if there are performance gains setting this to something like 2, 4, 1/2, etc. The trick is knowing what to measure and doing benchmarks thoroughly to know what to measure against. If this can't be done, no use in changing these values.
Parallism is more widely optimized in high performance single user (or low number of users) scenarios such as Data warehouses and/or bulk loading. Regardless of the setting, if you don't know what you're doing, don't set this value.
How do I change the MAXDOP value?
You can change this value through sp_config tool, SSMS, or T-SQL. You can also change it from the SQL server properties
Figure 1 - The Max Degree of Parallelism setting in SQL Server properties
Thanks for this wonderful post and hoping to post more of this! Please Visit:
ReplyDeletepharmacy software
retail systems
retail system