Database Reference
In-Depth Information
■
I'll analyze more of the information provided by
sys.dm_db_index_physical_stats
in the “analyzing the
amount of Fragmentation” section later in this chapter.
Note
Therefore, if you attempt to expand the content of column
C3
for one of the rows as follows, it should cause
a page split:
UPDATE dbo.Test1
SET C3 = 'Add data'
WHERE C1 = 200;
Selecting the data from
sys.dm_db_index_physical_stats
results in the information in Figure
13-5
.
Figure 13-5.
i1 index after a data update
From the output in Figure
13-5
, you can see that SQL Server has added a new page to the index. On a page split,
SQL Server generally moves half the total number of rows in the original page to the new page. Therefore, the rows in
the two pages are distributed as shown in Figure
13-6
.