When setting MAXDOP at the server level what does this actually mean? I always thought it gave you the ability to restrict the number of processors any one SQL process can use so for example if you have an 8 proc server and set MAXDOP to 5, no process could use more then 5 CPUs at a time. However, I was told yesterday it does not limit the number of CPUs available to a process, but it restricts process threads. But aren't they the same thing? I know there are sub-threads that you can see by looking at the ecid column in sysprocesses, so is that what you are really restricting when using MAXDOP or are you restricting the number of procs?
When a query is executed, the text of the query determines what the query needs to do which generates the query plan, then the optimizer makes a lot of decisions about how that plan needs to be implemented. While creating the plan the analyzer notes where tasks are possible to do in parallel, which means that those parts of the query don't depend on each other... During execution, if there are tasks ready to execute that the optimizer thinks could execute enough faster in parallel to justify the cost of marshalling and if there are enough threads ready to execute those tasks in parallel, then those tasks will be launched in parallel.
MAXDOP sets a threshold on the number of threads that are available to one process (spid) at one time. The net effect of setting MAXDOP is to manage how many active threads that a query can use. MAXDOP can always be set at the server and the spid levels, and in some versions of SQL Server it can be set at other levels too. The effects of all MAXDOP settings are cumulative, so the lowest setting currently in effect determines how many threads could be used by a given query.
So what actually is a thread if it is not a CPU thread? I ask because here is a BOL definition. It is from this definition that I thought a thread is a CPU thread and there is only one CPU thread per processor.
Threads allow complex applications to make more effective use of a CPU, even on computers that have a single CPU. With one CPU, only one thread can execute at a time. If one thread executes a long-running operation that does not use the CPU, such as a disk read or write, another one of the threads can execute until the first operation is completed. By being able to execute threads while other threads are waiting for an operation to be completed, an application can maximize its use of the CPU. This is especially true for multi-user, disk I/O intensive applications such as a database server. Computers that have multiple microprocessors or CPUs can execute one thread per CPU at the same time. For example, if a computer has eight CPUs, it can execute eight threads at the same time.
I believe you are taking the words "execute one thread per CPU at the same time" too literally.
As an example, consider my teenage son and the 15 instant message chat sessions he has going at any given time. He is actively chatting with all 15 different people. However, at any given point in time, he can only be typing or reading one chat window. He can quickly do that work and move to the next chat session that needs his attention. At various times, he is waiting for the other people to respond and he sits idle. So he is one person, working on 15 different chat sessions as they need attention.
If you think of my son as a CPU and the various chat windows as threads, you can start to understand that, at any given point in time, only one thread is being worked on, but a CPU can have more than one thread available to be worked on.
Does this make sense? If the optimizer realizes it can execute a query faster by breaking into parts and running them in parallel, then it can run the individual parts as they need attention. But, at any given point in time, only one thread is being worked on by the CPU.
Hopefully this helps and doesn't muddy the waters.
Nice analogy. That makes sense. How does too many sub-threads impact a processor and is there any rule of thumb for determining a good MAXDOP setting? Unfortunately the application we are trying to tune is a vendor product with components that cause all 8 CPUs (4 dual-core 3GHz) to hit 100% utilization. We want to reduce the possibility of any one process from consuming all CPU resources.
This is actually a separate issue, so I'm responding separately.
A good MAXDOP setting is not using MAXDOP. The only really good use I've ever seen for MAXDOP is as triage for dealing with problems inside the SQL engine. There are very rare cases where the optimizer will make truly awful choices about parallelization of queries, and even more rare cases where there are actual bugs you need to circumvent. Other than these cases, I don't know of any good use for MAXDOP.
The issues involved in thread scheduling, thread overhead, marshalling, optimizations, etc are huge... These issues can consume immense amounts of both time and beer to properly understand and appreciate. More than one PHD thesis has been done on analyzing and optimising very small parts of these topics. As old maps used to say near their edges... There be Dragons here!