Basics of SQL Server Pages
This article will be helpful to the SQL beginners like me, who may be confused with SQL pages, how SQL Server page is storing the data and how pages are aligned in Master data file. I am writing my view here and if I am wrong in this concept, kindly please correct me. Page split happens only conceptually not physically.
Pages
Page is the fundamental storage unit of SQL, the page is 8KB size with 96 byte page header store information about page, such as Page type, page number, amount of free space on the page and allocation unit ID.
Extents
Extents are eight contiguous pages or 64KB.
Type of Extents
- Uniform Extents-Owned by a single object, all eight pages are used by single user objects.
- Mixed Extents –Owned by multiple objects, each of the pages is shared by different objects may be some times eight objects.
Where my table is stored?
Many people are confused with where my table is stored in which page. How to find where is my table data stored?
Here we go
use Demo
go
create table PageDemo
(
id int identity primary key,
FirstName varchar(10) ,
LastName Varchar(10)
)
go
create nonclustered index row_cl ON PageDemo (Firstname);
GO
insert into PageDemo values('Selva','Kumar')
insert into PageDemo values('John','Paul')
go
Here we created small demo table in our database.
Before we will go inside page,we want to enable traceon flag to get output in console (Management studio console), otherwise engine will write the output in Error log file.
Run the Traceon Command
use master
go
DBCC Traceon(3604) ----------Enable output in Management studio Console
go
Now we will go in depth about page
Command to get page number:
Here is the command to get the page number where our table is stored.
DBCC IND
(
['database name'|database id], -- the database to use
table name, -- the table name to list results
index id, -1 shows all indexes and IAMs, -2 just show IAMs
)
We choose option -1 in the above command; so it will give all pages related to the table.
We will focus on key column- page Type.
Here the page types:
- 1-Data Page
- 2-Index Page
- 3 and 4 –Text pages
- 8-GAM Page (Global Allocation map)
- 9-SGAM Page (Shared Global Allocation map)
- 10-IAM page (Index Allocation map)
- 11-PFS page (Page Free Space)
Now we will get the pages which are occupied by our table data and index.
Page ID=Page number
Page ID=154 and 156 are used by Index allocation mapping.
Now our actual data and index storage
Page ID=153 and Page type=1 is our actual data is storing**
How to get deep on actual data**
Here the generic command to get the page details
DBCC page ( {'dbname' | dbid}, filenumber, pagenumber [, printoption={0|1|2|3} ])
The print option parameter has the following meanings:
0 - print just the page header
1 - Page header plus per-row hex dumps and a dump of the page slot array (unless it’s a page that doesn't have one, like allocation bitmaps)
2 - Page header plus whole page hex dump
3 - Page header plus detailed per-row interpretation
Here our output:
Output in table format:
Where is my INDEX PAGE?
Now we will go with
Page ID=155 and Page Type=2 is our actual non clustered index data.
We create non clustered index on First name
Our non-clustered index plus Key hash value for leaf node.
Hope my article will be helpful to beginners.