Move all DB indexes with page_count of 10000 or less to new filegroup

SQL Server Administration
1

Hello,

I am already able to move nonclustered, nonunique indexes with page count of 10,000 or less to secondary filegroup manually one by one. I have to move 1,100 of them.

I am looking for a way to do this programmatically either in bulk or batches or all in one shot. I'm using SQL Server 2017.

Thanks in advance

2 
DECLARE @myfg sysname=N'my_filegroup';  -- change to the name of the filegroup you're moving to
select sch.name, tbl.name, ind.name idx
,CONCAT(N'CREATE NONCLUSTERED INDEX ',QUOTENAME(ind.name), N' ON ', QUOTENAME(sch.name), N'.', QUOTENAME(tbl.name), N'('
,key_col.key_cols,N') ', N' INCLUDE(' + inc_col.inc_cols + N')'
,N' WHERE ' + ind.filter_definition, N' WITH (DROP_EXISTING=ON) ON ', QUOTENAME(@myfg), N';') as sql_statement
from sys.tables tbl
inner join sys.schemas sch on tbl.schema_id=sch.schema_id
inner join sys.indexes ind on tbl.object_id=ind.object_id
inner join sys.data_spaces ds on ind.data_space_id=ds.data_space_id
cross apply sys.dm_db_index_physical_stats(DB_ID(),ind.object_id,ind.index_id,null,null) indps
cross apply (select STRING_AGG(quotename(c.name),N',') WITHIN GROUP (ORDER BY ic.index_column_id ASC)
    from sys.index_columns ic
    inner join sys.columns c on ic.object_id=c.object_id and ic.column_id=c.column_id
    where ind.index_id=ic.index_id and ic.object_id=ind.object_id and ic.is_included_column=0) key_col(key_cols)
cross apply (select STRING_AGG(quotename(c.name),N',') WITHIN GROUP (ORDER BY ic.index_column_id ASC)
    from sys.index_columns ic
    inner join sys.columns c on ic.object_id=c.object_id and ic.column_id=c.column_id
    where ind.index_id=ic.index_id and ic.object_id=ind.object_id and ic.is_included_column=1) inc_col(inc_cols)
where tbl.is_ms_shipped=0 -- no system tables
and ind.index_id>1 -- non-clustered indexes only
and ind.is_unique=0 -- non-unique indexes only
and indps.page_count<10000 -- smaller than this page count
and ds.name<>@myfg -- not already on designated filegroup

There are some index options like data compression, fillfactor, etc. that I haven't included, to keep the SQL relatively simple. They can be changed with ALTER INDEX afterward, possibly in a REBUILD. I also haven't tested if the INCLUDE or the WHERE works correctly, so there may be some bugs but should be easy to fix.

You can put the sql_statement column output into a variable and then EXEC(@var) to run it, but I'd recommend copying and pasting the generated SQL and running it manually, at least at first.

Edit: added missing QUOTENAME

1 Like
3

What is the purpose of moving these to a separate filegroup?

4

Hello, the purpose is performance optimization. The destination filegroup saves to a file located on a different drive. Thanks

5

I will take a look and do some testing. Thank you

6

So - is that different drive accessed through separate channels to the SAN? Or is it going through the same HBA as all other volumes?

Is that separate drive on separate spindles - or is this an Enterprise level SAN where the data would be spread across hundreds of drives?

Unless you have configured the hardware appropriately - and the storage, then moving to a secondary filegroup isn't going to help your performance. If your system is a virtual system (VMware, Hyper-V) - then it really isn't going to help since that storage is most likely presented from the storage pool and not directly.