Chapter 3: RDBMS Architecture

Overview

This chapter investigates the core of the SQL Server 2005-the Database Management System (DBMS). You will be learning about its storage architecture and data input/ output (I/O). I might add that the architecture and processes discussed in this chapter relate mostly to the SQL Server storage engine, while the architecture and processes discussed in Chapter 2 relate mostly to the relational engine.

For the most part, all SQL-driven database management systems operate on the same principles, just as all cars run on tires, gas, and a combustion engine. Some systems, however, work better than others do, and each DBMS is unique in how it makes use of memory, central processing unit (CPU), hard disks, and its query processing algorithms.

In this chapter, it will seem that I am straying into general relational database theory and architecture and moving away from SQL Server 2005. But the opposite is the case, If you are new to databases, C/S RDBMS systems, SQL, and SQL Server, you will greatly benefit from a general discussion on how a DBMS works in general and how SQL Server works in particular. You also need to investigate the claims that SQL Server 2005 leads the field in many transaction processing areas and is currently the most scalable product available; this chapter helps you meet that objective.

I believe you should first understand how a DBMS works-and then learn how SQL Server works in relation to general DBMS or transaction processing theory. It is thus a good idea to combine this chapter with a good book on the principles of relational database systems. Combining knowledge of the two will enable you to appreciate SQL Server better, understand how to make it work for you, and ultimately become a better DBA and SQL Server developer as you tackle the more complex “data” in the chapters that lie ahead.

If you are an experienced SQL Server or C/S DBMS engineer, you can skim the first part of this chapter. If you are new to SQL Server, however, I strongly recommend you understand how this DBMS works. Coupled with an understanding of the collateral components and resources, this understanding will help flatten the learning curve before you.

For the most part, you will be learning conceptually about SQL Server’s internal environment: how data gets into the DBMS; how it gets stored, retrieved, and sent back out to the clients. We will not get into SQL Server 2005’s CLR support in this chapter, but we do cover CLR specifics in Chapter 11. First let’s get familiar with some of the important pieces before getting into deeper water. I will launch the discussion with a subject that all vendors make a lot of noise about: the Transaction Processing Performance Council’s (TPC) transaction processing benchmarks.