Workaround to implement Minimum Degree of Parallelism

Question

Please correct me if I am mistaken but as I understand it, when a query is being scheduled for execution, the engine takes into account the number of available free threads and will adjust the query plan accordingly.

For example, if a machine is highly loaded such that only a single thread is free, a query that normally runs using multiple threads on an unloaded machine could be executed as single threaded.

Is there a way to dynamically query the number of free threads available so that a particular critical query (that runs unacceptably as MaxDop < X) could be delayed (while/waitfor) for execution until a minimum free thread count is available?

Update: A starting point based on Kin's answer:

This seems to follow a simple load test but never returns all idle for some reason. I know nothing about this table.

select count(*) as Cpus, 
       sum(IsIdle) + 1 as IdleCpus  -- +1 since current query should be excluded
  from 
     (
        select Cpu_id, 
               min(convert(int, is_idle)) IsIdle
          from sys.dm_os_schedulers
          group by Cpu_id
     ) q
   ;

Answer 1

You would use a Resource Governor Resource Pool. It doesn't directly specify the degree of parallelism for a particular query, rather it can set minimum and maximum CPU resource availability for a connection.

So

if a machine is highly loaded such that only a single thread is free, a query that normally runs using multiple threads on an unloaded machine could be executed as single threaded

By assigning the session running the parallel query to a resource pool with MIN_CPU_PERCENT set, you can ensure that schedulers are made available to run the plan.

Answer 2

There is no such concept like minimum degree of parallelism.

SQL Server has Maximum degree of parallelism (max DOP) and cost threshold of parallelism. These 2 settings are used by sql server's cost based optimizer on the number of threads to use to run queries.

if a machine is highly loaded such that only a single thread is free, a query that normally runs using multiple threads on an unloaded machine could be executed as single threaded.

If a machine is heavily loaded or a run away query monopolizes the server, then you will run into thread starvation where in you will exhaust the available worker threads. You should adjust the max dop setting to a sensible value and test it out.

Is there a way to dynamically query the number of free threads available so that a particular critical query (that runs unacceptably as MaxDop < X) could be delayed (while/waitfor) for execution until a minimum free thread count is available?

Below query will help you will provide information about the system tasks that have spawned the additional threads

SELECT  s.session_id, r.command, r.status,  
r.wait_type, r.scheduler_id, w.worker_address,  
w.is_preemptive, w.state, t.task_state,  
t.session_id, t.exec_context_id, t.request_id  
FROM sys.dm_exec_sessions AS s  
INNER JOIN sys.dm_exec_requests AS r  
ON s.session_id = r.session_id  
INNER JOIN sys.dm_os_tasks AS t  
ON r.task_address = t.task_address  
INNER JOIN sys.dm_os_workers AS w  
ON t.worker_address = w.worker_address  
WHERE s.is_user_process = 0;

Alternatively, you can check sys.dm_os_waiting_tasks for any THREADPOOL related waits. Also, sys.dm_os_scheduler will tell you the current_workers_count for each scheduler.

You can have some complex logic build in to check for current worker count and then just have a waitfor delay x where x is the delay that you want. In my opinion, it will be an overkill to unnecessarily complicate things.

The best is to tune maxdop, cost threashold of parallelism along with making sure that the stats are up-to-date and gathering the wait stats for your instance. In short, baseline your server and see if you spot any anomalies and then start your tuning exercise.