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.

-- Example 1: With Clustered Index

CREATE TABLE tbl5 
(
    i VARCHAR(900) NOT NULL 
) ;
GO
CREATE CLUSTERED INDEX CIX_tbl5
    ON tbl5 (i ASC) ;
GO

INSERT      INTO tbl5
            ( i )
VALUES
            ( REPLICATE('a' , 900) )
    ,       ( REPLICATE('b' , 900) ) 
    ,       ( REPLICATE('c' , 900) )
    ,       ( REPLICATE('d' , 900) ) 
    ,       ( REPLICATE('e' , 900) ) 
    ,       ( REPLICATE('f' , 900) ) 
    ,       ( REPLICATE('g' , 900) ) 
    ,       ( REPLICATE('h' , 900) )  -- 900 x 8  + (13 x 8) = 7200 + 104 = 7304
    ,       ( REPLICATE('i' , 761) ) ;-- 7304 + 761 + 13  
GO                                    -- = 8078 + 96(Page Header) + 18 (Slot)
                                      -- = 8192
                                      -- m_freeCnt = 0

DBCC IND(tempdb,tbl5,-1) ; -- everything is IN_ROW_DATA
GO
DBCC TRACEON(3604) ;
GO
DBCC PAGE(tempdb,1,177,3) ;
GO

-- Example 2: With heap. When column 'i' is 392 m_freeCnt = 387. When 'i' = 393, I get a new page. Why can I not add more bytes to 'i'?

CREATE TABLE tbl6
(
    i VARCHAR(900) NOT NULL 
) ;
GO
INSERT      INTO tbl6
            ( i )
VALUES
            ( REPLICATE('a' , 900) ) 
    ,       ( REPLICATE('b' , 900) ) 
    ,       ( REPLICATE('c' , 900) ) 
    ,       ( REPLICATE('d' , 900) ) 
    ,       ( REPLICATE('e' , 900) ) 
    ,       ( REPLICATE('f' , 900) ) 
    ,       ( REPLICATE('g' , 900) ) 
    ,       ( REPLICATE('h' , 900) ) 
    ,       ( REPLICATE('i' , 393) ) ;
GO

DBCC IND(tempdb,tbl6,-1); 
GO
DBCC TRACEON(3604);
GO
DBCC PAGE(tempdb,1,181,3);
GO
share|improve this question
    
When the final row is of length 392 you can see loads of free space on the page in SQL Internals Viewer Screenshot. Maybe something to do with PFS units? –  Martin Smith Mar 14 '12 at 20:54
    
@Martin if only I could get SSQL Internals Viewer to work on my 08R2 system! =-) I'll dig in further. –  ooutwire Mar 14 '12 at 20:58
1  
Do you mind updating your scripts to use row constructors? It would make them much, much, shorter and easier to read. –  Nick Chammas Mar 14 '12 at 21:06
1  
@nick Done, sorry about the slop –  ooutwire Mar 14 '12 at 21:19
    
@ooutwire - Sweet! The post looks much better now. Thank you. –  Nick Chammas Mar 14 '12 at 21:32
add comment

1 Answer

up vote 6 down vote accepted

'page split' and 'heap' should not be used in the same sentence. Do you mean why wasn't the row of size 393+change fit in the free space of the first page of the heap? That is because the PFS free space info has not high enough resolution, see Managing Extent Allocations and Free Space:

The PFS has one byte for each page, recording whether the page is allocated, and if so, whether it is empty, 1 to 50 percent full, 51 to 80 percent full, 81 to 95 percent full, or 96 to 100 percent full.

Would fit in page, indeed, but the PFS only says that the page is '95_PCT_FULL' and the record inserted is fairly big and close to the free space as-per-PFS (95% of 8060 is 403), so the INSERT chooses to allocate a new page.

With a B-Tree the record position is deterministic due to key order, so the engine must load the page and check the free space, and the record will fit.

share|improve this answer
    
Thank you. I did not intend to write "page split"; I was asking, as you correctly deduced, why won't the row fit onto the first page. –  ooutwire Mar 14 '12 at 21:04
2  
Note how if you reduce the size to 392 chars the resulted record (which fits and does not cause 'split') has exactly 403 bytes length, ie. the 95% mark. 393 crosses the threshold. –  Remus Rusanu Mar 14 '12 at 21:09
    
It is odd though that I cannot always repeat my results. Sometimes I can actually get the page full. I'll continue to experiment. –  ooutwire Mar 14 '12 at 21:57
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.