2.1 Introduction

A developer of application code is probably quite content to consider a SQL Server as a collection of databases containing tables, indexes, triggers, stored procedures, and views. As a database designer and a person who will be responsible for the performance of those databases, it is useful to be able to look a little deeper at the storage structures in SQL Server. A lot of the internals of SQL Server are hidden and undocumented, but we can still learn a fair amount about the way the product works. This chapter investigates the storage structures that SQL Server uses and the methods available to view them.

2.2 Databases and files

A database contains all the tables, views, indexes, triggers, stored procedures, and user data that make up an application. A SQL Server will typically host many databases. Usually individual databases are backed up, restored, and integrity checked, so a database can also be thought of as a unit of administration. Because a database is the container for our objects, we will need to spend some time here looking at how databases are structured and managed. We will then drill down into the database files and investigate database pages and other structures.

A database resides in one or more operating system files, which may reside on FAT, FAT32, or NTFS partitions depending on the operating system. These operating system files are known in SQL Server terminology as database files. These database files may be used to hold user and system...