Moving Data to a New Filegroup
Occasionally, you may need to move a table from one filegroup to another. Perhaps someone inadvertently created a table in the system filegroup, but company policy dictates never to do that. Perhaps you need to split data up between several filegroups to get better I/O performance. Either way, manually moving data from one filegroup to another can be a tedious, time-consuming, and error-prone process.
How not to move a table!
This post provides an automated script that can handle many scenarios where you need to move data from one filegroup to another.
The script temporarily creates a stored procedure, executes the stored procedure several times, then drops the stored procedure. I recommend running the stored procedure with the @DebugOnly parameter set to 1, at least initially, to see what T-SQL statements will be executed. If you set the @DebugOnly parameter to 0, those T-SQL statements will be executed, and the selected indexes will be moved from the @OldFileGroupName to the @NewFileGroupName filegroup.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 |
CREATE OR ALTER PROCEDURE dbo.MoveDataToCustomFilegroups ( @IndexType int , @OldFileGroupName sysname , @NewFileGroupName sysname , @DebugOnly bit = 0 ) AS BEGIN /* Description: MOVE ALL INDEXES of specified @IndexType from @OldFileGroupName to @NewFileGroupName By: Hannah Vernon NOTE: Should not be used on partitioned tables. */ SET NOCOUNT ON; DECLARE @OnlineOption bit; DECLARE @msg nvarchar(255); DECLARE @cmd nvarchar(max); /* @IndexType can be 0 = Heap 1 = Clustered 2 = Nonclustered 3 = XML 4 = Spatial */ IF NOT EXISTS (SELECT 1 FROM (VALUES (0), (1), (2), (3), (4))v(n) WHERE v.n = @IndexType ) BEGIN SET @msg = N'Invalid @IndexType specified. @IndexType can be 0 = Heap 1 = Clustered 2 = Nonclustered 3 = XML 4 = Spatial '; THROW 50000, @msg, 1; END IF @DebugOnly = 1 BEGIN SET @msg = N'Running in DEBUG mode. No statements will be executed.'; RAISERROR (@msg, 10, 1); END SET @cmd = N''; SET @msg = N''; SET @OnlineOption = 0; IF EXISTS (SELECT name FROM sys.data_spaces WHERE name = @OldFileGroupName) BEGIN IF EXISTS (SELECT name FROM sys.data_spaces WHERE name = @NewFileGroupName) BEGIN IF @IndexType = 0 BEGIN /* Heaps require building a clustered index on the target filegroup, then dropping the clustered index. */ SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE CHAR(13) + CHAR(10) END + N'CREATE CLUSTERED INDEX [CX_' + o.name + N'_' + (SELECT TOP(1) col_c.name FROM sys.columns col_c WHERE col_c.object_id = o.object_id ORDER BY col_c.column_id) + N'] ON ' + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + N' (' + QUOTENAME((SELECT TOP(1) col_c.name FROM sys.columns col_c WHERE col_c.object_id = o.object_id ORDER BY col_c.column_id)) + N') WITH (FILLFACTOR=100, DATA_COMPRESSION=PAGE) ON ' + QUOTENAME(@NewFileGroupName) + N'; DROP INDEX [CX_' + o.name + N'_' + (SELECT TOP(1) col_c.name FROM sys.columns col_c WHERE col_c.object_id = o.object_id ORDER BY col_c.column_id) + N'] ON ' + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + N'; ' FROM sys.indexes i INNER JOIN sys.objects o on i.object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id INNER JOIN sys.partitions p ON o.object_id = p.object_id AND i.index_id = p.index_id WHERE i.type = 0 /* HEAP */ AND o.type = 'U' /* USER_TABLE */ AND o.is_ms_shipped = 0 AND ds.name = @OldFileGroupName; END ELSE BEGIN SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE CHAR(13) + CHAR(10) END + CASE WHEN i.is_primary_key = 1 AND i.type = 2 THEN N' BEGIN TRANSACTION BEGIN TRY ALTER TABLE ' + QUOTENAME(s.name) + N'.'+ QUOTENAME(o.name) + N' DROP CONSTRAINT ' + QUOTENAME(i.name) + N'; ALTER TABLE ' + QUOTENAME(s.name) + N'.'+ QUOTENAME(o.name) + N' ADD CONSTRAINT ' + QUOTENAME(i.name) + N' PRIMARY KEY NONCLUSTERED (' WHEN i.is_primary_key = 1 AND i.type = 1 THEN N' BEGIN TRANSACTION BEGIN TRY ALTER TABLE ' + QUOTENAME(s.name) + N'.'+ QUOTENAME(o.name) + N' DROP CONSTRAINT ' + QUOTENAME(i.name) + N'; ALTER TABLE ' + QUOTENAME(s.name) + N'.'+ QUOTENAME(o.name) + N' ADD CONSTRAINT ' + QUOTENAME(i.name) + N' PRIMARY KEY CLUSTERED (' ELSE N'CREATE ' + CASE WHEN i.is_unique = 1 THEN N'UNIQUE ' ELSE N'' END + i.type_desc + N' INDEX ' + QUOTENAME(i.name) + N' ON ' + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + N'(' END + ( SELECT STUFF(( SELECT N', ' + QUOTENAME(col_c.name) + CASE WHEN col_ic.is_descending_key = 1 THEN N' DESC' ELSE N'' END FROM sys.indexes col_i INNER JOIN sys.index_columns col_ic ON col_i.object_id = col_ic.object_id AND col_i.index_id = col_ic.index_id INNER JOIN sys.columns col_c ON col_ic.object_id = col_c.object_id AND col_ic.column_id = col_c.column_id WHERE col_ic.is_included_column = 0 AND col_i.object_id = i.object_id AND col_i.index_id = i.index_id ORDER BY col_ic.key_ordinal FOR XML PATH('') ),1,2,'') ) + N')' + CHAR(13) + CHAR(10) + CASE WHEN ( SELECT COUNT(1) FROM sys.indexes col_i INNER JOIN sys.index_columns col_ic ON col_i.object_id = col_ic.object_id AND col_i.index_id = col_ic.index_id INNER JOIN sys.columns col_c ON col_ic.object_id = col_c.object_id AND col_ic.column_id = col_c.column_id WHERE col_ic.is_included_column = 1 AND col_i.object_id = i.object_id AND col_i.index_id = i.index_id ) > 0 THEN N' INCLUDE (' + (SELECT STUFF(( SELECT N', ' + QUOTENAME(col_c.name) FROM sys.indexes col_i INNER JOIN sys.index_columns col_ic ON col_i.object_id = col_ic.object_id AND col_i.index_id = col_ic.index_id INNER JOIN sys.columns col_c ON col_ic.object_id = col_c.object_id AND col_ic.column_id = col_c.column_id WHERE col_ic.is_included_column = 1 AND col_i.object_id = i.object_id AND col_i.index_id = i.index_id ORDER BY col_ic.key_ordinal FOR XML PATH(N'') ),1,2,N'') ) + N')' ELSE N'' END + CASE WHEN i.has_filter = 1 THEN N' WHERE ' + i.filter_definition ELSE N'' END + N' WITH (' + CASE WHEN NOT(i.is_primary_key = 1 AND i.type = 2) THEN N'DROP_EXISTING = ON, ' ELSE N'' END + CASE WHEN i.fill_factor > 0 AND i.fill_factor < 100 THEN N'FILLFACTOR = ' + CONVERT(NVARCHAR(3), i.fill_factor) + N', ' ELSE N'' END + N'PAD_INDEX = ' + CASE WHEN i.is_padded = 1 THEN N'ON' ELSE N'OFF' END + N', ' + N'IGNORE_DUP_KEY = ' + CASE WHEN i.ignore_dup_key = 1 THEN N'ON' ELSE N'OFF' END + N', ' + CASE WHEN NOT(i.is_primary_key = 1 AND i.type = 2) THEN N'ONLINE = ' + CASE WHEN @OnlineOption = 1 THEN N'ON' ELSE N'OFF' END + N', ' ELSE N'' END + N'ALLOW_ROW_LOCKS = ' + CASE WHEN i.allow_row_locks = 1 THEN N'ON' ELSE N'OFF' END + N', ' + N'ALLOW_PAGE_LOCKS = ' + CASE WHEN i.allow_page_locks = 1 THEN N'ON' ELSE N'OFF' END + N', ' + N'DATA_COMPRESSION = ' + p.data_compression_desc + N') ON ' + N'[' + @NewFileGroupName + N']; ' + CASE WHEN i.is_primary_key = 1 AND (i.type = 1 OR i.type = 2) THEN N' COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION END CATCH' ELSE N' ' END FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id INNER JOIN sys.partitions p ON o.object_id = p.object_id AND i.index_id = p.index_id WHERE o.type = 'U' /* USER_TABLE */ AND i.type = @IndexType AND o.is_ms_shipped = 0 AND ds.name = @OldFileGroupName --AND i.name = 'AcisC5ModuleLaunc3' ORDER BY o.name, i.name; END IF @cmd = N'' BEGIN SET @msg = N'Warning: no indexes found on ' + @OldFileGroupName + N'!'; THROW 50000, @msg, 3; END ELSE BEGIN RAISERROR (@cmd, 0, 1) WITH NOWAIT; IF @DebugOnly = 0 BEGIN EXEC sp_executesql @cmd; END END END ELSE BEGIN SET @msg = N'ERROR: ' + @NewFileGroupName + N' does not exist!'; THROW 50000, @msg, 2; END END ELSE BEGIN SET @msg = 'ERROR: ' + @OldFileGroupName + N' does not exist!'; THROW 50000, @msg, 2; END END GO </code> |
Example usage for the procedure:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
/* move heaps from PRIMARY to fgdata01 */ EXEC dbo.MoveDataToCustomFilegroups @IndexType = 0 , @OldFileGroupName = 'PRIMARY' , @NewFileGroupName = 'fgdata01'; /* move Clustered Indexes from PRIMARY to fgdata01 */ EXEC dbo.MoveDataToCustomFilegroups @IndexType = 1 , @OldFileGroupName = 'PRIMARY' , @NewFileGroupName = 'fgdata01'; /* Non-Clustered Indexes from PRIMARY to fgindex01 */ EXEC dbo.MoveDataToCustomFilegroups @IndexType = 2 , @OldFileGroupName = 'PRIMARY' , @NewFileGroupName = 'fgindex01'; /* Non-Clustered Indexes fgdata01 to fgindex01 */ EXEC dbo.MoveDataToCustomFilegroups @IndexType = 2 , @OldFileGroupName = 'fgdata01' , @NewFileGroupName = 'fgindex01'; GO /* drop the stored procedure */ DROP PROCEDURE dbo.MoveDataToCustomFilegroups; |
It would be great if you could simply rebuild a table with a modified ON [...] clause to move all associated data and indexes into a new filegroup. Something like ALTER TABLE <sometable> REBUILD ON <newfilegroup>;. Unfortunately, the required syntax is not supported. Alternately, it might be nice if the ALTER TABLE ... SWITCH syntax supported specifying a different filegroup. Alas, that doesn’t work either.
Check out the rest of our SQL Server tools!