Recently I was involved in a project where in non-clustered indexes were to be moved to a different file group for the sake of performance benefit. It has to be done for multiple databases. I came up with a SQL Server Using Powershell script to do the same.

An index can be moved to different file group by setting DROP EXISTING = ON and then specifying the name of new file group in the create index script. Thus, to move an index say ix_lastname_firstname on table employee from filegroup Primary to Secondary we need to change the create index script as shown below.

All I had to do is to script out indexes and modify the create script as explained above. I did it using SMO in powershell.

The first part is to get server instance, get database object and create the new file group if it doesn’t exists and map a secondary data file to the new file group. This is done by the below code.

Once new file group is created, iterate through tables and indexes using the below code.

The above code skips partitioned, XML and clustered indexes as I am only interested in moving non-clustered indexes. Also, only indexes which belong to file group other then the new file group are selected. The index script is returned as string collection object, so to get the script we need to iterate through it.

The above code is the core part of the program which replaces the current file group of the indexes with the new one and switches ON DROP_EXISTING option.

The lastindexof function gives the last position of a character in a string. The file group is enclosed in square brackets. So, If I do a substring which starts from lastofindex(“[“)  and is of length ($str.LastIndexOf(“]”) – $str.LastIndexOf(“[“)+1) I will get the exact file group name which is to be replaced. I can then replace the file group name with the new one using Replace function. Similarly I can replace DROP_EXISTING = OFF to DROP_EXISTING = ON in the index script. This gives me the modified index script to move it to a new file group. Another thing, I need to take care of are the unique constraints.

The above code executes a query to check whether an index is a unique constraint or not. The function ExecuteQuery returns 1 if index is a constraint. As constraints first need to be dropped before they are created on to new file group, It appends the drop constraint code to $NewIndexQuery.  The last thing is to execute the new index script.

The above code skips the primary keys and executes the modified index script which moves the index to a new file group. The below query can be used to verify whether the indexes have been moved to new file group or not.

The complete code is available here



Ahmad Osama

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook