Page Split Timing

Question

So, imagine I have a page that I intend to cause to split.

 
USE master ;
GO

IF DATABASEPROPERTYEX (N'Pages', N'Version') > 0
BEGIN
    ALTER DATABASE Pages SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE Pages ;
END
GO

CREATE DATABASE Pages ;
GO

IF EXISTS(SELECT object_id FROM sys.objects WHERE name = 'PageSplit')
BEGIN
    DROP TABLE PageSplit ;
END


USE Pages ;
GO

CREATE TABLE PageSplit
(
      c1 INT IDENTITY
    , c2 VARCHAR(2000) DEFAULT REPLICATE('b' , 1000)
) ;
GO

CREATE UNIQUE CLUSTERED INDEX CIX_c1
ON dbo.PageSplit
(
    [c1] ASC
) ;
GO

INSERT INTO PageSplit DEFAULT VALUES ;
GO 7

DBCC IND(Pages,PageSplit,-1) ;
GO
DBCC TRACEON(3604) ;
GO
DBCC PAGE (Pages,1,231,3) ;
GO
-- m_freeCnt = 977

At this point, I need only update a row like so and I get a split: 3 rows to one page, 4 to the other:

UPDATE PageSplit 
SET c2 = REPLICATE('b' , 2000)
WHERE c1 = 1 ;
GO

So, my question is this: when does the update happen: before or after the page split? I assume that it occurs after the split.

I have attached the output of SQL Profiler and DBCC IND. Just a side note, the updated row remained on the original page.

DBCC IND Output

SQL Profiler Trace

I applied SQL Kiwi's advice. See the results here: enter image description here

score 3 · Accepted Answer · answered Jul 12 '12 at 04:30

The UPDATE would happen after the split because from a data state perspective, SQL Server will never overwrite another currently-allocated row in the process.

Moreover, if SQL Server did overwrite a portion of another row, and that row had to be moved, it wouldn't know what data to copy to the new page. A copy of the row could be kept in a temporary buffer in memory... which... is the very definition of a data page.

And so the splitting process goes as follows:

Allocate a new page
Copy the split rows to the new page
Deallocate the split rows from the original page
Did we reach at least the target amount of free space? If yes, we're done; if no, split again.

Finally, the UPDATE occurs, which is always free to overwrite unallocated portions of the page.

Page Split Timing

1 Answers1