Sql-server – Page Split Timing

page-splitssql-server-2012

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

Best Answer

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.

Related Question