Professional SQL Server® 2005 Performance Tuning

4.2. Architecture

The SQL Server Operating System (SQLOS) uses schedulers to manage the execution of user requests. The number of schedulers defaults to the number of logical CPUs in the server because only one scheduler at a time can be executing on a CPU. If you have a 4 CPU server, you'll have four schedulers, 8 if they are dual-core, and 16 if they are dual-core and hyper-threaded. See Chapter 6 for more details on CPU types and configuration.

Within a scheduler, a session (actually a task associated with a session) can be in one of three states: running, runnable, or suspended. (There are also three other states: pending, spinloop, and done; however, they're not important to the concept being discussed here.) Only one session can be running on a scheduler at any one time. All sessions waiting to run are in a runnable queue. Sessions that stopped executing to wait for something have a status of suspended. This is illustrated in Figure 4-1.

Windows uses a scheduling model called pre-emptive scheduling, which basically means that it decides when a thread running a task needs to make way for another task and switches it out. This works great for a server running lots of different services of equal importance, but for a server that is dedicated to running SQL Server it would be bad because a SQL Server task might get switched out unexpectedly to give a minor Windows task some CPU time. SQL Server instead has its own scheduler that uses a non-preemptive or cooperative model whereby ...