Database Reference
In-Depth Information
Figure 13-7.
Output from DBCC IND showing two pages
If you focus on the
PageType
, you can see that there are now two pages of
PageType = 1
, which is a data page.
There are other columns in the output that also show how the pages are linked together.
To show the resultant distribution of rows shown in the previous pages, you can add a trailing row to each page:
INSERT INTO dbo.Test1
VALUES (410, 'C4', ''),
(900, 'C4', '');
These new rows are accommodated in the existing two leaf pages without causing a page split. You can confirm
this by querying the other mechanism for looking at page information,
DBCC PAGE
. To call this, you'll need to get the
PagePID
from the output of
DBCC IND
. This will enable you to pull back a full dump of everything on a page.
DBCC TRACEON(3604);
DBCC PAGE('AdventureWorks2012',1,24256,3);
The output from this is involved to interpret, but if you scroll down to the bottom, you can see the output, as shown
in Figure
13-8
.
Figure 13-8.
Pages after adding more rows
On the right side of the screen, you can see the output from the memory dump, a value, “C4.” That was added
by the foregoing data. Both rows were added to one page in my tests. Getting into a full explanation of all possible
permutations of these two
DBCC
calls is far beyond the scope of this chapter. Know that you can determine which page
data is stored on for any given table.
