Symmetric Server Architecture | Inside Microsoft SQL Server 7.0 (Mps)

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 and contrast the architecture of SQL Server, it's useful for you to first understand the traditional architectures that have been used by UNIX-based DBMS products. UNIX-based DBMS products are usually structured in one of two ways. In the first way, multiple processes (or shadow processes ) are used, 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 that specific stack for whatever unit is being executed.

NOTE
A stack is a LIFO (last-in, first-out) data structure kept in memory that basically serves as the control block for the executable unit to the operating system (a thread on Windows NT, often called a lightweight process on other operating systems). A stack stores status data such as function call 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 efficient 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 approachsimulated multithreadingthe 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 will be severelyperhaps fatallyaffected.

SQL Server Process/Thread Model

SQL Server 7 uses a scheduling architecture that's somewhere in between the two traditional approaches. Windows NT makes available support for lightweight threads, called fibers . Certain high-volume symmetricmultiprocessing (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.

SEE ALSO
A more complete description of the Windows NT process and thread model is beyond the scope of this book. For more information, refer to Inside Windows NT , Second Edition, by David Solomon (Microsoft Press, 1998).

To enable the option of using fibers, SQL Server takes control of its own scheduling of processes. It enables this option by using one or more NT 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 process is associated with exactly one UMS thread, and the process will remain with that UMS thread for its lifetime. Windows NT 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 15 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 include processes that run extended stored procedures and processes that run distributed queries. These processes are scheduled on normal operating system threads and managed by Windows NT'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 is able to efficiently handle hundreds, even thousands, of simultaneous active users. Built-in thread pooling allows workloads of this magnitude to be performed without the need for an external Transaction (TP) Monitor, which adds cost and complexity to a system.

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

The majority of systems that just a couple years ago required a mainframe or large minicomputer-based solution can now be efficiently built, deployed, and managed with 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 traditionally would have required a mainframe solution.