LOB pointers - am I stupid?

Question

LOB pointers - am I stupid?

sqlg67

SSC Veteran

Points: 258
More actions
July 18, 2011 at 9:34 am

#244235

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.

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

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

Paul Randal One Orange Chip Points: 29544 More actions · Answer 1

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

Mark S. Rasmussen Right there with Babe Points: 724 More actions · Answer 2

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

sqlg67 SSC Veteran Points: 258 More actions · Answer 3

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!

Mark S. Rasmussen Right there with Babe Points: 724 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

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.

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

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.

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