Data Pages – the Foundation of SQL Server

Pages are the foundation of SQL Server – everything in SQL Server is about pages. When we want to improve the performance of our queries, we try to lower the page reads that SQL Server needs for a specific query. When we talk about indexing in the 2nd month, we find that even index structures are composed of pages. When you don’t know what a page is, you can’t tune and troubleshoot your SQL Server.

Data Page Structure

A page in SQL Server is always 8kb in size, and there are different types of pages – data pages, index pages, system pages, etc. Today we take a more detailed look at data pages, where SQL Server stores our table data. A data page always consists of 3 parts:

  • Page Header
  • Payload
  • Row Offset Array

The page header is always 96 bytes long in SQL Server (independent of the type of the page), and stores general information like the Page ID, Object ID, etc. The most interesting part of the data page is the payload area, because our records are stored in that area. SQL Server gives you from the 8192 bytes (8kb), 8096 bytes for the payload section. Therefore it’s an easy task to calculate how many rows for a given table fit on a page – just divide 8096 by the record size (incl. the internal overhead of at least 7 bytes). If you round down the result, you have the number of records you can store on a data page.

The goal is always to have as many records as possible on a page, because SQL Server has to read and write complete pages. SQL Server isn’t able to read a part of a page from your storage, or write part of a page out to your storage. I/O operations are always done (at least) on a page level.

And finally at the end of the page you have the so-called Row Offset Array. The Row-Offset Array just stores with 2 bytes for every record the offset on the page at which the record is located. The first record always begins at the decimal offset of 96 – directly after the page header. The following picture gives you an overview about the described structure of the data page.

Data Page Structure

Data Page Internals

Let’s have a look at a simple table definition, like the following one:

CREATE TABLE Customers
(
   FirstName CHAR(50) NOT NULL,
   LastName CHAR(50) NOT NULL,
   Address CHAR(100) NOT NULL,
   ZipCode CHAR(5) NOT NULL,
   Rating INT NOT NULL,
   ModifiedDate DATETIME NOT NULL,
)
GO

With such a table definition it’s now very easy to calculate how many records we can store on one page. The size of a record is here 224 bytes long (50 + 50 + 100 + 5 + 4 + 8 + 7). When you now divide 8096 by 224, you get 36.14, which means you are able to store 36 records of that table on one data page. The other remaining space of the table – in our case 32 bytes (8096 – 224 * 36) are just waisted, because a data page always belongs to a specific database object, and can’t be shared amoung other objects. In the worst case, when your table definition has a record size of 4031 bytes, you are wasting 4029 bytes on every page. Things will change here when you introduce variable length data types, like VARCHAR, because SQL Server is then able to store these columns on different pages.

If you want to know how much space on your pages is wasted by your table design, you can query the buffer pool through the Dynamic Management View sys.dm_os_buffer_descriptors. Every record from this DMV represents one page that you are currently storing in the buffer pool, so please be aware of this, when you are querying this DMV on machines with a larger amount of RAM. The column free_space_in_bytes tells you how much space is currently free on the specific page. The following query returns you, how much space is wasted by every database on your SQL Server instance.

SELECT
   DB_NAME(database_id), 
   SUM(free_space_in_bytes) / 1024 AS 'Free_KB'
FROM sys.dm_os_buffer_descriptors
WHERE database_id <> 32767
GROUP BY database_id
ORDER BY SUM(free_space_in_bytes) DESC
GO

This is always a query that I’m running on a system (e.g. during a SQL Server Health Check), to find out which database might have a bad table design.

Summary

I hope that I have given you today a better understanding about data pages in SQL Server, and why they are important for performance tuning. As you also have seen, you can directly influence how many data pages a given table needs by concentrating on the table design. If you want further detailed information about data pages I’m also recommend to watch my SQL Server Quickie about that topic.

Thanks for your time,

-Klaus

5 thoughts on “Data Pages – the Foundation of SQL Server”

  1. Thank you for this post. It always good to be reminded of data page structuring in SQL Server.
    One question, just to be sure we all get it right: you mentioned the 3 parts of a 8192 bytes data page to be the page header (96 bytes), the payload (8096 bytes). This totals to 8192 bytes. But there is also the Row Offset Array. I gather from the post that that is part of the Payload bytes, but it is not quite clear how many bytes I need to ‘reserve’ for that. Is that the ‘at least 7 bytes overhead’ you mentioned?

    1. Klaus Aschenbrenner

      Hello Rick,

      For every record you need 2 bytes in the row offset array.
      The at least 7 bytes are within the payload area for each individual row.

      Thanks,

      -Klaus

      1. Jeff Roughgarden

        Hi Klaus,

        I’m afraid I did not follow your answer. Could you post a detailed example including the header, the maximum number of records, the mysterious seven bytes, the row offset array, and the wasted space? Thanks in advance

  2. There used to be more. What happened to the:
    – null bit map
    – the offset table for fixed columns
    – one additional byte for each nullable column
    – one additional byte for each varchar column
    granted that last time I really dissected the data page was when MS still had that information in BOL.

    If you have a chance I’d love to hear from you and start a dialog.

    1. Klaus Aschenbrenner

      Hello Rudy,

      Thanks for your comment.
      When you read carefully through the blog posting, you can see that I have mentioned in the formula the overhead of at least 7 bytes.

      Thanks,

      -Klaus

Leave a Reply to Klaus Aschenbrenner Cancel Reply

Your email address will not be published. Required fields are marked *