Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server – Moving table with LOB data to different file group

Last week, i posted about moving tables to different file groups by recreating indexes. However, this method does not *work* when there is LOB data involved.

When we recreate indexes on a table containing LOB data, the LOB data pages are not moved to destination file group. This can be demonstrated with below example:

USE [master]

GO

 

/* Create a database with 2 file groups */

CREATE DATABASE [TestDB]

ON PRIMARY

(     NAME = N'TestDB',

      FILENAME = N'C:\Database\Data\TestDB.mdf' ),

FILEGROUP [SecondaryFG]

(     NAME = N'TestDB_2',

      FILENAME = N'C:\Database\Data\TestDB_2.ndf' )

LOG ON

(     NAME = N'TestDB_log',

      FILENAME = N'C:\Database\Log\TestDB_log.ldf' )

GO

 

/* Create a table on [PRIMARY] file group */

CREATE TABLE T2

(     [ID] INT IDENTITY(1,1),

      [Name] VARCHAR(MAX),

      [Status] SMALLINT

)

GO

 

/* store LOB data out of row */

EXEC sp_tableoption 'dbo.T2', 'large value types out of row', 1

GO

 

CREATE UNIQUE CLUSTERED INDEX [IX_CL_T2_ID] ON [dbo].[T2]

(     [ID] ASC

) GO

 

CREATE NONCLUSTERED INDEX [IX_NCL_T2_Status] ON [dbo].[T2]

(     [Status] ASC

) GO

 

INSERT INTO T2([Name], [Status])

VALUES ('LOB data', 1)

GO

 

SELECT      OBJ.name, OBJ.type, FG.name

FROM        sys.indexes IDX

INNER JOIN  sys.filegroups FG ON IDX.data_space_id = FG.data_space_id

INNER JOIN  sys.objects OBJ ON IDX.object_id = OBJ.object_id

WHERE       OBJ.type = 'U'

GO

 

DBCC IND('TestDB', 'T2', 1)

GO

 

SELECT      DF.file_id, F.name

FROM        sys.filegroups F

INNER JOIN  sys.database_files DF ON DF.data_space_id = F.data_space_id

GO

Result Set:

name   type   name

T2     U      PRIMARY

T2     U      PRIMARY

 

PageFID PagePID     IAMFID IAMPID      ObjectID    …  iam_chain_type

1       158         NULL   NULL        2105058535  …  In-row data

1       157         1      158         2105058535  …  In-row data

1       156         NULL   NULL        2105058535  …  LOB data

1       155         1      156         2105058535  …  LOB data

 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

file_id     name

1           PRIMARY

3           SecondaryFG

From the result set we can see that:

1. Both indexes are on [PRIMARY] file group.

2. All allocated pages are from file with file_id = 1

3. File with file_id = 1 is in [PRIMARY] file group.

Now let’s try to move the table to [SecondaryFG] by rebuilding indexes:

USE [TestDB]

GO

 

/* Re-create indexes on [SecondaryFG] */

CREATE UNIQUE CLUSTERED INDEX [IX_CL_T2_ID] ON [dbo].[T2]

(

      [ID] ASC

) WITH (DROP_EXISTING = ON)

ON [SecondaryFG]

GO

 

CREATE NONCLUSTERED INDEX [IX_NCL_T2_Status] ON [dbo].[T2]

(

      [Status] ASC

) WITH (DROP_EXISTING = ON)

ON [SecondaryFG]

GO

 

/* Indexes have moved to [SecondaryFG] */

SELECT      OBJ.name, OBJ.type, FG.name

FROM        sys.indexes IDX

INNER JOIN  sys.filegroups FG ON IDX.data_space_id = FG.data_space_id

INNER JOIN  sys.objects OBJ ON IDX.object_id = OBJ.object_id

WHERE       OBJ.type = 'U'

GO

 

/* But not LOB pages,

PageFID = FileID of file from which the page is allocated */

DBCC IND('TestDB', 'T2', 1)

GO

 

/* File ID 1 is from [PRIMARY] file group

File ID 3 is from [SecondaryFG] file group */

SELECT      DF.file_id, F.name

FROM        sys.filegroups F

INNER JOIN  sys.database_files DF ON DF.data_space_id = F.data_space_id

GO

Result Set:

name   type   name

T2     U      SecondaryFG

T2     U      SecondaryFG

 

PageFID PagePID     IAMFID IAMPID      ObjectID    … iam_chain_type

3       9           NULL   NULL        2105058535  … In-row data

3       8           3      9           2105058535  … In-row data

1       156         NULL   NULL        2105058535  … LOB data

1       155         1      156         2105058535  … LOB data

 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

file_id     name

1           PRIMARY

3           SecondaryFG

From this result set we can see that:

1. Rebuilding indexes have moved them to [SecondaryFG] file group.

2. In-row data pages are now allocated from File with file_id = 3

3. LOB data pages have not moved, and are still in file with file_id = 1

4. File with file_id = 1 is in [PRIMARY] file group, and File with file_id = 3 is in
[SecondaryFG] file group.

So how do we move the LOB data pages? You need to drop and recreate the table if you need to move LOB data. The general steps to follow are:

1. Create a new table with same structure on destination file group i.e. [SecondaryFG]

2. Import rows from old table

3. Delete old table

4. Rename new table

For example:

USE [TestDB]

GO

 

/* Create a new table on new file group */

CREATE TABLE Tmp_T2

(

      [ID]        INT    IDENTITY(1,1),

      [Name]      VARCHAR(MAX),

      [Status]    SMALLINT

) ON [SecondaryFG]

GO

 

EXEC sp_tableoption 'dbo.Tmp_T2', 'large value types out of row', 1

GO

 

CREATE UNIQUE CLUSTERED INDEX [IX_CL_Tmp_T2_ID] ON [dbo].[Tmp_T2]

(

      [ID] ASC

) ON [SecondaryFG]

GO

 

CREATE NONCLUSTERED INDEX [IX_NCL_Tmp_T2_Status] ON [dbo].[Tmp_T2]

(

      [Status] ASC

) ON [SecondaryFG]

GO

 

/* Import data from old table */

SET IDENTITY_INSERT Tmp_T2 ON

INSERT INTO Tmp_T2([ID], [Name], [Status])

SELECT * FROM T2

SET IDENTITY_INSERT Tmp_T2 OFF

GO

 

/* Delete old table */

DROP TABLE T2

 

/* Rename the temporary table */

EXEC sp_rename 'dbo.Tmp_T2', 'T2'

GO

 

/* Rename indexes, not necessary but useful when they have been used in hints. */

EXEC sp_rename    N'dbo.T2.IX_CL_Tmp_T2_ID',

                  N'IX_CL_T2_ID',

                  N'INDEX'

GO

 

EXEC sp_rename    N'dbo.T2.IX_NCL_Tmp_T2_Status',

                  N'IX_NCL_T2_Status',

                  N'INDEX'

GO

This script implements the steps described above:

1. It creates a new table/indexes dbo.Tmp_T2 on [SecondaryFG] with the same structure
as dbo.T2

2. Imports all rows from dbo.T2 into dbo.Tmp_T2

3. Deletes dbo.T2

4. Renames dbo.Tmp_T2 to dbo.T2. It also renames the indexes as required.

You can check the page allocations using the queries we used earlier:

USE [TestDB]

GO

 

/* Indexes are on [SecondaryFG] .. OK. */

SELECT      OBJ.name, OBJ.type, FG.name

FROM        sys.indexes IDX

INNER JOIN  sys.filegroups FG ON IDX.data_space_id = FG.data_space_id

INNER JOIN  sys.objects OBJ ON IDX.object_id = OBJ.object_id

WHERE       OBJ.type = 'U'

GO

 

/* LOB Pages are on File ID = 3 */

DBCC IND('TestDB', 'T2', 1)

GO

 

/* File ID 3 is in [SecondaryFG] .. OK. */

SELECT      DF.file_id, F.name

FROM        sys.filegroups F

INNER JOIN  sys.database_files DF ON DF.data_space_id = F.data_space_id

GO

Result Set:

name   type   name

T2     U      SecondaryFG

T2     U      SecondaryFG

 

PageFID PagePID     IAMFID IAMPID      ObjectID    …    iam_chain_type

3       11          NULL   NULL        2121058592  …    In-row data

3       10          3      11          2121058592  …    In-row data

3       9           NULL   NULL        2121058592  …    LOB data

3       8           3      9           2121058592  …    LOB data

 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

file_id     name

1           PRIMARY

3           SecondaryFG

From this result we can see that all indexes and LOB data pages have moved to file with file_id = 3, which is in [SecondaryFG].

Hope This Helps! Cheers!

Reference : Vishal (http://SqlAndMe.com)


Filed under: SQLServer

Comments

Posted by Anonymous on 20 October 2011

Pingback from  Dew Drop – October 20, 2011 | Alvin Ashcraft's Morning Dew

Posted by Jason Brimhall on 26 October 2011

This is a very useful tip.  It is related to questions I have seen come up from time to time.  This demonstrates very well how to solve it.

Posted by rparnell on 26 October 2011

I recently had to move LOB out of one table to another, but within the same file. The goal was to speed up queries and only grabbing the LOB once the desired rows were found.

In the end, it was dramatically faster to move the non-LOB data and rename the tables (created a 1 to 1 relationship) so it felt like I moved the LOB, rather than actually move the LOB columns themselves. Duh, but it didn't dawn on me right away.

Hope this helps someone else too.

Posted by Anonymous on 26 October 2011

Pingback from  SQL Server ??? Moving table with LOB data to... | SQL Server | Syngu

Leave a Comment

Please register or log in to leave a comment.