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
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)
)
----------------------
create nonclustered index row_cl ON PageDemo (Firstname);
GO
-------------
insert into PageDemo values('Selva','Kumar')
insert into PageDemo values('John','Paul')
-----------------------
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
DBCC Traceon(3604) ----------Enable output in Management studio Console
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:
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
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.
Understanding Pages and Extents How SQL Server stores data
Naomi N edited Revision 5. Comment: Minor edit
Naomi N edited Revision 4. Comment: Minor edit
Naomi N edited Revision 3. Comment: Minor edit
Naomi N edited Revision 1. Comment: Added See Also
Naomi N edited Original. Comment: Minor edit
Nice article. thanks.
Question: In Page header, What is record size and what is the reason record size 27 and 25? where as bytes used by both records are 15 and 12 respectively.
Following query is also related and helpful to see File, Page and number of records in a page.
SELECT PARSENAME(t1.[PageName], 2) AS PageNumber
,PARSENAME(t1.[PageName], 3) AS FileNumber
,COUNT(*) records_in_this_page
FROM PageDemo t
CROSS APPLY (
SELECT REPLACE(sys.fn_PhysLocFormatter(% % Physloc % %), ':', '.') AS PageName
) t1
GROUP BY PARSENAME(t1.[PageName], 2)
,PARSENAME(t1.[PageName], 3)