Database Reference
In-Depth Information
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 cross join N2 as T2) -- 1,024 rows
,IDs(ID) as (select row_number() over (order by (select NULL)) from N5)
insert into dbo.PageSplitDemo(ID)
select ID * 2
from Ids
where ID <= 620
select page_count, avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.PageSplitDemo'),1,null,'DETAILED');
Following is the output from the code shown from Listing 5-1. As you can see, there is the single data page, which
is almost full.
page_count avg_page_space_used_in_percent
---------------- ---------------------------------------------
1 99.5552260934025
As a next step, let's insert a large row into the table with the code from Listing 5-2 using
SQL Server 2008
.
Listing 5-2.
Multiple page splits: Insert a large row into the table
insert into dbo.PageSplitDemo(ID,Data) values(101,replicate('a',8000));
select page_count, avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.PageSplitDemo'),1,null,'DETAILED');
Following is the output of the code from Listing 5-2. As you can see, SQL Server had to allocate seven new leaf-
level data pages to accommodate a new data row and to preserve the logical sorting order in the index.
The process worked in the following way. SQL Server kept 50 rows with
ID<=100
on the original page, trying to fit
new (
ID=101
) and remaining (
ID>=102
) rows into the newly allocated data page. They did not fit into the single page,
and SQL Server continued to allocate pages, splitting rows by half until they finally fit.
It is also worth mentioning that SQL Server had to create the root level in the index.
page_count avg_page_space_used_in_percent
---------------- ---------------------------------------------
8 24.8038670620213
1 1.26019273535953
Fortunately, the page split algorithm has been dramatically improved in SQL Server 2012. Following is the output
of the code from Listing 5-2 if you run it using
SQL Server 2012
. When SQL Server 2012 detected that the data does
not fit into the newly allocated page, it allocated another (third) page, put new (
ID=101
) row to one of the pages and
all of the remaining rows (
ID >= 102
) to another one. Therefore, with SQL Server 2012-2014, page split introduces at
most two new page allocations.
page_count avg_page_space_used_in_percent
---------------- ---------------------------------------------
3 99.5552260934025
1 0.457128737336299
