Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Posts
    95

    Unanswered: Help understanding MAXDOP Setting

    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?

    Thanks, Dave

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That's easy... Neither.

    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.

    -PatP

  3. #3
    Join Date
    Jan 2003
    Posts
    95
    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.

  4. #4
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    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.

  5. #5
    Join Date
    Jan 2003
    Posts
    95
    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.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I wouldn't try using MAXDOP for that purpose, it is the wrong tool for that job... In theory you could use a hammer to install lightbulbs, but I wouldn't try that either.

    If your "one process" is SQL Server, then you could use the CPU affinity mask. If your "one process" is a spid, I'd suggest trying the Query Governor. If you do, let me know how that works out.

    -PatP

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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!

    -PatP

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think you can see the cross-posting at the foot of the thread (Linkbacks)??
    ...unless they're created because of your post..


    Confused
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •