SQL Server Using Powershell to Move Indexes to New File group

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.

CREATE UNIQUE NONCLUSTERED INDEX [ix_lastname_firstname] ON [Employee]
(
    [Lastname] ASC,
    [Firstname] ASC
)WITH (DROP_EXISTING = ON) ON [SECONDARY] -- Replace Primary with Secondary.

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.

# get server object
$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst
# get database object
$database = $srv.Databases[$db] 
#check that the supplied filegroup exists or not?
foreach($fgs in $database.filegroups)
{
   if($fgs.Name -eq $fg)
   {
       $fgcount=1
       break;
   }
    
}   
 #create filegroup and a file if it doesn't exists
if($fgcount -eq 0)
{
   #Create a new filegroup
   $fgcount
   $nfg = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Filegroup -argumentlist $database, $fg
   $nfg.Create()
	 
   #Define a DataFile object on the file group and set the FileName property. 
   $file = New-Object -TypeName Microsoft.SqlServer.Management.SMO.DataFile -argumentlist $nfg, $filename
	 
   #Make sure to have a directory created to hold the designated data file
   $file.FileName =$filepath
 
   #Call the Create method to create the data file on the instance of SQL Server. 
   $file.Create()
}

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

# iterate through tables in database
     foreach($tb in $database.Tables)
     {
        $tbname=$tb.Name
       # iterate through indexes in a database.
        foreach($ix in $tb.Indexes)
        {
          $ixname=$ix.Name
           
          #skip partitioned, XML, Clustered indexes
          #skip indexes which already have filegroup 
          #similar to the supplied one
          if($ix.IsPartitioned -ne "True" -and $ix.IsXMLIndex -ne "True" -and $ix.IsClustered -ne "True" -and $ix.FileGroup -ne $fg) 
          { 
            #get index script
            $indexscript=$ix.script()

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.

#replace the existing filegroup with the new one.
              if($str.LastIndexOf("[") -gt 0)      
                 {
                    $NewindexQuery=$str.Replace($str.SubString($str.LastIndexOf("["),($str.LastIndexOf("]") - $str.LastIndexOf("[")+1)),"[" + $fg + "]").Replace("DROP_EXISTING = OFF","DROP_EXISTING = ON")

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.

$qry="select 1 from sys.indexes where object_id=object_id('$tbname')
 and name='$ixname' and is_unique_constraint=1"                
# check if index is a constraint.   
 $IsConstraint=ExecuteQuery $inst $db $qry 1    
 if ($IsConstraint -eq 1 )
{ 
# Add drop constraint if index is a constraint.
 $NewindexQuery="Alter Table $tbname Drop Constraint $ixname"  + "`r`n" + $NewindexQuery
$NewindexQuery
} # if constraint ends

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.

if($NewindexQuery -ne "" -and $ix.IndexKeyType -ne "DriPrimaryKey")
{
   ExecuteQuery $inst $db $NewindexQuery 0 
   WriteToLog($NewindexQuery)
 }

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.

-- New filegroup = SECONDARY
SELECT  [TableName]=so.Name,
       [IndexName]=si.Name,
       [FileGroup]=fg.Name
FROM sys.objects so JOIN sys.indexes si
ON so.object_id=si.object_id
JOIN sys.filegroups fg on fg.data_space_id=si.data_space_id
WHERE so.type='U' and fg.Name='SECONDARY'

The complete code is available here http://sdrv.ms/XqzlsK

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