Geek City: Where are LOBs stored?

re: Geek City: Where are LOBs stored?

Wes Clark — Mon, 27 Jun 2011 18:14:34 GMT

What you have proved is the actual behavior is how I instinctually understood what the documentation was saying, but as you have pointed out, default has a specific meaning, and the documentation is wrong. Have you filed a bug on that?

Did you try specifying "default" to see where the LOB data went then?

re: Geek City: Where are LOBs stored?

Kalen Delaney — Mon, 27 Jun 2011 18:32:29 GMT

Hi Wes...

Are you saying you understood "default" to mean "the FG where the rest of the table was going?

I showed you the code I ran. I would guess that explicitly saying "default" would refer to the default fg, in this case PRIMARY.

You're free to take my good and modify one of the CREATE TABLE commands to specify "default" for the TEXTIMAGE_ON filegroup. Let us know what you get!

Thanks

Kalen

re: Geek City: Where are LOBs stored?

Stuart Cowen — Tue, 28 Jun 2011 19:25:55 GMT

Great article and example code. I actually ran this step-by-step in my 180-day eval SS2K8R2 instance and realized the same results. This post is way over my current skill-level, but I was able to track with you and it seemed to make sense. I appreciate it :)

re: Geek City: Where are LOBs stored?

Kalen Delaney — Tue, 28 Jun 2011 21:52:48 GMT

Thanks for your feedback, Stuart! This is a great way to start getting a feel for what is waiting for you as you start getting and deeper and deeper in!

~Kalen

re: Geek City: Where are LOBs stored?

Greg Linwood — Thu, 30 Jun 2011 00:05:05 GMT

nice article Kalen, thx. I was having a discussion about this with a customer just a few days ago so I can now refer them here for further reading :)

re: Geek City: Where are LOBs stored?

Kalen Delaney — Thu, 30 Jun 2011 00:15:06 GMT

Hi Greg!

I'm glad it was useful.

:-)

re: Geek City: Where are LOBs stored?

Dale — Tue, 05 Jul 2011 14:12:16 GMT

I added the following code to the above:

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'bigrows4')

DROP TABLE bigrows4;

CREATE TABLE bigrows4

(a char(1000),

b varchar(8000),

c text )

ON FG1 TEXTIMAGE_ON 'default';

INSERT INTO bigrows4

SELECT REPLICATE('a', 1000), REPLICATE('b', 1000),

REPLICATE('c', 50);

SELECT OBJECT_NAME(object_id) as object_name, FILEGROUP_NAME(data_space_id) as FG_name, type_desc

FROM sys.partitions p

JOIN sys.allocation_units a

on p.partition_id = a.container_id

WHERE OBJECT_NAME(object_id) like 'bigrows%';

--------------------------

The results I got back were:

object_name FG_name type_desc

bigrows PRIMARY IN_ROW_DATA

bigrows PRIMARY LOB_DATA

bigrows PRIMARY ROW_OVERFLOW_DATA

bigrows2 FG1 IN_ROW_DATA

bigrows2 FG1 LOB_DATA

bigrows2 FG1 ROW_OVERFLOW_DATA

bigrows3 FG1 IN_ROW_DATA

bigrows3 FG2 LOB_DATA

bigrows3 FG1 ROW_OVERFLOW_DATA

bigrows4 FG1 IN_ROW_DATA

bigrows4 PRIMARY LOB_DATA

bigrows4 FG1 ROW_OVERFLOW_DATA

--------------------------------

So it looks like if you specify TEXTIMAGE_ON 'default', it goes to the PRIMARY filegroup.

re: Geek City: Where are LOBs stored?

Kalen Delaney — Tue, 05 Jul 2011 19:44:21 GMT

Thanks, Dale. In this script it went to the PRIMARY filegroup because the PRIMARY FG is the default FG, but remember that you can change which FG is default. So 'default' actually means a specific FG, whichever one was declared default, and it does not mean 'the FG where the rest of the data is'.

~Kalen

re: Geek City: Where are LOBs stored?

Matic Zeljko — Wed, 13 Jul 2011 00:19:38 GMT

If you create table without LOB and after some time add LOB column you should drop and recreate table to be able to assigne textimage_on option.

It's more complicated if you use table in merge replication and do smoething of following:

- on creation of table you not specify textimage_on and add after some time add it to replication

- in time of making table as part of replication you not have LOB (If you don't have LOB textimage_on can't be specified (or is ignored and changed in data filegroup)) but after some time you add LOB to table through replication.

In both case you cant't change or add textimage_on option.

re: Geek City: Where are LOBs stored?

Kalen Delaney — Wed, 13 Jul 2011 01:20:34 GMT

Hi Matic

Thanks for the additional data points!

~Kalen

re: Geek City: Where are LOBs stored?

Chirag Shah — Mon, 09 Jan 2012 21:03:39 GMT

I had the same question when creating a table (found your blog post)

It appears to me that SQL 2000 BOL documentation is much cleaner.

from http://msdn.microsoft.com/en-us/library/aa258255(v=sql.80).aspx

If TEXTIMAGE_ON is not specified, the text, ntext, and image columns are stored in the same filegroup as the table

re: Geek City: Where are LOBs stored?

Kalen Delaney — Tue, 10 Jan 2012 01:06:42 GMT

Hi Chirag

They had to rewrite the docs for SQL 2005, to add info about varchar(MAX), and partitions, so they seemed to have made it more complicated that it needed to be.

Thanks!

Kalen

re: Geek City: Where are LOBs stored?

Yuval — Fri, 31 Aug 2012 15:19:02 GMT

I want put VARBINARY(MAX) on a filegroup. when i run

SELECT [type],type_desc,FILEGROUP_NAME( data_space_id) FGname,total_pages,used_pages,data_pages

FROM sys.allocation_units A

INNER JOIN sys.partitions p

ON P.partition_id = A.container_id

WHERE P.object_id = OBJECT_ID(N'DMS_Message')

I got:

type type_desc FGname total_pages used_pages data_pages

1 IN_ROW_DATA PRIMARY 7 7 5

2 LOB_DATA FG_Big_Binary 0 0 0

3 ROW_OVERFLOW_DATA PRIMARY 0 0 0

1 IN_ROW_DATA PRIMARY 2 2 1

Are pages of LOB_DATA empty? Why?

Thanks

re: Geek City: Where are LOBs stored?

Kalen Delaney — Fri, 31 Aug 2012 16:50:28 GMT

Hi Yuval

What data did you put in the table? If varbinary(max) is less than 8000 bytes, it can go in the in_row_data allocation unit with the rest of the table data.

~Kalen

re: Geek City: Where are LOBs stored?

Yuval — Sat, 01 Sep 2012 17:50:22 GMT

Thanks Kalen,

My current state is according your suggestion and I experience performance problem, when multi threads insert to the table, there are latch waits.

I want to move the varbinary column to dedicated filegroup and gain both: in the data-row page, the writes will be faster and the big data will be written to a separate page for each record (will it?) without blocking other threads.

When implementing, as I wrote before, it seems the LOB_DATA pages are not used. Is it true? Why?

Thanks again,

Yuval

re: Geek City: Where are LOBs stored?

Yuval — Sat, 01 Sep 2012 19:59:00 GMT

Update:

When I used IMAGE instead of VARBINARY(MAX) I got:

rows type type_desc FGname total_pages used_pages data_pages

10 1 IN_ROW_DATA PRIMARY 2 2 1

10 2 LOB_DATA FG_Big_Binary 17 11 0

So, how can I force VARBINARY(MAX) to be applied on the LOB filegroup?

or, should i use IMAGE instead of VARBINARY(MAX)?

Yuval

re: Geek City: Where are LOBs stored?

Kalen Delaney — Sun, 02 Sep 2012 01:36:16 GMT

Yuval

You still haven't answered my question and told me what data you are loading in, and how big it is. As I said, if it is less than 8K, varbinary(max) will store it in the row, not in a LOB allocation unit. Since you say that if you store it as IMAGE instead, and that uses the LOB allocation_unit, I would guess that it is less than 8K, because IMAGE is always stored outside the row by default.

Since IMAGE is going away in the next version of SQL Server, I suggest that you don't use it. If you want your varbinary(MAX) to be always stored outside the row, so you can place it on its own filegroup, take a look at sp_tableoption. You can run a command like this, but please check the docs for full details:

EXEC sp_tableoption 'your_table_name', 'large value types out of row', 1

I hope this helps

~Kalen

re: Geek City: Where are LOBs stored?

Yuval — Sun, 02 Sep 2012 21:07:18 GMT

Thanks a lot,

It worked, data was allocated at LOB_DATA filegroup pages.

Still, there were lass pages than rows (yes, most of LOB's are less than 8KB).

What is the order rows are written to LOB_DATA pages?

Will a thread interfere inserting's of others? (Latch wait)

yuval

re: Geek City: Where are LOBs stored?

Yuval — Mon, 15 Oct 2012 07:52:43 GMT

Well,

I had the opportunity to test the solution of moving big-data-column to dedicated file-group (using TEXTIMAGE_ON clause).

The solution is working, there were no latch waits and total process duration was reduced by 50%.

Thanks again,

Yuval