Symmetric Server Architecture | Inside Microsoft SQL Server 2000

SQL Server uses a single-process, multithreaded architecture known as Symmetric Server Architecture that provides scalable high performance with efficient use of system resources. With Symmetric Server Architecture, only one memory address space is provided for the DBMS, eliminating the overhead of having to manage shared memory.

Traditional Process/Thread Model

To understand the architecture of SQL Server, it's useful to first understand the traditional architectures used by UNIX-based DBMS products. UNIX-based DBMS products are usually structured in one of two ways. One architecture uses multiple processes (or shadow processes), with one process per user, which makes the system quite resource-intensive. The second type of architecture employs a single process that tries to simulate an operating system threading facility by moving in a round-robin way among multiple requests, maintaining a stack for each request and switching to the specific stack for whatever unit is being executed.

NOTE
A stack is a last-in, first-out (LIFO) data structure that's kept in memory. It essentially serves as the control block for the executable unit on an operating system. (In Windows NT/2000, the basic executable unit is known as a thread, often called a lightweight process in other operating systems.) A thread stack stores status data such as function call return addresses, passed parameters, and some local variables.

In the first approach, because each process has its own address space, processes must resort to shared memory to communicate with one another. Unfortunately, shared memory is less efficient to use than the private memory of a process's own address space because of the weight of synchronization mechanisms (semaphores, mutexes, and so on) that are needed to avoid collisions while accessing shared memory. In addition, the implementation of stack switching and synchronized access to shared memory adds overhead and complexity. Adding complexity to a system is never good. The best way to avoid bugs in software and maximize performance is to keep code simple and, better yet, to write no new code when an existing tried-and-true service exists.

In the second approach, called simulated multithreading, the DBMS performs duties usually handled by the operating system. Typically, using such an architecture requires that the executing task be trusted to yield back to the system so another task can be run. If the task doesn't yield (because of software or hardware failure), all other tasks are severely—and perhaps fatally—affected.

SQL Server Process/Thread Model

SQL Server 2000 uses a scheduling architecture that's somewhere in between the two traditional approaches. Windows NT/2000 makes available support for lightweight threads, called fibers. Certain high-volume symmetric multiprocessing (SMP) systems can gain considerable performance benefits by using fibers instead of using threads, and Microsoft wanted to make this functionality possible with SQL Server.

NOTE
A more complete description of the Windows NT/2000 process and thread model is beyond the scope of this book. For more information, see Inside Microsoft Windows 2000, Third Edition, by David Solomon and Mark Russinovich (Microsoft Press, 2000) or Programming Server-Side Applications for Microsoft Windows 2000 by Jeffrey Richter (Microsoft Press, 2000).

To make the best use of fibers, SQL Server takes control of their scheduling; it does so by using one or more operating system threads as its own schedulers. These threads are called UMS threads, or User Mode Schedulers. Whether it's running in thread mode or fiber mode, SQL Server uses only one UMS thread per CPU. Each SQL Server fiber is associated with exactly one UMS thread, and the fiber remains with that UMS thread for its lifetime. Windows NT/2000 can schedule each of the UMS threads on any of the available processors. Each UMS thread determines which of the SQL Server processes associated with that UMS thread should run at any given time. Chapter 17 examines in detail how to control whether SQL Server processes run on threads or fibers, and the benefits and drawbacks of each option.

SQL Server processes that execute non_SQL Server code aren't scheduled by the UMS. These processes include those that run extended stored procedures and those that run distributed queries. These processes are scheduled on normal operating system threads and managed by Windows NT/2000's own scheduler. In addition, when SQL Server is executing a backup or restore operation, additional UMS threads control the progress of the operation.

Multiuser Performance

The efficiency of the SQL Server threading model is borne out by its multiuser performance. SQL Server can efficiently handle hundreds, even thousands, of simultaneous active users. Built-in thread pooling allows workloads of this magnitude without the need for an external Transaction Processing (TP) Monitor, which adds cost and complexity to a system.

NOTE
Of course, questions such as "How many users can SQL Server handle?" and "How big a database can it handle?" never have simple answers. The answers depend on the application and its design, required response times and throughput, and the hardware on which the system is running.

Most systems that just a couple of years ago required a mainframe or large minicomputer-based solution can now be efficiently built, deployed, and managed using SQL Server. Such industry-standard benchmarks as TPC-C can be illuminating. Today's SQL Server can perform workloads that surpass those submitted by the largest mainframe systems of a few years ago. As computer resources continue to grow, SQL Server will extend its reach into systems that have traditionally required a mainframe solution.