The key features required of an enterprise-level database server platform are preemptive scheduling, virtual paged memory management, symmetric multiprocessing, and asynchronous I/O. Unlike some other database platforms, SQL Server relies on the Windows operating system to provide these capabilities and uses them fully. Under Windows NT and Windows 2000, the SQL Server engine runs as a single process and, within that process, uses multiple threads of execution. SQL Server relies on the operating system to schedule each thread to the next available processor.
SQL Server Threads
Unlike some other DBMS platforms that run as multiple processes under Windows NT, SQL Server runs as a single process using multiple operating system threads. SQL Server uses a single-process, multithreaded architecture that uses a single memory address space for the DBMS, eliminating the overhead of having to manage shared memory. The threads are scheduled onto a CPU by a User Mode Scheduler, which gives SQL Server more control over the scheduling of threads and fibers.
SQL Server always uses multiple threads, even on a single-processor system. SQL Server keeps separate thread pools for different operations. One pool of threads handles the Net-Libraries that SQL Server simultaneously supports, another thread handles database checkpoints, another handles the lazywriter process, and another handles the log writer, as well as the thread pool for general database cleanup tasks , such as periodically shrinking a database that is in auto-shrink mode or cleaning up ghost records. Most importantly, for handling user requests and queries, there is pool of threads that handles all user commands, the worker thread pool.
SQL Server establishes a pool of worker threads to handle all user connections instead of using a separate thread for each connection and managing hundreds of separate operating system threads to support hundreds of users. Most often, the worker threads are simply waiting for input from the client applications. Instead of having dedicated threads doing nothing most of the time, SQL Server maintains a pool of worker threads and assigns worker threads to connections as needed.
When a client submits a request, the SQL Server network handler places the request in a completion queue (actually, the operating system's IOCompletion port) where it is picked up by the next available worker thread in the pool. If no idle worker thread is available to wait for the next incoming request, SQL Server adds a new thread to the worker thread pool. It does this until the limit set by the max worker threads configuration option is reached (the default limit is 255 threads, which is generally sufficient for most installations). When the limit has been reached, the client's request has to wait in the queue for a worker thread to be freed. As the workload decreases, SQL Server gradually eliminates idle threads to improve resource and memory utilization.
In SQL Server 2000, complex queries can be broken into component parts that can be executed in parallel on multiple CPUs. This intraquery parallelism occurs only if the number of processors available is greater than the number of connections currently processing.
Under the normal pooling scheme, a worker thread remains assigned to the user request until the request is completed. When a user process requests a resource that is not available, such as a memory page that isn't yet in RAM, only that thread (and therefore, only the associated client connection) is halted until the request can be completed.
By default, SQL Server 2000 requests CPU resources from Windows NT or Windows 2000 to process a thread and the operating system then assigns each thread to a specific CPU. SQL Server lets the operating system distribute threads evenly among the CPUs on a system. At times, Windows NT or Windows 2000 can also move a thread from one CPU with heavy usage to another CPU. The affinity mask option in SQL Server 2000 can be used to exclude one or more CPUs from processing SQL Server threads and associate SQL Server threads with specific CPUs, but it is recommended that you leave it up to the operating system to schedule and balance the threads among the available CPUs.