Rebuild SQL Server index with Powershell

  • Article

let continue to explore what we can achieve with powershell for SQL Server management.

The goal of this post is to rebuild fragmented index with power shell.

To achieve that we need to use the invoke-sqlcmd cmdlet.

try to use this command, if it doesn't work, be sure to have all the system module loaded, if not, run the importSystemModules cmdlet.

the query that we are going to use to know if indexes are fragmented or not is :

Select i.name as index_name,
object_name(s.object_id) as table_name,
s.avg_fragmentation_in_percent,
s.page_count
from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) s,
sys.indexes i
where i.object_id = s.object_id
and i.index_id = s.index_id
and i.name is not null

to run this query from powershell, just put the query in a variable ($indexList) and use the invoke-sqlcmd cmdlet like :

 invoke-sqlcmd -ServerInstance "myServer\myInstance" -database "myDB" -query $indexlist

you can put the result of this query in a powershell variable :

$listObject = invoke-sqlcmd -ServerInstance "opieri02\R2" -database "AdventureworksDW2008R2" -query $indexlist

after you just have to test if an index is fragmented or not (here, we consider an index as fragmented if it have more than 1000 pages and if the fragmentation is more than 10%)

to achieve this test we are going to use smo object.

1) load the smo assemby :
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

2) connect to your instance :
$server = New-Object('Microsoft.SqlServer.Management.Smo.Server') myServer\myInstance

3) for each index that you have retreive, instanciate an smo index :
$index = $Server.databases["myDB"].Tables[$i.table_name].Indexes[$i.index_name]

 The complete script is :

$indexlist = "
Select i.name as index_name, object_name(s.object_id) as table_name,
s.avg_fragmentation_in_percent, s.page_count from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) s,
sys.indexes i
where i.object_id = s.object_id and i.index_id = s.index_id and i.name is not null"

$listObject = invoke-sqlcmd -ServerInstance "myServer\myInstance" -database "myDB" -query $indexlist

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$server = New-Object('Microsoft.SqlServer.Management.Smo.Server') myServer\myInstance

foreach ($i in $listObject)
{
$index = $Server.databases["myDB"].Tables[$i.table_name].Indexes[$i.index_name]
if ($i.avg_fragmentation_in_percent -gt 10 -and $i.page_count -gt 1000)
{
Write-Host -BackgroundColor Red "Table : " $i.table_name " Index :" $i.index_name " is Fragmented = > Rebuild"
$index.Rebuild()
}
else
{
Write-Host "Table : " $i.table_name " Index :" $i.index_name " is not Fragmented"
}

}

 

Enjoy !!!