Can setting max dop = number of cpu's cause situation where a single query blocks other queries?



  • I have sql server with 8 cpus. Max worker threads setting is say 850. Max dop is say 8. And cost thread hold for parallelism is 50.

    This means sql server will breakdown a query going above cost threshold into 8 threads. Since each threads runs on one cpu, then does this mean no other user will be allowed to run a query until at least one of the currently running 8 threads gets released?

    So does setting max dop = number of cpu's cause situation where a single query blocks other queries?



  • In addition to the other posts:

    Once a task has been given access to the core, it has roughly 4 ms to do what it wants to do. After ~4ms, it yields so the next in line will be given access to the core (cooperative multitasking).

    You can have 3 different outcomes after a task has been given access to the core:

    1. It finishes before the ~4ms, and the session goes back to sleeping. Next in line is given the core.
    2. It isn't done yet - it wants more CPU and is put last in queue (a signal wait). Next in line is given the core.
    3. It encounters a wait (lock for instance) before the ~4ms and is put on a waiting list (a resource wait). Next in line is given the core.

    None of above resulted in blocking.

    There can of course be issues with the thread not giving up the core by it self (non-yielding), but that is not the normal, and troubleshooting should be done in such a situation.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2