0

Say for a operator there are 8 threads. One control thread and 7 worker threads.

Do threads that don't have any work, that is - either of the below consume the 4ms cpu time?:

  1. Thread that has finished fetching the data
  2. Thread that doesn't fetch any data (ones showing 0 rows processed)

I understand they will consume thread from the max worker threads, but do these consume the 4ms of cpu time slice?

In other words, are such threads placed in RUNNING/RUNNABLE queue?

5
  • Why would a thread with nothing to do be scheduled to run from SQL's point of view? Threads run if Windows schedules them. Commented Jan 21 at 11:58
  • @SeanGallardy I'm guessing OP means when a query goes parallel but is unbalanced such that some of the parallel threads have 0 rows for the "Actual Number of Rows For All Executions" in the query plan. I'm also guessing the 4ms they're referring to is the quantum allocated to each thread.
    – J.D.
    Commented Jan 21 at 14:02
  • @J.D. - yes this is the question. Whether that thread also get the 4ms cpu slice and is part of the RUNNING/RUNNABLE queue. Or maybe such threads are in suspended state?
    – variable
    Commented Jan 21 at 15:17
  • 2
    Ah, so you're actually asking if there is some sort of skew to parallelism, if it'll still execute the thread? Is that correct @variable ? Commented Jan 21 at 20:40
  • Absolutely yes.
    – variable
    Commented Jan 22 at 10:50

1 Answer 1

3

Do threads that don't have any work, [...] consume the 4ms cpu time?

They will need to do some work, how much is unknown and will depend on the requirement of the operator, the mode, and how it was made to execute. Regardless of that, the thread will still be created/re-used and the task executed, thus it will use some cpu and be scheduled, yes.

Whether or not it will continue to need to do so depends on the above stated, which is ultimately how much cpu it ends up using. If the thread is only set to process an input and the input is skewed such that there is no input to that thread, there will be, at a minimum, setup code and shut down code, along with any intermediate items (such as passing end of rowset if needed) before it finishes.

One last point, while SQL Server attempts to have threads only use 4 ms of time (what people refer to as the quantum), a thread can technically take however much time it wants. It's up to Windows to schedule the thread and it's up to whatever code is executing in the task to voluntarily yield (or will yield at different points, for example if it hits a wait) on the SQL scheduler.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.