Jeremy Carter

It’s moving day!

Occasionally I come across a situation where someone wants to move their non-clustered indexes to a new filegroup. Why would you want to do this? Typically it has to do with disk space or performance issues. By adding filegroups you can create the additional files on separate raid groups thereby giving you access to the space and speed of the additional spindles. File groups also gives you control over which objects are created on this new file group. This control can be very good if you have identified particular hot spots for certain tables/indexes you wish to isolate.

The first step we will need to complete is creating the new file group. For the purposes of my example I am using the AdventureWorks database and creating a new filegroup named IndexData.

ALTER DATABASE [AdventureWorks] ADD FILEGROUP IndexData;

Once the file group is created you then need to create the file(s). You will need to change the filename to the path you wish to use. You may also wish to change the initial file size rather than relying on the autogrow.

ALTER DATABASE [AdventureWorks] ADD FILE
(NAME = IndexData,
FILENAME = 'F:\sqldata\AdventureWorks_IndexData.ndf',
SIZE = 5MB,
FILEGROWTH = 250MB
) TO FILEGROUP IndexData;

Finally once the prep-work of preparing the filegroup and files is complete, we can work on moving the objects to the newly created filegroup. As is the following script will generate all of the necessary statements for SQL 2008 to move all of the non clustered indexes to the new filegroup. With a bit of tweaking it can work with 2005 or be used to move specific indexes or even the clustered indexes.

WITH index_cte (table_name, index_name, type_desc, index_columns, index_included_columns,
filter_definition, is_unique, is_primary_key, is_unique_constraint,fill_factor,
is_padded, allow_row_locks, allow_page_locks, ignore_dup_key, data_compression_desc,
no_recompute)
as(
select
sch.name+'.'+obj.name [table_name],
i.name [index_name],
i.type_desc,
REPLACE(
(select '['+col.name+'] '+
	CASE WHEN k.is_descending_key = 1 THEN 'DESC'
	ELSE 'ASC'
	END as [data()]
from sys.index_columns as k
inner join sys.columns col
		on k.object_id=col.object_id
		and k.column_id=col.column_id
	where k.object_id = i.object_id
	and k.index_id = i.index_id
	and k.index_id<>0
	and k.is_included_column<>1
	order by key_ordinal, k.column_id
for xml path(''))
,'C [','C, [') as [index_columns],
isnull(
REPLACE(
	(select '['+col.name+']' as [data()]
from sys.index_columns as k
inner join sys.columns col
		on k.object_id=col.object_id
		and k.column_id=col.column_id
	where k.object_id = i.object_id
	and k.index_id = i.index_id
	and k.index_id<>0
	and k.is_included_column<>0
	order by key_ordinal, k.column_id
for xml path(''))
,'] [','], [')
,'') as [index_included_columns],
ISNULL(i.filter_definition,'') as filter_definition,
is_unique,
is_primary_key,
is_unique_constraint,
fill_factor,
is_padded,
allow_row_locks,
allow_page_locks,
ignore_dup_key,
data_compression_desc,
no_recompute
from sys.indexes as i
inner join sys.objects obj
	on i.object_id=obj.object_id
inner join sys.schemas sch
	on obj.schema_id=sch.schema_id
inner join sys.partitions part
	on i.object_id=part.object_id
	and i.index_id=part.index_id
inner join sys.stats stats
	on i.object_id=stats.object_id
	and i.index_id=stats.stats_id
where i.name is not null
and obj.type<>'S'
and sch.name<>'sys'
and i.index_id>1
--and sch.name='HumanResources'
--and obj.name='Employee'
)
SELECT
'CREATE ' +
CASE is_unique
	WHEN 1 THEN 'UNIQUE '
	ELSE ''
END
+ type_desc
+' INDEX '
+ index_name COLLATE DATABASE_DEFAULT
+' ON '
+table_name
+' ('+index_columns+')'+
CASE index_included_columns
	WHEN '' THEN ''
	ELSE ' INCLUDE (' + index_included_columns + ') '
END+
CASE filter_definition
	WHEN '' THEN ''
	ELSE ' WHERE ' + filter_definition
END+
' WITH ('+
'FILLFACTOR = ' + CASE fill_factor WHEN 0 THEN '100' ELSE CAST(fill_factor as varchar) END+
', PAD_INDEX = ' + CASE is_padded WHEN 1 THEN 'ON' ELSE 'OFF' END+
', ALLOW_PAGE_LOCKS = ' + CASE allow_page_locks WHEN 1 THEN 'ON' ELSE 'OFF' END+
', ALLOW_ROW_LOCKS = ' + CASE allow_row_locks WHEN 1 THEN 'ON' ELSE 'OFF' END+
', IGNORE_DUP_KEY = ' + CASE ignore_dup_key WHEN 1 THEN 'ON' ELSE 'OFF' END+
', STATISTICS_NORECOMPUTE = ' + CASE no_recompute WHEN 1 THEN 'ON' ELSE 'OFF' END+
', DATA_COMPRESSION = ' + data_compression_desc +
', DROP_EXISTING = ON'+
')' + ' ON IndexData'
as [create_statement]
from index_cte
order by table_name, type_desc, index_name

And there you have it. Once you run the newly generated script the indexes will be moved to the new filegroup. Make sure you test, test, test before running this in production so you are completely certain of what you are moving and where you are moving it.