Page Split

Page split occurs when there is not enough space in a particular data page to insert/update a specific record. There are 3 ways that generate Page splits:

• End/Sequential/Identity Insert  = good page split = when we insert at the end of the table . ex: imagine you have an identity field  which is also PK and clustered index , and all new records would have id+1
• Mid lvl table Inserts = bad page split
• Updates = bad page split = when we’re updating a record on a page that don’t have enough space to accommodate the extra space. For this needs extra space and voila , page split

For bad page split, this is bad because there is additional information that needs to be save in the transaction log.

• adding a new page
• move half of records to the new page (insert into the new page and delete from old page  = mark it as delete )
• update link pointers in the pages

 

Here is a sample of it:

We have a simple table , with id field clustered :

–create a table with an id and some string , so that 2 records will be stored in a page
drop table if exists dbo.pageSplit;
create table dbo.pageSplit
(
id int not null,
myChar char(3500) not null,
constraint pk_pageSplit_id PRIMARY KEY CLUSTERED(id)
)
go

Now ,let start inserting one record, starting with value of id = 3 , and see the log data generated.

begin transaction
insert into dbo.pageSplit(id,myChar)
values(3,REPLICATE(‘3’,3500));

select database_transaction_log_bytes_used from sys.dm_tran_database_transactions where database_id = DB_ID(‘db_workspace’)
–3796
commit transaction

 

Log data generated

 

Next step, insert value 2:

begin transaction
insert into dbo.pageSplit(id,myChar)
values(2,REPLICATE(‘2’,3500));

select database_transaction_log_bytes_used from sys.dm_tran_database_transactions where database_id = DB_ID(‘db_workspace’)
–3756
commit transaction

 

Step 3, insert a record with value 1.

begin transaction
insert into dbo.pageSplit(id,myChar)
values(1,REPLICATE(‘1’,3500));

select database_transaction_log_bytes_used from sys.dm_tran_database_transactions where database_id = DB_ID(‘db_workspace’)
–5784
commit transaction

Log data generated is 5784, almost double (70%) .wow…

You can monitor it with perform counters:

SQLServer:AccessMethods:Page Splits/Sec

Or from dmvs:

select cntr_value
from sys.dm_os_performance_counters
where counter_name = ‘Page Splits/sec’

Code here

S