I came across the MAXDOP option term recently. It is a new term for me and I made a google search and found this article. But to be honest I could not understand it . Also they are talking about some parallelism and may be these two are interlinked in some way. In simple terms but informative way, can anybody please share some knowledge on both the topic and in which situation should we use it?
Thanks
When a query is run, SQL Server can create a query plan that is executed on more than one processor. This is what query parallellism is about. Configuring the MAXDOP option in the server limits the number of CPUs that can be used in a query plan.
This option does NOT limit the number of CPUs that SQL Server as such is using (for example due to license limitations). It only limits the number of CPUs that SQL Server will use to run a single execution plan.
When you have multiple processors in a computer, SQL Server can take advantage of using more than one processor by creating multiple threads, threads are how the operating system takes advantage of multiple processors by using different execution paths. There are two general factors that affect whether or not a parallel plan is generated. The first is whether or not you have enabled multiple processor use. By default the server is set to zero, which means it will use all processors available, but you can limit it to only using one or some sub-set of processors (there are other options there as well, but I won't get into them at this point). Second, you can set the cost threshold for parallelism. This uses the estimated cost of an execution plan as part of a series of calculations that determine when to use multiple processors, if available. The default for this value is 5, and in my opinion, WAY too low. I usually set it to somewhere between 25-35, depending on the system and the queries being run against it.
MAXDOP is a query hint that will allow you to control, for a given query, how parallelism is applied. It is a useful hint when you have a query that is hitting parallelism, but not benefiting from it. However, most of the time it is used incorrectly when the cost threshold for parallelism should be adjusted upwards instead. Seting the MAXDOP to 1 will prevent the query for using multiple processors through multiple threads.
