THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: Where are LOBs stored?

When researching a question from one of the students in my class last week, I was reading the documentation for CREATE TABLE about storing LOB columns at http://msdn.microsoft.com/en-us/library/ms174979.aspx. For this discussion LOB columns includes text, image, ntext, xml and the MAX columns when they are over 8000 bytes and stored outside the regular data row. I knew that SQL Server gives us the capability of storing LOB columns in a separate filegroup with the TEXTIMAGE_ON clause, but I was surprised at one thing the docs mentioned.

The documentation says:

TEXTIMAGE_ON { filegroup | "default" }

Are keywords that indicate that the text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), and CLR user-defined type columns are stored on the specified filegroup.

TEXTIMAGE_ON is not allowed if there are no large value columns in the table. TEXTIMAGE_ON cannot be specified if <partition_scheme> is specified. If "default" is specified, or if TEXTIMAGE_ON is not specified at all, the large value columns are stored in the default filegroup. The storage of any large value column data specified in CREATE TABLE cannot be subsequently altered.

The way I read this highlighted sentence is that if you don’t put the LOB data on a particular filegroup, it will always go on the default filegroup. The default filegroup has a specific meaning for SQL Server. It is the filegroup where all objects are placed if you don’t use the ON clause to put them on a specific filegroup. By default, the default filegroup is the PRIMARY filegroup, but with the ALTER DATABASE / MODIFY FILEGROUP command, you can change the default filegroup.

I did not think that what this sentence was saying was true, so I decided to test it.

I first created a new database with three filegroups. The PRIMARY filegroup is the default filegroup, because I am not changing the default to be anything else. The two additional filegroups are FG1 and FG2.

USE master;

GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'TestFGs')

DROP DATABASE TestFGs;

GO

CREATE DATABASE TestFGs

ON PRIMARY

(NAME = Data ,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestFGPrimary.mdf'

, SIZE = 10 MB

, MAXSIZE = 1 GB

, FILEGROWTH = 25%),

FILEGROUP FG1

(NAME = FG1_data ,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FG1_data.mdf'

, SIZE = 10 MB

, MAXSIZE = 1 GB

, FILEGROWTH = 25%),

FILEGROUP FG2

(NAME = FG2_data ,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FG2_data.mdf'

, SIZE = 10 MB

, MAXSIZE = 1 GB

, FILEGROWTH = 25%)

LOG ON

(NAME = TestFG_log,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestFG_log.ldf'

, SIZE = 20 MB

, MAXSIZE = 2 GB

, FILEGROWTH = 25%);

GO

I then use the new database, and create three tables, each with the same column definitions. One of the column is a TEXT column which will be stored separately from the regular data rows unless I specify otherwise.

The first table does not specify any filegroups so everything should go on the default filegroup. The second table specifies a filegroup for the table (FG1) but not for the LOB data. The third table specifies a filegroup for the table (FG1) and separate filegroup for the LOB data (FG2).

To determine where the data has been stored, I examine the sys.allocation_units view. I need to join it to sys.partitions in order to get the corresponding object_id. The function object_name will give me the name of the table, and the function filegroup_name will translate the column data_space_id into a filegroup name.

USE TestFGs

GO

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

DROP TABLE bigrows;

GO

CREATE TABLE bigrows

(a char(1000),

b varchar(8000),

c text );

GO

INSERT INTO bigrows

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

REPLICATE('c', 50);

go

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) = 'bigrows';

GO

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

DROP TABLE bigrows2;

GO

CREATE TABLE bigrows2

(a char(1000),

b varchar(8000),

c text )

ON FG1;

GO

INSERT INTO bigrows2

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

REPLICATE('c', 50);

go

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%';

GO

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

DROP TABLE bigrows3;

GO

CREATE TABLE bigrows3

(a char(1000),

b varchar(8000),

c text )

ON FG1 TEXTIMAGE_ON FG2;

GO

INSERT INTO bigrows3

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

REPLICATE('c', 50);

GO

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%';

And here are my results. The first table, bigrows1 has both its regular rows and its LOB data on the default filegroup, PRIMARY. You might also notice that an allocation unit for row-overflow data was created, even though we didn’t have any data that needed that space.

The second table, bigrows2, has all its data on FG1 even though I just specified to put the table there. The LOB data is stored with the rest of the table, if I don’t specifically place the LOB data on a different filegroup.

Only for the third table, bigrows3, is the LOB data stored separately, and only because I specifically used the TEXTIMAGE_ON clause when creating the table.

clip_image001

So now you know.

~Kalen

Published Sunday, June 26, 2011 3:27 PM by Kalen Delaney
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Wes Clark said:

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?

June 27, 2011 2:14 PM
 

Kalen Delaney said:

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 code and modify one of the CREATE TABLE commands to specify "default" for the TEXTIMAGE_ON filegroup. Let us know what you get!

Thanks

Kalen

June 27, 2011 2:32 PM
 

Stuart Cowen said:

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 :)

June 28, 2011 3:25 PM
 

Kalen Delaney said:

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 deeper and deeper in!

~Kalen

June 28, 2011 5:52 PM
 

Greg Linwood said:

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 :)

June 29, 2011 8:05 PM
 

Kalen Delaney said:

Hi Greg!

I'm glad it was useful.

:-)

June 29, 2011 8:15 PM
 

Dale said:

I added the following code to the above:

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

DROP TABLE bigrows4;

GO

CREATE TABLE bigrows4

(a char(1000),

b varchar(8000),

c text )

ON FG1 TEXTIMAGE_ON 'default';

GO

INSERT INTO bigrows4

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

REPLICATE('c', 50);

GO

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%';

GO

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

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.

July 5, 2011 10:12 AM
 

Kalen Delaney said:

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

July 5, 2011 3:44 PM
 

Matic Zeljko said:

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.

July 12, 2011 8:19 PM
 

Kalen Delaney said:

Hi Matic

Thanks for the additional data points!

~Kalen

July 12, 2011 9:20 PM
 

Chirag Shah said:

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

January 9, 2012 4:03 PM
 

Kalen Delaney said:

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

January 9, 2012 8:06 PM
 

Yuval said:

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

August 31, 2012 11:19 AM
 

Kalen Delaney said:

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

August 31, 2012 12:50 PM
 

Yuval said:

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

September 1, 2012 1:50 PM
 

Yuval said:

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

September 1, 2012 3:59 PM
 

Kalen Delaney said:

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

September 1, 2012 9:36 PM
 

Yuval said:

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

September 2, 2012 5:07 PM
 

Yuval said:

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

October 15, 2012 3:52 AM
 

Yuval said:

Hi Kalen,

When i use

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

is there any performance advantage using TEXTIMAGE_ON as well?

Thanks,

Yuval

November 13, 2013 8:26 AM
 

Kalen Delaney said:

Hi Yuval

There may be performance benefit in some cases, but the main benefit is administrative. If you put the LOB data on a separate filegroup, you can choose to back it up on a different schedule than the rest of the database.

HTH

Kalen

November 13, 2013 12:47 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement