LOB pointers - am I stupid?

  • This may be a stupid question but all the research I do states SLOB's (Row Overflow) have a 24-byte pointer and LOB's have a 16-byte pointer.

    I have tested and confimred the SLOB pointer. However when working with LOB's [varchar (Max) data type] I still get a 24-byte pointer even when the data >8060 bytes? I can only get the 16-byte LOB pointer whenw working with text and the other legacy data types. Example follows:

    create table LOBtest (a VARCHAR(max), col_b varchar(max))

    insert into lobtest select REPLICATE('a', 10000), REPLICATE ('b', 10000)

    once complete using DBCC IND and DBCC Page I lookup and view the IN_ROW_DATA page and once I look ati it stores a 24-byte pointer to the LOB.

    Pointer DBCC Page + printoptn 3>>

    col_b = [BLOB Inline Root] Slot 0 Column 2 Offset 0x1f4d Length 24 Length (physical) 24

    Level = 0 Unused = 0 UpdateSeq = 1

    TimeStamp = 772210688

    Link 0

    Size = 8000 RowId = (1:90:0)

    Pointer DBCC Page + printoptn 2>>

    040000 00010000 00072e00 00401f00 005a0000 00010000 00

    For the life of me I can't figure this out. Any help would be appreciated!! Thx.

  • Wherever you got that info from is incorrect - the off-row pointer size depends on the kind of inline root: SLOB, LOB, or legacy LOB: 24, 24, 16 IIRC

    Edit: Read Mark's post below for more gory details.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • That is in no way a stupid question, one that's stumped me too.

    Although [SQL Server 2008 Internals] by Kalen Delaney et. al. mentions that SQL Server will store (MAX) data types just like regular LOBs like text, ntext & image, that's bit of an oversimplification.

    One comment on your test - REPLICATE('a', 10000) will only result in a 8000 character long value. As 'a' is implicitly typed as a varchar with a max value of 8000. REPLICATE(CAST('a' AS varchar(MAX)), 10000) will give the 10.000 character long value as expected. It doesn't change the result of the test though.

    The overflow pointer uses 24 bytes by default, but it may take up more space. Each page slot referenced takes up 12 bytes so you may see pointers of size 24, 36, 48, etc. At some point SQL Server decides to store a single reference to a text tree page instead. The largest pointer I've been able to reproduce is 72 bytes, after which it starts using 24 byte pointers to text_tree pages instead.

    So when does it store a regular 16 byte LOB pointer? Only when you set the "large value types out of row" table setting to ON:

    sp_tableoption N'MyTable', 'large value types out of row', 'ON'

    At this point, no matter the size of the value it will be stored as a LOB value using a 16 byte LOB pointer.

    Mark S. Rasmussen
    Blog: improve.dk
    Twitter: @improvedk
    Internals geek & author of OrcaMDF

  • Thanks Paul. This makes sense I tried a ton of combinations but couldn't figure it out.

    Mark - Thanks, belive it or not I also referned SQL Server 2008 internals and was further puzzled with the *(MAX) storage statement you captured! There's so much info on the net all echoing that *(Max) pointers are the same as LOB pointers it's great to get clarification.

    Regarding testing - Thank you for the correction I didn't know that there was a difference. Some of my test I wrote much greater values but to your point wouldn't have changed the result.

    You also answered another question I haven't even asked yet - when SLOB pointers are greater than 24-bytes (I saw a 36-byte one). You are one step ahead of me!

    Thanks to both of you for the quick reply!

  • No prob! I took the liberty of elaborating a bit more on my blog since you're not the only one unable to find the behavior cleanly documented on the net.

    Mark S. Rasmussen
    Blog: improve.dk
    Twitter: @improvedk
    Internals geek & author of OrcaMDF

  • Mark S. Rasmussen wrote:

    No prob! I took the liberty of elaborating a bit more on my blog since you're not the only one unable to find the behavior cleanly documented on the net.

    Mark, I hope your still "listening" to this post.  Your link no longer works.  Do you have a new one for this very interesting information?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Ah... I think I found it... it looks like it's at the following link now...

    http://improve.dk/what-is-the-size-of-the-lob-pointer-for-max-types-like-varchar-varbinary-etc/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply