Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
I would think that the split has to happen first, because the whole point of the split is that the update cannot be written in-place until some space is freed up. –  Aaron Bertrand Jul 11 '12 at 23:26
    
I agree with @AaronBertrand. I can't imagine that the update is physically possible without the page split first. –  Thomas Stringer Jul 11 '12 at 23:47
2  
Yes the split must happen first to make room. You can verify the order of operations for yourself by issuing a CHECKPOINT to truncate the log immediately before the UPDATE and selecting from the system table function sys.fn_dblog(NULL, NULL) afterward. The splitting activity occurs first, and the column update (LOP_MODIFY_ROW) last. –  Paul White Jul 12 '12 at 5:39
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
    
I am with SQL Kiwi on this...I agree with what you say, but can you show it to be true? I myself tried to "prove it" with a trace and XEvent, but could not. –  ooutwire Jul 13 '12 at 2:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.