This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In the previous series of the articles, I shared internal structure of the Data File and Data Page of the SQL Server.
In this article, I am going to share small practical demonstration on how to find Data Page information of a particular table of the SQL Server.
First, Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE dbo.tbl_TestDataPage ( DataPageID INT PRIMARY KEY ,Name VARCHAR(30) ) GO INSERT dbo.tbl_TestDataPage VALUES (1,'Anvesh'),(2,'Neevan') ,(3,'Martin'),(4,'Jeeny') ,(5,'Maria'),(6,'Roy') GO |
Execute DBCC IND to check allocated pages of a Table:
You should put your Database_Name. -1 means display all type of pages.
|
1 |
DBCC IND('Database_Name', 'tbl_TestDataPage', -1) |
The Result:
PageFID is a file number and PagePID is a page number. You can use these numbers for further tracing.
Execute DBCC PAGE to find more information about the allocated page:
Syntax:
|
1 2 3 4 5 6 7 |
DBCC PAGE ( {'database_name' or DB_ID}, file_number, page_number [, printopt={0|1|2|3} ]) Print option: 0 - print the page header 1 - page header plus per-row hex dumps and slot array 2 - page header plus whole page hex dump 3 - detailed per-row interpretation |
|
1 2 3 4 |
DBCC TRACEON(3604) -- This trace flag enable result in table format. GO DBCC PAGE('database_name',1,274,3) GO |
The Result:
