Vous êtes ici :
Le déplacement de tables entre groupes de fichiers peut être une opération complexe à réaliser, surtout si ces tables ont des colonnes de type LOB. En effet, simplement reconstruire un index clustered ne suffit pas à déplacer les LOB. Il existe pourtant un moyen de faire ce déplacement : en utilisant le partitionnement de tables et d’index.
Voici une procédure stockée qui permet de faire ce déplacement. J’y ai fait quelques modifications par rapport à sa version originale, en particulier pour rajouter la possibilité de faire de la compression, bien utile en cas d’archivage (et disponible désormais en version standard depuis SQL Server 2016 SP1…)
Transact-SQL
|
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 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 |
*------------------------------------------------------------------- [SCRIPT] sp_MoveTablesToFileGroupWithCompression system procedure [DATABASE] master [DESCRIPTION] Move tables, heaps, index and lobs to filegroups with compression [MAJ PAR] DATAFLY - Arian Papillon -- Modified from script : MoveTablesToFileGroup - Author: Mark White [DATEMAJ] 20190905 -------------------------------------------------------------------*/ USE master; GO CREATE OR ALTER PROC [dbo].[sp_MoveTablesToFileGroupWithCompression] @SchemaFilter VARCHAR(255) = '%', -- Filter which table schemas to work on @TableFilter VARCHAR(255) = '%', -- Filter which tables to work on @DataFileGroup VARCHAR(255) = 'PRIMARY', -- Name of filegroup that data must be moved to @LobFileGroup VARCHAR(255) = NULL, -- Name of filegroup that LOBs (if any) must be moved to @FromFileGroup VARCHAR(255) = '%', -- Only move objects that currenly occupy this filegroup @ClusteredIndexes BIT = 1, -- 1 = move clustered indexes (table data), else 0 @SecondaryIndexes BIT = 1, -- 1 = move secondary indexes, else 0 @Heaps BIT = 1, -- 1 = move heaps, else 0 @Online BIT = 0, -- 1 = keep indexes online (required Enterprise edition) @Compression VARCHAR(4) = NULL, -- NULL = original compression, or ROW or PAGE @ProduceScript BIT = 0 -- 1 = emit a T-SQL script instead of performing the moves AS BEGIN SET NOCOUNT ON; SET CONCAT_NULL_YIELDS_NULL ON; IF FILEGROUP_ID(@DataFileGroup) IS NULL RAISERROR('Invalid Data FileGroup specified.',10,1); IF @Online = 1 AND SERVERPROPERTY('EngineEdition') <> 3 RAISERROR('SQL Server Enterprise edition is required for online index operations.',10,1); IF (@LobFileGroup IS NOT NULL) AND (@@MICROSOFTVERSION / 0x01000000) < 11 RAISERROR('LOB data can only be moved in SQL 2012 or newer. Consider re-creating your table/s.',10,1); DECLARE @SQL VARCHAR(MAX) = ''; DECLARE @Script VARCHAR(MAX) = ''; DECLARE @RANDOM_NAME VARCHAR(100) = REPLACE(NEWID(),'-',''); DECLARE C CURSOR FOR WITH TYPED_COLUMNS AS ( SELECT name = '[' + col.name + '] ', col.is_nullable, col.user_type_id, col.max_length, col.object_id, col.column_id, [type_name] = '[' + typ.name + '] ' from sys.columns col JOIN sys.types typ on typ.user_type_id = col.user_type_id ), INDEX_COLUMNS AS ( SELECT col.*, k.index_id, k.is_included_column, k.key_ordinal, k.is_descending_key from TYPED_COLUMNS col join sys.index_columns as k on k.object_id = col.object_id and k.column_id = col.column_id ) SELECT DISTINCT /* If the table contains LOB data which does not reside where the caller would like it to reside, then use the Brad Hoff's neat partition scheme trick to move LOB data. Effectively, we simply create a partition function & scheme, rebuild the index on that scheme, and then allow the normal rebuild (without partitioning) to be done after wards. For details, see Kimberly Tripp's site: http://www.sqlskills.com/blogs/kimberly/understanding-lob-data-20082008r2-2012/) */ CASE WHEN COALESCE([lob_fg], @LobFileGroup,'PRIMARY') <> COALESCE(@LobFileGroup, [lob_fg], 'PRIMARY') AND [first_ix_col_type] IS NOT NULL AND [type_desc] <> 'NONCLUSTERED' THEN 'CREATE PARTITION FUNCTION PF_' + random_name + ' (' + [first_ix_col_type] + ') AS RANGE RIGHT FOR VALUES (0);' + CHAR(13) + 'CREATE PARTITION SCHEME PS_' + random_name + ' AS PARTITION PF_' + random_name + ' TO ([' + @LobFileGroup + '],[' + @LobFileGroup + ']);' + CHAR(13) + CHAR(13) + CASE [type_desc] WHEN 'HEAP' -- create index on first column THEN 'CREATE CLUSTERED ' + index_on_table + ' (' + [first_ix_col_name] + ') ' + options + ' ON PS_' + random_name + '(' + [first_ix_col_name] + ');' + CHAR(13) + 'DROP ' + index_on_table + ';' + CHAR(13) WHEN 'CLUSTERED' THEN 'CREATE ' + is_unique + ' CLUSTERED ' + index_on_table + ' (' + index_columns + ')' + CHAR(13) + [includes / filters] + options + 'ON PS_' + random_name + '(' + [first_ix_col_name] + ');' + CHAR(13) WHEN 'NONCLUSTERED' THEN 'CREATE ' + is_unique + index_on_table + ' (' + index_columns + ')' + CHAR(13) + [includes / filters] + options + 'ON PS_' + random_name + '(' + [first_ix_col_name] + ');' + CHAR(13) END + CHAR(13) ELSE '' END + CASE [type_desc] WHEN 'HEAP' -- create index on first column THEN 'CREATE CLUSTERED ' + index_on_table + ' (' + [first_ix_col_name] + ') ' + options + ' ON [' + @DataFileGroup + '];' + CHAR(13) + 'DROP ' + index_on_table + ';' + CHAR(13) WHEN 'CLUSTERED' THEN 'CREATE ' + is_unique + ' CLUSTERED ' + index_on_table + ' (' + index_columns + ')' + CHAR(13) + [includes / filters] + options + 'ON [' + @DataFileGroup + '];' WHEN 'NONCLUSTERED' THEN 'CREATE ' + is_unique + index_on_table + ' (' + index_columns + ')' + CHAR(13) + [includes / filters] + options + 'ON [' + @DataFileGroup + '];' END + CASE WHEN COALESCE([lob_fg], @LobFileGroup,'PRIMARY') <> COALESCE(@LobFileGroup, [lob_fg], 'PRIMARY') AND [first_ix_col_type] IS NOT NULL THEN CHAR(13) + CHAR(13) + 'DROP PARTITION SCHEME PS_' + random_name + ';' + CHAR(13) + 'DROP PARTITION FUNCTION PF_' + random_name + ';' + CHAR(13) + CHAR(13) ELSE '' END FROM ( select distinct index_on_table = 'INDEX [' + ISNULL(i.name, 'PK_' + sch.name + '_' + obj.name) COLLATE DATABASE_DEFAULT + ']' + CHAR(13) + 'ON [' + sch.name + '].[' + obj.name + ']', type_desc = i.type_desc, is_unique = CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END , [lob_fg] = CASE WHEN (i.type IN (0,1) AND EXISTS (SELECT * FROM TYPED_COLUMNS col WHERE col.object_id = obj.object_id and col.max_length = -1)) OR (i.type = 2 AND EXISTS (SELECT * FROM INDEX_COLUMNS col WHERE col.object_id = i.object_id and col.index_id = i.index_id AND col.max_length = -1)) THEN FILEGROUP_NAME(obj.lob_data_space_id) END, [index_columns] = REPLACE( ISNULL( (SELECT col.name + CASE WHEN is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END as [data()] FROM INDEX_COLUMNS col where col.object_id = i.object_id and col.index_id = i.index_id and col.is_included_column <> 1 and i.type in (1,2) order by key_ordinal for xml path('')) , (select '[' + col.name + '] ' as [data()] from sys.columns col where col.object_id = i.object_id and i.type = 0 and (col.user_type_id in (48,52,56,58,59,62,104,127,106,108) or col.max_length between 1 and 800) order by col.is_nullable desc for xml path('')) ) , ' [' , ', [' ), [first_ix_col_name] = ISNULL( (SELECT TOP 1 col.name FROM INDEX_COLUMNS col WHERE col.object_id = i.object_id and col.index_id = i.index_id and col.is_included_column <> 1 and i.type in (1,2) order by key_ordinal) , (select TOP 1 name --type_name from TYPED_COLUMNS col where col.object_id = i.object_id and i.type = 0 and (col.user_type_id in (48,52,56,58,59,62,104,127,106,108) or col.max_length between 1 and 800) order by col.is_nullable desc) ), [first_ix_col_type] = ISNULL( (select TOP 1 type_name FROM INDEX_COLUMNS col JOIN sys.types typ on typ.user_type_id = col.user_type_id WHERE col.object_id = i.object_id and col.index_id = i.index_id and col.is_included_column <> 1 and i.type in (1,2) order by key_ordinal) , (select TOP 1 type_name + CASE WHEN col.user_type_id not in (48,52,56,58,59,62,104,127,106,108) THEN '(' + CONVERT(VARCHAR(10), col.max_length) + ')' ELSE '' END from TYPED_COLUMNS col where col.object_id = i.object_id and i.type = 0 and (col.user_type_id in (48,52,56,58,59,62,104,127,106,108) or col.max_length between 1 and 800) order by col.is_nullable desc) ), [includes / filters] = ISNULL( REPLACE('INCLUDE (*)','*', REPLACE( (select col.name as [data()] from INDEX_COLUMNS col where col.object_id = i.object_id and col.index_id = i.index_id and col.is_included_column <> 0 order by key_ordinal, col.column_id for xml path('') ), '] [', '], [' )) + CHAR(13) ,'' ) + ISNULL ('WHERE ' + i.filter_definition + CHAR(13) , ''), options = ' WITH (' + ISNULL( CASE WHEN i.type IN (1, 2) THEN 'DROP_EXISTING = ON ' END, 'DROP_EXISTING = OFF') + ISNULL(', FILLFACTOR = ' + NULLIF(CAST(fill_factor AS VARCHAR(10)), '0') , '') + ISNULL(', PAD_INDEX = ' + CASE is_padded WHEN 1 THEN 'ON' ELSE 'OFF' END, '') + ISNULL(', ALLOW_ROW_LOCKS = ' + CASE allow_row_locks WHEN 1 THEN 'ON' ELSE 'OFF' END ,'') + ISNULL(', ALLOW_PAGE_LOCKS = ' + CASE allow_page_locks WHEN 1 THEN 'ON' ELSE 'OFF' END , '') + ISNULL(', IGNORE_DUP_KEY = ' + CASE ignore_dup_key WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' END, '') + CASE WHEN @Compression IS NULL THEN ISNULL(', DATA_COMPRESSION = ' + data_compression_desc, '') ELSE ', DATA_COMPRESSION = ' + @Compression END + ISNULL(', STATISTICS_NORECOMPUTE = ' + CASE no_recompute WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' END, '') + CASE WHEN @Online = 1 AND SERVERPROPERTY('EngineEdition') = 3 THEN ', ONLINE = ON' ELSE '' END + ') ' + CHAR(13), random_name = 'MOVE_HELPER_' + @RANDOM_NAME from sys.indexes i (readpast) join sys.filegroups f (readpast) ON i.data_space_id = f.data_space_id join sys.tables obj (readpast) ON i.object_id=obj.object_id join sys.schemas sch ON obj.schema_id=sch.schema_id left join sys.partitions part ON i.object_id = part.object_id and i.index_id = part.index_id left join sys.stats stats ON i.object_id=stats.object_id and i.index_id = stats.stats_id where sch.name <> 'sys' and sch.name LIKE ISNULL(@SchemaFilter, '%') and obj.name LIKE ISNULL(@TableFilter, '%') and f.name LIKE ISNULL(@FromFileGroup, '%') and (f.name <> @DataFileGroup OR COALESCE(FILEGROUP_NAME(obj.lob_data_space_id), @LobFileGroup,'PRIMARY') <> COALESCE(@LobFileGroup, FILEGROUP_NAME(obj.lob_data_space_id), 'PRIMARY')) and ( (i.type = 1 AND @ClusteredIndexes = 1) OR (i.type = 2 AND @SecondaryIndexes = 1) OR (i.type = 0 AND @Heaps = 1) ) ) AS Script_Builder OPEN C; FETCH NEXT FROM C INTO @SQL; WHILE @@FETCH_STATUS = 0 BEGIN IF @ProduceScript = 1 SELECT @SQL ELSE EXEC (@SQL); FETCH NEXT FROM C INTO @SQL; END CLOSE C; DEALLOCATE C; END GO EXEC sp_ms_marksystemobject 'sp_MoveTablesToFileGroupWithCompression' |
Table of Contents