Chapter 1: Database Architecture and Storage Engine Features

Building on the same essential architecture that was established with the SQL Server 7 release, Microsoft has continued to make evolutionary improvements in the SQL Server engine. Following Moore’s Law, hardware has continued to double in capacity every 18 months, while at the same time hardware costs have continued to decline. However, while hardware has been getting cheaper, people costs have risen. In the SQL Server 2005 release, Microsoft has made enhancements that enable SQL Server to take advantage of the newest generations of high-performance hardware, providing it with the capability to scale to the utmost peak of the enterprise. At the same time, the company has added features that make SQL Server easier to manage and also provided new capabilities that enable you to get more value out of SQL Server for your organization. In this chapter, I’ll introduce you to some of the most important architectural improvements that Microsoft has made to SQL Server 2005, with the goal of helping you to see how these features can improve your productivity and the reliability of your database environment.

New Hardware Support

One of the key new features that enables SQL Server 2005 as a database engine to achieve the same performance levels as the biggest UNIX databases is its improved hardware support. Memory has always been one of the most critical factors that contribute to database performance, and the large UNIX databases that topped the non-clustered TPC-C performance rankings all attained that level of performance using a 64-bit hardware and operating system platform. When a comparable 64-bit platform became available for SQL Server in the form of the 64-bit Intel Itanium2 processor and Windows Server 2003’s native 64-bit support, SQL Server immediately jumped to the top of TPC-C benchmarks. SQL Server 2005 inherits that native 64-bit support from Windows Server 2003 along with the capability to support Non-Uniform Memory Architecture (NUMA). Building on the Windows Server 2003 platform, SQL Server 2005 provides 64-bit support for both the Itanium IA-64 architecture and the AMD x64 architecture.

Native 64-Bit Support

When running on Windows Server 2003 for 64-bit Itanium systems, SQL Server 2005 supports Intel’s Itanium and Itanium2 processors. When running on Windows Server 2003 for 64-bit Extended Systems, SQL Server 2005 supports AMD’s 64-bit Opteron and Athlon 64 processors as well as Intel’s Xeon with Intel Extended Memory 64 Technology (EMT64). SQL Server 2005 fully supports both 32-bit and 64-bit hardware platforms for all of its major services, including the SQL Server Engine, Analysis Services, SQL Agent, and Reporting Services. The real advantage in moving a database to the 64-bit platform isn’t faster processing power. Rather, the real advantage lies in the vastly increased addressable memory. Figure 1-1 shows a comparison of the maximum addressable memory for the 32-bit SQL Server and the 64-bit version of SQL Server 2005.

Figure 1-1: Comparing 32-bit and 64-bit memory addressing

The native 32-bit architecture is limited to a maximum of 4GB of addressable memory. Under Windows, this 4GB limit is divided evenly between the operating system and the applications. In other words, 2GB is reserved for the Windows operating system, leaving the remaining 2GB for applications. Using the Advanced Windowing Extensions (AWE) support found in the 32-bit version of Windows, the 32-bit version of SQL Server can address a maximum of 32GB of RAM. While this increase is substantial, there was still the overhead of paging to get to the appropriate memory pages. The native 64-bit implementation virtually eliminates the memory constraint by raising the maximum addressable memory to 32TB. Currently no production system supports anywhere near this amount of physical RAM. The maximum amount of physical RAM on which SQL Server 2005 has been tested at the time of release is 512GB. With the upcoming release of the next service pack for Windows Server 2003, the maximum supported memory is expected to jump to 1TB.

NUMA Support

Another new feature that leverages the Windows Server 2003 operating system is Non-Uniform Memory Architecture (NUMA) support. NUMA is a system architecture used by some system manufacturers such as IBM and Unisys that manages CPU and memory usage in multiprocessor systems more efficiently than the typical SMP architecture. In standard SMP systems, as the speed and number of processors increase, the competition between the processors for access to memory creates bus contention. This injects delays in the processing and hinders the capability of a system to scale. The result of this is that SMP systems don’t scale linearly as the number of processors increases. The NUMA architecture is designed to solve this problem found in SMP systems, in which the processors are able to access the data in RAM faster than the RAM and system bus can provide it. The NUMA architecture groups the CPU and memory into local pods of multiple processors. These pods are connected to each other via an external bus that transports cross-pod data traffic. This pod arrangement addresses the contention issue by limiting the number of CPUs competing for access to memory. To realize the maximum benefits from this architecture, both the operating system and the applications must be designed to minimize cross-pod data traffic and maximize the faster intra-pod memory access. If the operating system and the application are designed correctly, the NUMA architecture enables nearly linear scaling as more processors are added. Windows Server 2003 and SQL Server 2005 incorporate architecture improvements to increase the degree to which threads and the memory they use are located in the same pod.

Support for Hyper-Threading

SQL Server 2005 can also take advantage of hyper-threading, thanks in part to the new support for hyper-threading that Microsoft has added to Windows Server 2003. Hyper-threading is a CPU technology developed by Intel that creates two logical processors for each physical processor in a system. Each logical processor is capable of simultaneously executing separate threads. The goal of hyper-threading is to provide better resource consumption for multithreaded applications or multiple applications running on a single machine. While hyper-threading provides the potential of increasing the throughput on a server, the logical processors do compete for system resources such as the data in the processor’s cache. This competition for resources prevents a hyper-threaded CPU from performing as well as a comparable system that is using two physical CPUs.

SQL Server 2005 gets two main benefits from Windows Server 2003’s support for hyper-threading. First, unlike Windows 2000, Windows Server 2003 counts only physical processors for licensing purposes. Thus, a single processor using hyper-threading is licensed as a single processor rather than a dual processor, as it would be under Windows 2000. Next, Windows Server 2003 provides improved thread scheduling for hyper-threaded systems. These changes result in better performance for multithreaded applications like SQL Server.