Although SQL Server might seem to offer each user a separate operating system thread, the system is actually a bit more sophisticated than that. Because it's inefficient to use hundreds of separate operating system threads to support
hundreds of users, SQL Server establishes a pool of worker threads.
When a client issues a command, the SQL Server network handler places the command in a "completion queue" and the next available thread from the worker thread pool takes the request. Technically, this queue is an operating system facility called an IOCompletion port. The SQL Server worker thread waits in the completion queue for incoming network requests to be posted to the IOCompletion port. If no idle worker thread is available to wait for the next incoming request, SQL Server dynamically creates a new thread until the maximum configured worker thread limit has been reached. The client's command must wait for a worker thread to be freed.
Even in a system with thousands of connected users, most are typically idle at any given time. As the workload decreases, SQL Server gradually eliminates idle threads to improve resource and memory use.
The worker thread pool design is efficient for handling thousands of active connections without the need for a transaction monitor. Most competing products, including those on the largest mainframe systems, need to use a transaction monitor to achieve the level of active users that SQL Server can handle without such an extra component. If you support a large number of connections, this is an important capability.
NOTE
In many cases, you should allow users to stay connected—even if they will be idle for periods of, say, an hour—rather than have them continually connect and disconnect. Repeatedly incurring the overhead of the logon process is more expensive than simply allowing the connection to remain live but idle.
A thread from the worker thread pool services each command to allow multiple processors to be fully utilized as long as multiple user commands are outstanding. In addition, with SQL Server 2000, a single user command with no other activity on the system can benefit from multiple processors if the query is complex. SQL Server can break complex queries into component parts that can be executed in parallel on multiple CPUs. Note that this intraquery parallelism occurs only if there are processors to spare—that is, if the number of processors is greater than the number of connections. In addition, intraquery parallelism is not considered if the query is not expensive to run, and the threshold for what constitutes "expensive" can be controlled with a configuration option called cost threshold for parallelism.
Active vs. Idle
SQL Server may consider many database connections idle, even though the associated human end user might be quite active, filling in the data entry screen, getting information from customers, and so forth. But those activities don't require any server interaction until a command is actually sent. So from the SQL Server engine perspective, the connection is idle.
When you think of an active user vs. an idle user, be sure to consider the user in the context of the back-end database server. In practically all types of applications that have many end users, at any given time the number of users who have an active request with the database is relatively small. A system with 1000 active connections might reasonably be configured with 150 or so worker threads. But this doesn't mean that all 150 worker threads are created at the start—they're created only as needed, and 150 is only a high-water mark. In fact, fewer than 100 worker threads might be active at a time, even if end users all think that they're actively using the system all the time.
Under the normal pooling scheme, a worker thread runs each user request to completion. If a given thread performs an operation that causes a page fault, only that thread, and hence only that one client, is blocked. (A page fault occurs if the thread makes a request for a memory page that isn't yet in RAM, so the virtual memory manager of the
operating system must swap that page in from disk. Such a request for memory must wait a long time relative to the normal memory access time because a physical I/O is thousands of times more expensive than reading real memory.) Now consider something more serious than a page fault. Suppose that while a user request is being carried out, a bug is exposed in SQL Server that results in an illegal operation that causes an access violation (for example, the thread tries to read some memory outside the SQL Server address space). The operating system immediately terminates the offending thread—an important feature of a truly protected operating system. Because SQL Server makes use of structured exception handling in Windows NT/2000, only the specific SQL Server user who made the request is affected. All other users of SQL Server or other applications on the system are unaffected and the system at large will
not crash. Of course, such a bug should never occur and in reality is rare. But this is software, and software is never perfect. Having this important reliability feature is like wearing a seat belt—you hope you never need it, but you're glad it's there if a crash occurs.
NOTE
Since Windows 98 does not support SMP systems or thread pooling, the previous discussion is relevant only to SQL Server running on Windows NT/2000. The following discussion of disk I/O is also relevant only to SQL Server on Windows NT/2000.