Data Storage in SQL Server

Within the data files, SQL Server stores data in 8KB blocks of contiguous disk space, known as pages. The page is the minimum unit of IO when SQL Server transfers data to and from disk. Each of these data pages has the capacity to store up to 8060 bytes of data (8192?132 for overhead), and a single row cannot span more than one page. This is an important consideration when calculating disk usage. For example, if a table has an average row size of 4050 bytes, only one row would fit per page, essentially doubling the disk space required to store the data.

In an effort to reduce internal operations and increase IO efficiency, SQL Server, when allocating space to a table or index, allocates space in extents. An extent is eight contiguous pages, or 64KB of storage. There are actually two types of extents. Every table or index is initially allocated space in a mixed extent. As the name implies, mixed extents store pages from more than one object. When an index or table is first created, it is assigned an Index Allocation Map, or IAM, which is used to track space usage for the object, and at least one data page. The IAM and data page are assigned to a mixed extent in an effort to save space, as dedicating an extent to a table with a few small rows would be wasteful. Up to eight initial pages will be assigned this way. When an object requires more than eight pages of storage, all further space will be allocated from uniform extents. Uniform extents store pages for only a single index or table. This allows SQL Server to optimize read and write operations and reduce fragmentation because the data will be stored in units of 64KB (eight pages), as opposed to individual 8KB pages scattered throughout the data file.

For more detailed information on the internal storage structures and how they are managed in SQL Server databases, see Chapter 33, "SQL Server Internals."

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features