CODEWITHSUNDEEP
sql-server
Ronnie
Ronnie

Reputation: 11

SQL Server and the effect of setting MAXDOP

I am confused about what MAXDOP actually does. I have read that it constrains a SQL query to run on the number of processor specified by MAXDOP; and in other articles, they say MAXDOP constrains the number of processors an operator within the query is constrained by.

If I have a system with 4 CPUs and I set MAXDOP to 2, then run a simple query that will have two index scans that get parallelized, I can see that I end up with 5 threads.

Do these 5 threads then get restricted to run on only 2 CPU's due to the MAXDOP setting?

Upvotes: 1

Views: 1360

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

The MAXDOP hint limits both the number of threads used by each parallelism operator and the total number of logical CPUs used by all the parallel operators in the query plan. This post explains this in more detail.

With the 2 parallel index scans mentioned in your question, each parallel scan is limited to 2 threads for a total of 4 parallel threads. Tasks for these parallel threads share the same CPUs as specified by MAXDOP number. No more than 1 worker can be active on the same CPU at a time so the total number of CPUs actually used at any given time for parallel operations is limited by MAXDOP. But you will also have another thread for other plan operators which is not considered part of MAXDOP so the query may use up to 3 cores concurrently.

Run this query during query execution and you should see no more than one task running on a given scheduler at a time:

SELECT  ost.session_id
      , ost.scheduler_id
      , w.worker_address
      , ost.task_state
      , wt.wait_type
      , wt.wait_duration_ms
FROM    sys.dm_os_tasks ost
        LEFT JOIN sys.dm_os_workers w ON ost.worker_address = w.worker_address
        LEFT JOIN sys.dm_os_waiting_tasks wt ON w.task_address = wt.waiting_task_address
WHERE   ost.session_id = 54
ORDER BY scheduler_id;

Upvotes: 4

Related Questions