Music Show miscellaneous-split pages 2

In the previous music show, the page split was tested based on SQL Server 2008. in SQL Server 2012 and SQL Server 2014, the page split was optimized, this prevents multiple page splits caused by one insert operation.

Let's test it in SQL Server 2014:

-- ===================================================== ==-- Use the testdb database to test use testdbgodrop table tb01go -- ====================== ============ -- create a test table tb01create table tb01 (ID int primary key, c1 nvarchar (max )) go -- ============================================== = -- insert 420 data records, insert into tb01 (ID, C1) Select T. RID, N 'c' from (select row_number () over (order by object_id) as RID from sys. all_columns) as twhere T. rid <422and T. rid <> 418 -- ========================================== = -- = insert into tb01 (ID, c1) Select 418, replicate (n'1', 4000) -- ======================================================= -- View data page DBCC Ind ('testdb ', 'tb01', 1)

-- =======================================================-- View non-leaf DBCC page ('testdb ',, 3)

We can see that this split only creates two new pages. When the records with ID = 418 are inserted, we find that the new records cannot be stored on page 8012, so we split page 8012, leave the data before 418 on page 8012, put the data after 418 on the new page 8105, and then apply for another page to store the id = 418 record to be inserted.

 

In SQL Server 2012 and SQL Server 2014, when a page is split for the first time and new data rows cannot be inserted, a new page is created to store the record, to avoid multiple page splits. (I do not know which algorithm is used for the first page split)

 

-- ===================================================== =====

 

Music Show miscellaneous-split pages 2