通俗的例子解释SQL的各种运行状态

The SQL Server SQLOS uses schedulersto manage the execution of user requests. SQLOS Schedulers map to CPUs.  Assuminga 4-CPU Server, there would be 4 SQLOS schedulers by default.  The following diagrams depict a simplifiedversion of execution model using a single SQLOS scheduler.  The execution model in Figure 1 depicts how SQL Serveruser requests or sessions (denoted by SPIDs) are scheduled for execution.  

Figure 1: Execution Model – Running, runnable and suspendedstatus, Runnable Queue and Wait List

Figure 2 shows how SQL Server sessionsrotate between the following statuses:Running(only one session can be running or executing, per scheduler),Runnable (sessions waiting for CPU), orSuspended.  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 changes affect 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 depicts execution “after”session_ids (or SPIDs) have rotated clockwise due to status changes. 

Figure 3: Execution Model – After status change

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 sessionis moved to the wait list, a wait type is assigned and time is accumulated.When the resource becomes available, the thread is moved to the runnable queueand it executes as soon as the CPU is available.  The clockwise rotation between running,runnable and suspended states continues until the user request is completed.

具体参考微软白皮书：Performance_Tuning_Waits_Queues.doc