SPID Execution Model

Figure 1: Execution Model – Running, runnableand suspended status, Runnable Queue and Wait List

 

Figure 2 shows how SQL Serversessions rotate between the following statuses: Running (only one session can be running or executing, perscheduler), Runnable (sessionswaiting for CPU), or Suspended.  SPIDs with suspended statuses are placedin Waiter List until the requested resources are available. If a running sessionneeds a data page that is not in cache, or needs a page that is blocked byanother user’s lock, the session is moved to the wait list. The next SPID or session_idin the runnable queue is scheduled to start running. 

Figure 2: Execution Model – How status changesaffect SPIDs

 

The status change sequence of events is as follows:

1. SPID60 needs a page not in cache.  Thus its status changes from Running to Suspended with wait type IO_Completion
2. SPID60 moved to Waiter List
3. SPID51 moves from Runnable queue with a runnable status to Running status, SPID64 then moves to the top of the Runnable queue
4. SPID56 is waiting for a parallel process to complete.  When the parallel process is completed, the status for SPID56 changes from Suspended with wait type CXPACKET to Runnable
5. SPID56 moved to the bottom of the Runnable queue

 

Figure 3 depictsexecution “after” session_ids (or SPIDs) have rotated clockwise due to statuschanges. 

 

Figure 3: Execution Model – After statuschange

The wait list means a thread has to wait for aresource. Example of resource waits includes IOs to complete, a lock to bereleased, a memory grant, and so on. When the session is moved to the waitlist, a wait type is assigned and time is accumulated. When the resourcebecomes available, the thread is moved to the runnable queue and it executes assoon as the CPU is available.  Theclockwise rotation between running, runnable and suspended states continuesuntil the user request is completed.

A session status of ‘Sleeping’ indicates SQL Server is waiting for thenext SQL Server command.