Last week we laid out the foundation for how SQL Server executes queries. I have also already talked here a little bit about pages that are buffers of 8kb. Today we concentrate further on these pages and drill into more detail and what it means from a performance tuning perspective.
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 a few weeks, 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 must read and write complete pages. SQL Server can’t 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 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 can 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 with this blog posting 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 watching my SQL Server Quickie about that topic. Next week we are talking in more details about Extents in SQL Server, and why they are important to us.