[Top] [Prev] [Next]

Chapter 2

SQL Server Architecture

Overview

SQL Server 11's multithreaded architecture is designed for high performance in both uniprocessor and multiprocessor systems. Performance and scalability were built into SQL Server from the outset by a design team that had previously developed three other relational database systems. Each module of the original database architecture was assigned a performance specification before coding began and was subsequently tested against the specification. SQL Server 11 is the result of this design approach and is one of the most advanced architectures available for relational database environments.

This chapter provides an overview of SQL Server architecture for both uniprocessor and multiprocessor systems. It covers the following topics:

Architecture that Promotes Platform Independence

 SQL Server provides the open architecture and distributed capabilities to integrate information effectively across distributed, multi-vendor environments. Sybase client/server architecture provides the basis for both hardware independence and software independence because it is designed and implemented using a layered component approach. Each component is a self-contained software block with a specific function and with well-defined upper and lower interfaces. This component approach results in open architecture that gives you a lot of freedom to choose the Sybase or third-party components that best suit your application. It also results in the ability to reuse some components over and over again, as Sybase has done with its Open Server and Open Client products.

SQL Server's Process Model

SQL Server can manage a full load of thousands of client connections and multiple simultaneous client requests without over-burdening the operating system, because it is a multithreaded, single process server. That is, no matter how many users access SQL Server, it consumes only a single operating system process for each CPU and each connection consumes only about 65K of memory.

In systems supported by a single CPU, SQL Server runs as a single process sharing CPU time with other processes as scheduled by the operating system. Figure 2-1 shows a run queue for single CPU architecture in which the process with process identifier (PID) 8 is running on the CPU and PID 6, 7, 1, and 4 are waiting their turn in the run queue for CPU time. PID 7 in the figure is a SQL Server process; the others can be any operating system process.

Figure 2-1: Processes wait their turn in the run queue for a single CPU

Client Implementation

SQL Server clients are programs that open connections to SQL Server. Some examples are ISQL, Open Client Client-Library, and Optima++. A single client can open multiple connections to SQL Server, which launches a new client task (thread) for every new connection. SQL Server uses a dedicated stack to keep track of each client task's state during processing and ensures that only one thread at a time accesses a common, modifiable resource such as memory by using spinlocks. A spinlock is a special type of resource lock that helps synchronize access to code and memory areas that need to be accessed as though the environment were only single-threaded.

From a user's point of view, threading lets you do more than one thing at a time. For instance, from a multithreaded workstation, you can use a compiler to compile a program and at the same time generate a multifile document using a desktop publishing application. While those two programs are running, you can open a mail program and read or write mail. Machines that are not multithreaded can't process any more than one application at a time. To accomplish the same three tasks in a nonthreaded environment, you would have to execute the compiler, desktop publisher, and mail programs one after the other.

The Advantage of Threads Over Processes

Threads are subprocesses, or "lightweight processes" so called because they use only a small fraction of the operating system resources that a process uses. Multiple processes executing concurrently require more memory and CPU time than multiple threads of execution do. They also require operating system resources to switch context (time-share) from one process to the next.

Figure 2-2 illustrates the concept of context switching between three threads over time. In the figure, the large arrows pointing down represent the client task threads. They are receiving CPU time when they are solid (near the arrow head). They are in the run queue waiting for processing when they are broken lines. The small arrows represent context switching between each client task.

Figure 2-2: Threads switching context

For conventional database management systems with a one process-per-user architecture, the overhead of paging, context switching, and locking, in addition to the other operating system functions, can potentially reduce throughput to an unacceptable level. These systems experience reduced performance as the number of users increases, or require significantly more hardware to perform well. Threads, on the other hand, do not need any operating system resources after they are launched and can share memory space with each other. SQL Server's multithreaded kernel architecture, running as a single process for each CPU, handles scheduling, context switching, disk caching, locking, and transaction processing without any operating system overhead.

Figure 2-3 illustrates the difference in system resources required by client connections implemented as processes and client connections implemented as threads. Threads exist and operate within a single instance of the executing program process and its address space in shared memory.

Figure 2-3: Single-threaded versus multithreaded architecture

Symmetric Multiprocessing

Virtual Server Architecture (VSA) extends the performance benefits of Sybase's multithreaded kernel architecture and SQL Server engines to multiprocessor systems. Sybase designed this VSA architecture for SQL Server version 4.8 to make efficient use of hardware and operating system resources.

VSA is Sybase's implementation of symmetric multiprocessors (SMPs) processing¯an environment in which multiple CPUs work together to get work done more quickly than a single processor could. What makes it symmetric is a lack of affinity between processes and CPUs¯processes are not attached to a specific CPU.

Sybase SMP is intended for machines with the following features:

Multiple SQL Server Engines

 In a system with multiple CPUs, you can enhance performance by configuring SQL Server to run using multiple server instances called engines. All engines are peers that communicate through shared memory. The engines perform all database functions, including updates and logging.

Scheduling Engines to CPUs

Figure 2-4 represents SQL Server engines as the non-shaded ovals waiting their turns in the run queue for processing time on one of three CPUs. It shows two SQL Server engines, PID 3 and PID 8, being processed simultaneously. If there was only a single SQL Server engine in a multiprocessing system, SQL Server could not take advantage of multiple CPUs. In a system with multiple CPUs, multiple processes can run concurrently.

Figure 2-4: Processes queue up for multiple CPUs

There is no affinity of engines to particular CPUs. The operating system schedules engines to CPUs as CPUs become available in just the same way it schedules non-SQL Server processes to CPUs. So it makes sense to run the minimum number of other processes on the database machine.

Scheduling Tasks to Engines

Figure 2-5 shows tasks queued up for a SQL Server engine. (There are tasks queued up for engines and engines queued up for CPUs.) SQL Server, not the operating system, dynamically schedules client tasks onto available engines from its run queue. When an engine becomes available, it executes any runnable task.

The SQL Server kernel schedules a task onto an engine for processing by a CPU for a configurable length of time called a timeslice. At the end of the timeslice, the engine yields to the CPU and the next process in the queue starts to run. Figure 2-5 also shows the sleep queue with two sleeping tasks. Tasks are put to sleep when they are waiting for resources or the results of a disk I/O operation. The kernel uses the task's stack to keep track of state from one execution cycle to the next.

Figure 2-5: Tasks queue up for SQL Server engines

Multiple Network Engines

Engines are numbered sequentially starting with engine 0. Other than one exception, all engines do the same work. The exception is this: When a user logs into SQL Server, engine 0 handles the login to establish packet size, language, character set, and other login settings. After the login is complete, engine 0 determines which engine is managing the fewest user connections and it passes the file handle for the new client task to that engine, which then becomes the task's network engine.

Because any engine can be a network engine, SQL Server processing is fully symmetrical. By allowing each database engine to perform its own network I/O, SQL Server 11 scales to the maximum number of network connections that the hardware and operating system can manage, dramatically increasing system throughput. This spreads the network I/O load across multiple hardware network controllers when they are used in large SMP systems. Multiple network engines provide the following additional benefits:

``Multiple Engines Handle Network Connections'' provides an additional discussion on multiple network engines.

Communicating Through Shared Memory

In a multiple CPU configuration, multiple SQL Server engines perform operations that affect data. There must be some way to ensure that the objects don't act completely independently, because if they do, data quickly becomes corrupt. Multiple engines share memory to communicate and use spinlocks to coordinate data access. Figure 2-6 illustrates how all the mutual resources that engines use exist in shared memory, allowing all engines equal access to following resources:

Figure 2-6: Equal access to shared resources in the SMP environment

An SMP Processing Scenario

Figure 2-7 shows the SMP subsystems, which consist of clients, disks, the operating system, multiple CPUs, and the SQL Server executable. It also represents the resources in shared memory, which include data caches and procedure cache, queues for network and disk I/O, structures that perform resource management, a sleep queue for processes that are waiting for a resource or that are idle, and the run queue for processes that are ready to begin or continue executing.

Figure 2-7: SQL Server task management in the SMP environment

Here's how a SQL Server SMP system manages tasks.

Login and Assigning a Network Manager Engine

1. When a new user logs into SQL Server, engine 0 handles the login to establish packet size, language, character set, and other login settings. After the login is complete, engine 0 determines which engine is managing the fewest user connections and passes the file descriptor for the task (new connection) to that engine. For the system in Figure 2-7, the task is assigned to engine 1. The task then sleeps while it waits for the client to send a request.

Checking for Client Requests

2. Engine 1 checks for incoming client requests once every clock tick.

3. When Engine 1 finds a command for the connection, it wakes up the task and places it on the end of the run queue.

Fulfilling a Client Request

4. When the task becomes first in the queue, any available engine can begin execution.

5. In this example, engine 2 executes the task; it parses and compiles it and begins execution.

Performing Disk I/O

6. If the task needs to perform disk I/O, the I/O request is issued and the task sleeps again.

7. Once each clock tick, the pending I/O queue is checked to see if the task's I/O has completed. If so, the task is moved to the run queue and the next available engine resumes execution.

Performing Network I/O

8. When the task needs to return results to the user, it performs the network write on engine 1. Engine 2 puts the tasks to sleep on a network write.

9. As soon as the task that engine 1 is executing yields or is put to sleep, engine 1's scheduler checks to determine if it has any network tasks pending.

10. Engine 1 issues the network writes, removing the data structures from the network I/O queue.

11. When the write completes, the task is awakened and placed in the run queue. When the task reaches the head of the queue, it is scheduled on the next available engine.

The execution cycle for single processor systems is very similar to this except that the network migration described in step 1 and steps 8 and 9 do not take place in the single processor system. A single processor system handles task switching, putting tasks to sleep while they wait for disk or network I/O, and checking queues in the same way.

SQL Server Layout in Memory

SQL Server can handle approximately sixteen users with each additional megabyte of memory. This very low user memory requirement means there is more memory for data caching and fewer disk I/Os, which is a performance advantage. When SQL Server starts, it allocates memory for the executable and other static memory needs. What remains after all other memory needs have been met is available for the procedure cache and data cache. Figure 2-8 shows the SQL Server components that take up space in shared memory.

Figure 2-8: SQL Server layout in memory


[Top] [Prev] [Next] [Bottom]