Problem
Have you considered how many data pages make up a table? If you’re like me, you never give it a second thought. A page is around 8 KB, and SQL Server stores records on it. What more is there to know?
A few months back, someone asked me how many rows SQL stores on a page and why the math didn’t add up. They read that an integer is 4 bytes, and a page can hold around 8 KB of data. You should be able to fit around 2,000 records on a page, right? Nothing can ever be that simple.
Solution
At a high level, let’s explore what makes up a data page in SQL Server. Let’s also investigate the overhead SQL Server adds that limits the number of rows per page. Along the way, I’ll introduce a few tools to look at the number of pages in SQL Server. By the end of this tip, you’ll understand why the number of records you can save on a page is lower than you may think.
SQL Server Pages
What’s a page in SQL Server? Microsoft describes a page as a fundamental unit of data storage. SQL Server reads and writes data to pages. There are several types of pages, including Index, Boot, IAM, etc. This article focuses on Data pages. Also, you commonly hear a page size being 8 KB, more precisely 8192 bytes.
Structure of a Page
I love reading books on my Kindle. However, I've started reading physical books again. There's something about a physical book a Kindle can't replicate. Depending on the book, a page usually has a header with the chapter or book title. At the bottom, there could be footnotes and a reference to the page. A Data page in SQL Server has similar elements. A Data page consists of an area called the header consuming 96 bytes of space. The header contains information like the amount of free space, the next page, and a dozen other elements. Directly under the header is the body, which can hold 8096 bytes of data. I've included a visual below as a reference.
Also, there’s an area called the slot array or row offset array. I’ve heard both terms used interchangeably. The row offset area keeps track of where the records are stored on the page. An interesting fact: even if there is an index, there’s no guarantee that rows are in physical order on the page. However, the row offset points back to the physical location of the record on the page. This mechanism ensures that the engine knows where to find the records.
Going back to the original question, given that you can store 8096 bytes of data on a page, why can’t we fit 2,000 4-byte integers? Let’s keep going to figure it out.
Building a Dataset
To examine this further, let’s create a small table with a single integer column and 2,000 rows:
USE [master];
GO
IF DATABASEPROPERTYEX('PleaseDeleteMe', 'Version') IS NOT NULL
BEGIN
ALTER DATABASE PleaseDeleteMe SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE PleaseDeleteMe;
END;
GO
CREATE DATABASE PleaseDeleteMe;
GO
ALTER DATABASE PleaseDeleteMe SET RECOVERY SIMPLE;
GO
USE PleaseDeleteMe;
GO
CREATE TABLE dbo.PageCountDemo
(
Column1 INT NOT NULL,
);
GO
INSERT INTO dbo.PageCountDemo
(
Column1
)
SELECT TOP (2000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Column1
FROM sys.all_columns AS n1
CROSS JOIN sys.all_columns AS n2;
GO
Checking Page Count
Let’s check to see how many pages make up this table. Use the DMV, sys.dm_db_partition_stats, to get an accurate count of rows and pages:
SELECT OBJECT_NAME(s.object_id) AS [TableName],
s.row_count AS [RowCount],
s.used_page_count AS [UsedPages],
s.reserved_page_count AS [ReservedPages]
FROM sys.dm_db_partition_stats s
JOIN sys.tables t
ON s.object_id = t.object_id
WHERE OBJECT_NAME(s.object_id) = 'PageCountDemo';
From the screenshot above, it appears the table uses five pages. Why so many? You might expect two at the most. Let’s dig a little deeper.
Let’s run the dynamic management function, sys.dm_db_database_page_allocations. This undocumented system function returns information about how SQL stores data on pages. More specifically, it provides the actual page Id’s needed further down. Aaron Bertrand wrote a great article on being cautious when using sys.dm_db_database_page_allocations in larger systems.
SELECT OBJECT_NAME(pa.object_id) AS [TableName],
pa.page_free_space_percent,
pa.page_type_desc,
pa.allocated_page_page_id,
pa.extent_file_id
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.PageCountDemo'), NULL, NULL, 'DETAILED') AS pa;
From the screenshot above, you can see that one of the pages is type Index Allocation Map (IAM), meaning our actual data isn’t stored on it. SQL uses the IAM page to store information about pages belonging to a single allocation unit. We have three pages at capacity and one showing 50% full. The remaining pages indicate reserved but are 100% free to use.
Why are so many pages in use? The simple answer is that SQL Server adds overhead to each record on a page. This is like when you purchase a vehicle: the sticker price seems reasonable. However, sitting down with the salesperson, they bring up multiple add-ons (tire upgrades, extended warranty, etc.) In the end, the price significantly increased.
Examining a Data Page
To review the contents of a page, we need to run the code below. I’m using the first data page from the prior output. Your page numbers might be different. We also need to enable trace flag 3604.
DBCC TRACEON(3604);
GO
DBCC PAGE(PleaseDeleteMe, 1, 304, 3);
After executing the statement above, navigate to the Messages tab. One thing to call out in the page header is the m_slotCnt. This element indicates the number of records on a page. You can see there are 622. The element m_freeCnt shows there are 10 bytes free on the page.
Reviewing further, you can see the first data record with a slot offset of zero. Notice that the record size is 11 bytes.
Due to the additional tags and statuses that SQL adds, the 4-byte row now consumes 11 bytes. Additionally, records cannot span pages, and since we only have 10 bytes free, another record will not fit. This leaves 10 free bytes on the page.
Let's figure out why only 622 integers can be stored on a page. If we multiply 622 x 11, the answer is 6842 bytes. Remember the slot array mentioned above consumes 2 bytes per record, an additional 1244 bytes (2 x 622). If we add 1244 + 6842, we get 8086, plus from the header, there are 10 bytes free on the page. Finally, that gives us the 8096 bytes; mission accomplished. The calculation below may help to clarify:
As you can see, the overhead adds up quickly. For example, if you use variable-length columns, they add even more overhead.
I didn't go into all the details of what makes up the extra 7 bytes of our integer record. Several other articles do a fantastic job, including Paul Randal's post on the topic and a detailed post by Dmitri Korotkevitch.
Wrapping Up
My intent in writing this article was to answer a straightforward question: Why does SQL Server limit the records that can be saved on a page? The simple answer is that SQL adds needed overhead to each record on a page. Knowing this won’t drastically improve query performance. But, it does highlight the need to be realistic when assigning data types, i.e., do you need that BIGINT? Maybe, maybe not? However, if someone asks you a similar question, you can point them to this article or answer it yourself.
Next Steps
- Are you looking for a comprehensive overview of various page types in SQL Server? Please check out this post about pages and extents on Microsoft Learn.
- If you're on SQL Server 2019 and interested in finding information about pages, please review this article about sys.dm_db_page_info by Aaron Bertrand.
