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 
