Sunday, November 25, 2012

SQL Server - Wait Statistics : 2 - SQL Server Thread and Task Architecture

  • Each Physical CPU has one or many mapped Logical CPUs. A Logical CPU has to have one and only one mapped Physical CPU.
  • One scheduler is assigned to one Logical CPU. However, a logical CPU can have multiple schedulers.
  • A scheduler maintains two structures, Runnable queue and Wait list. Runnable Queue is maintained for the collection of Runnable tasks. As Queue is a First in First out operation, the first task in the queue waiting for CPU time will be executed first and then the second in the queue.The Wait/Suspended List is an un-ordered set of tasks waiting for some resource. Once the resource is available, the task is pushed to Runnable Queue. So the task in Wait list waits until the resource the task is waiting for is available.
  • A scheduler (logical CPU) is assigned a set of workers (Thread or fiber depending on configuration) to execute tasks.
  • If you pause and think about the previous 4 points, it becomes logical that each logical CPU or scheduler would maintain its own Runnable Queue and Wait List. And since each logical CPU is an independent CPU for SQLOS, each Logical CPU/Scheduler has its own work and task assigned to it. And thus multiple CPUs would lead to parallellism.
  • The worker is bound to the task until its finished. This means if for some reason the task is suspended due to unavailable resources, the worker too waits. The worker is assigned a new task only when it finishes the current task in context.
  • At a given point of time, a task can be in either in Runnable, Suspended or Running state. It may change the state in the below fashion
    • Running >> Suspended >> Runnable >> Running
While a task is running, it might need a resource (data page, memory, lock etc)
which might not be available. If the resource is unavailable, then the task is changed
to Suspended state. And it remains there until it gets the resource its waiting for.
Once the resource is available the task is moved to Runnable state. This means that
the task is ready to be processed and it waiting in Queue for the processor time. It
gets executed once the tasks before it in the queue are executed.
    • Running >> Runnable >> Running
While a task is running, it might directly enter into a runnable state without going in
suspended state. This will happen if the task meets its quantum exhaustion. A
quantum exhaustion is the time limit a task can use processor time.
In SQL Server its 4 milli seconds. This means after 4 milli seconds, the current task
will be pushed to the bottom of runnable queue and the task on the top of the queu
will be popped for processing.