October 30, 2017 at 8:02 am
Hi all.
I'm configuring/testing some aspects of Resource Governor, and right now , i'm trying to set up a limit into DOP.
Even specifying the value 1 (max_dop = 1 in the workload group), and confirming through the sys.dm_exec_sessions, that i'm currently in the group "limited", my queries still run in parallel.
Is it possible that, even with resource governor, sql still runs query in parallel when its necessary?
Thanks!
October 31, 2017 at 11:47 pm
I had to research this but found a good explanation here:
Which is linked on https://www.red-gate.com/simple-talk/sql/learn-sql-server/resource-governor/
When you prevent a workload group fromrunning tasks in parallel using the MAX_DOP = 1 setting, you may want tovalidate this by running a query and examining the query plan. You will surely besurprised to find that the graphical execution showplan will still displayparallel operators. If you look closer, the showplan XML (actual, notestimated) will have <QueryPlan DegreeOfParallelism="0" ... />, and only one thread listed inside of the RunTimeInformation node:
So when the server MAXDOP > 1, then parallel plans are generated, but Resource Governor limits the number of threads available to the plan to 1.
You should see that in the Actual execution plan, click on the plan operator before Parallelism, something like Index Scan, press F4 to show Properties tab, expand Actual Number of Rows - there will be Thread 0, Thread 1, Thread 2 etc, meaning the operator was services by multiple threads. With Resource Governor limited to 1, when you expand Actual Number of Rows, it shows "All threads" which is the same as running in Serial mode.
The other way to see is use this query to show number of workers for the session running the parallel query:
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=75 -- set session id here
order by scheduler_id;
November 3, 2017 at 4:11 am
Hey Andrew!
First of all, sorry for the delay in my feedback. I was offline for a couple of days.
Secondly: thank you so much for the explanation and for the links / documentation. Also, thanks for the time that you've spent in research such material
I've run some tests and all that you pointed is correct.
Regards,
Luiz
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply