SQL Servers Thread and Task Architecture

SQL Server’s Thread and Task Architecture

All DBMS products, like all major applications, are built on solid multithreading systems architecture. We don’t need to discuss threading technology here at any deep level save to say that threading technology allows complex processes to make more effective use of the CPU. If you are new to software engineering practice or new to the concept of threading, you can find many books on the subject, and the subject is well covered in the MSDN library and in the Microsoft online knowledge base.

In the not-too-distant past, the only way we could execute processes in a system concurrently was to have multiple instances of applications running on the operating system. A multitasking kernel (the OS) was responsible for deciding which applications got preferential use of the CPU and when. This was how the early Windows operating systems worked. Later it became possible for an application to obtain a level of concurrent processor use within the application’s address space by executing multiple process threads. Multithreading led to more reliable software because the operating system could properly manage the order and priority of thread execution.

It is important to understand that you cannot execute threads at exactly the same time on a single CPU, because threading is a synchronous technology. The operating system can, however, determine when to kill a thread or allow one thread to execute ahead of others (priority). On multiple-CPU systems, threads can run in a true concurrent state by being executed on parallel processors. In other words, if a computer has eight processors, it can concurrently execute eight threads.

An allied technology to threads is fibers. Fibers are similar to threads, but the application, rather than the operating system gets to control them. Fiber technology does not require the operating system’s overhead of threading, which requires resources in both the user mode and the kernel mode of the OS. That said, fibers are not exactly separate from threads. They are derived from threads. A thread can spawn multiple fibers.

Fiber execution is an advanced technology. Many different activities go on in SQL Server at the same time, and all these processes compete for CPU time. At the core of the DBMS we find what appears to be functionality that is very similar to the part of the operating system that works with threads. This SQL Server kernel thus allows the system to work with both threads and fibers, without having to engage the kernel and user mode resources of the OS. And each instance of SQL Server maintains a pool of threads and fibers that are executed in this thread-fiber layer.

This is just one of the features that makes SQL Server as powerful as it is. You control the configuration of the pool via the max worker threads configuration option (see Chapter 4). You can also determine if your Enterprise Edition of SQL Server makes use only of threads or of threads and fibers. If you choose threads and fibers, the server is then placed into fiber mode. In fiber mode, SQL Server establishes one thread per CPU and then establishes one fiber per concurrent client connection. The number of fibers issued is based on what you set in the max worker threads configuration option (the default is 255).

When SQL Server receives a T-SQL statement over the wire, it issues a free thread or fiber (depending on the mode) from the thread stack to the inbound connection. If there are no free threads or fibers and the max worker threads value has not been reached, SQL Server will allocate a new thread or fiber. If the thread ceiling has been reached, SQL Server will have to find threads or fibers that are not being used and free them up.

While you may be tempted to set the max worker threads configuration higher than the default, you should note that a point will be reached at which SQL Server performance will begin to deteriorate. The reason is simple: Thread and fiber management and creation themselves consume resources, and a runaway thread scenario can bring an application to a standstill. In any event, most of the threads created spend a lot of time waiting around doing nothing, and they can safely be freed and reused without reallocation of resources-so you should never have a need to set the max worker threads value higher than the 255 default.