It can often help to understand how a database actually stores data. While the way in which SQL Server stores data and the allocation units it uses are well documented this is my version for my web site. To gain a deeper understanding I would recommend you visit Paul Randal’s blog. Relevant sections to this post can be found here near the bottom. As Paul worked on the storage engine team at Microsoft, and managed the team for a period as well, his knowledge is beyond question.

Types of allocation unit in SQL Server

SQL Server has two different types of allocation unit, bytemap and bitmap. This is just a reference to how SQL Server represents the storage unit that the allocation unit pages relate to. The bitmap pages represent 64KB extents (A collection of 8 * 8KB pages) with a single bit. There are 5 bitmap pages as below: –

• Global Allocation Map (GAM)
• Shared Global Allocation Map (SGAM)
• Bulk Changed Map (BCM) – Also known as an ML page or Minimally Logged change map page
• Differential Change Map (DCM)
• Index Allocation Map (IAM) – these are special cases and not covered further in this post

There is also a bytemap page which represents individual 8KB pages with a single byte. This page type is as below

• Page Free Space (PFS) page

Bytemap pages

The PFS page tracks the below information about database pages: –

• Amount of free space on a heap page
• Presence of ghost records on index pages (Including clustered index leaf pages)
• Is this an IAM page
• Is this a mixed page
• Is this page allocated

This information is represented by an entire byte per page and each PFS page can track 8,088 database pages. This equates to just under 64MB of data pages. Therefore every 8,088 pages (64MB) in the data files of your database you will find a PFS page. You can confirm this with the below commands: –

[code language=”sql”]
/* Alter the database name to be a database where you have more than 64MB of data in the primary file group */
DBCC PAGE (DatabaseName, 1, 1, 0) WITH TABLERESULTS;
DBCC PAGE (DatabaseName, 1, 8088, 0) WITH TABLERESULTS;
GO
[/code]

On line 14 you should see a row for m_type and a value of 11 which is a PFS page.

Bitmap pages (except IAMs)

All the bitmap pages track data at the extent level. An extent is a collection of 8 * 8KB pages, so 64KB of data. Extents come in two types: –

• Mixed extents which have data or index pages from more than one entity
• Uniform extents which have pages relating to a single entity.

I use the term entity to mean, in simple terms, an index of a table. In truth it goes down to the partition and allocation unit type (in row data, row overflow data, lob data) but we’ll skip that for this explanation. Since we can track 64KB with a single bit we can track just under 4GB of data with a single bitmap page. These pages recur every 511,232 pages. This can be confirmed with the below statements if you have any data files that are greater than 4GB and I’ll put the statements together as though the data is in data file 1: –

[code language=”sql”]
/* Alter the database name to be the database where you have enough data, we’ll look at 2 GAM pages */
DBCC PAGE (DatabaseName, 1, 2, 0) WITH TABLERESULTS;
DBCC PAGE (DatabaseName, 1, 511232, 0) WITH TABLERESULTS;
GO
[/code]

On line 14 you will be able to confirm that the page type (m_type) is the same for these pages. Pages of interest are located at the below pages and then every 511,232 pages thereafter (I have included the page types so you know what to look for on line 14 of a DBCC PAGE print): –

• Page 2 – GAM – type 8
• Page 3 – SGAM – type 9
• Page 6 – DCM – type 16
• Page 7 – BCM/ML Map– type 17

This has been an overview of the types of allocation pages that SQL Server uses. There will be other future posts to go into more depth on each of these topics. However for now I will keep this short because a full post on allocation units and pages would be far too big 🙂

If you find the way that the storage engine tracks pages and objects interesting I fully recommend the posts of Paul Randal as previously mentioned or feel free to wait for future posts in this area.