Looking into SQL Server page types November 18, 2013Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags: SQL Server page types
I have been delivering a certified course in MS SQL Server 2012 recently and I was asked a very interesting question about SQL Server pages and what types of pages exist.
I had to explain to them about SQL Server architecture first. Each data file is divided internally into pages.Each page has a size of 8 kbytes.
In each of these pages (data,metadata,indexes) are stored. 8 continuous pages of 8kbytes make up one extent which is 64kbytes.
If you make the calculations,16 extents make up 1 mbyte.
Extents are divided into 2 categories,
- Uniform – 8 pages belong to the same object e.g table
- Mixed – 8 pages do not belong to the same object
The first 96 bytes of the page are also reserved for the page header.
Page header includes information like
- Page number
- Page type
- Free space per page
The greatest size in bytes I can have in a page is 8060 bytes
So the maximum record length I can have in a table is 8060 bytes
If I have a table that its record length is 100 bytes, that means I can have 80 records per page.
If I have 200 records I need 3 pages. Each record belongs to only one page. You cannot have half record in one page and another half in another.
Ι was asked to provide an example where we will see the various SQL Server page types and what happens when the row size is larger than 8060 bytes.
I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well.
I am connecting to my local instance through Windows Authentication and in a new query window I type (you can copy paste)
USE tempdb GO CREATE TABLE largetable ( col1 VARCHAR(2000), col2 VARCHAR(8000), col3 VARCHAR(max) ); INSERT INTO largetable VALUES ( REPLICATE('SQL Server 2012 will provide Mission Critical Confidence with greater uptime blazing-fast performance and enhanced security features for mission critical workloads',12), REPLICATE('SQL Server 2012', 533), REPLICATE('SQL Server indexes', 1000)) SELECT * FROM largetable -- Shows types of pages available inside SQL Server SELECT OBJECT_NAME (object_id) AS tablename, rows, type_desc AS page_types_SQL FROM sys.partitions part INNER JOIN sys.allocation_units alloc ON part.partition_id = alloc.container_id WHERE object_id = object_id('largetable');
Let me explain what I am doing in this bit of code
- I make the tempdb the current database
- I create a new table called largetable in the tempdb database. As you can see the record length is much larger than 8060 bytes.
- Insert some values into the table. I insert thousands of rows using the REPLICATE function.
- Finally I execute a SELECT JOIN statement from the sys.partitions and sys.allocation_units table.
The results are:
tablename rows page_types_SQL
largetable 1 IN_ROW_DATA
largetable 1 LOB_DATA
largetable 1 ROW_OVERFLOW_DATA
As you can see I have one row of data.Each of these types of pages is called an Allocation Unit.
IN_ROW_DATA refers to the situation where we have the whole row size staying within the 8,060-byte limit.
LOB_DATA refers to LOB data type columns, in this case the NVARCHAR(MAX) column and its values.
ROW_OVERFLOW_DATA refers to the data that could not be stored to single page for a single row and that is why it is using an overflow page.
Hope it helps!!!