Hardware Trends and Their Effect on SQL Server 2005


When SQL Server 7.0 was developed, Microsoft realized that they had to build an abstraction layer between the Microsoft Windows operating system kernel and the user-mode application that would become SQL Server. They faced many challenges but solved them through the creation of the User Mode Scheduler (UMS). The UMS worked by essentially "fooling" Windows; in effect, it enabled SQL Server to control Windows resources such as thread allocations. This worked very well on the hardware of the day, and the UMS concept was carried forward into SQL Server 2000. Unfortunately, as hardware became more sophisticated, the limitations of the UMS became apparent.

Hardware Building Blocks

The heart of any computer system, the central processing unit (CPU or "processor"), is the hardware component that is responsible for executing tasks. To execute tasks, the processor utilizes a unit of work known as a thread. If a program is written to be multithreaded, it can split itself into two or more simultaneously running tasks. Multiple threads can be executed in parallel on many computer systems. This multithreading generally occurs by time slicing, wherein a single processor switches between different threads, which means the processing only appears to be simultaneous. On a machine with multiple processors (or processor "cores"), threading can be achieved via multiprocessing, which means that different threads can literally execute simultaneously on different cores. Windows supports the use of threads as well as a technique known as fiber mode scheduling, which is also known as "lightweight pooling." Fibers are scheduled by the application itself and run in the context of a thread, enabling multiprocessing.

Hardware Trends

Computer hardware today is increasingly diverse, and it is very difficult to develop applications that exploit the hardware for performance and scalability. It wasn't that long ago that computers with multiple CPUs were rare and used only in sophisticated business environments. Today, multiple CPU cores are commonplace, and features such as Symmetric Multi Processing (SMP) are often present in household systems.

Manufacturers continue to find ways around the limitations of their hardware platforms and build these solutions into new systems at astonishing rates. For example, it has been generally accepted that 4 GB of RAM is the upper limit of supportable memory for 32-bit CPUs, but that hasn't stopped innovators from pushing for more.

Using Non-Uniform Memory Architecture (NUMA) is becoming a standard method to enable support of more than 4 GB of RAM. Some processor manufacturers have built CPU architectures that support cache-coherent or CC-NUMA architectures that enable utilizing large amounts of memory and cache for the CPU, making NUMA available on even low-end systems. Larger enterprise computer systems with as many as 64 CPUs and 512 GB or more memory are becoming more common today. As the number of CPUs within a machine increases, hardware manufacturers are beginning to standardize on CC-NUMA architectures.

NUMA Architecture

Modern computer systems generally have more than one system bus, each serving a set of processors (or processor cores). Each set of processors has its own memory, known as local memory, and might have its own input/output (I/O) channels as well. This is known as a NUMA group. Each group can access memory associated with the other groups as well. Memory associated with other groups is known as foreign memory (or remote memory). The ratio of the cost of accessing foreign memory to the cost of accessing local memory is called the NUMA ratio. The higher the NUMA ratio, the slower the access to the memory.

The main benefit of NUMA is scalability. The NUMA architecture was designed to surpass the scalability limits of SMP architectures. With SMP, all memory access is located on the same memory bus. This works fine for a relatively low number of processors, but not when you have several (more than 16) cores competing for access to the shared memory bus. NUMA alleviates these bottlenecks by limiting the number of CPUs on any one memory bus and connecting the various nodes with a high-speed bus.

SQL Server 2005 has been designed to fully support NUMA and CC-NUMA architectures without special configuration. Generally speaking, this is true for systems with 16 cores or fewer. When dealing with systems that have more than 16 cores, special care must be taken to configure SQL Server 2005 to most efficiently utilize the NUMA architecture.

Implications of Hardware on Software Design

Because hardware vendors are continuously pushing the limits of what they can achieve with their architecture, more and more limitations are being uncovered that must be overcome by either new hardware-management techniques or software workarounds. Current hardware trends such as the move towards NUMA architectures have significantly affected software designs. To get adequate performance and scalability, software developers have to explicitly take advantage of hardware features, which is not an easy task within the Windows operating system because only kernel-mode applications have direct access to the hardware. To take advantage of the hardware while at the same time keeping SQL Server a user-mode application, Microsoft decided to replace the UMS with a new component called the SQL Operating System (SQLOS).




MCITP Self-Paced Training Kit Exam 70-442  .Designing and Optimizing Data Access by Using Microsoft SQL Server 2005
MCITP Self-Paced Training Kit Exam 70-442 .Designing and Optimizing Data Access by Using Microsoft SQL Server 2005
ISBN: 073562383X
EAN: N/A
Year: 2007
Pages: 162

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net