Page Split Timing

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.

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

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.

StackExchange Database Administrators Q#20752, answer score: 3