Managing Index Fragmentation with Powershell

Here’s a Powershell script that can be used to manage index fragmentation in SQL Server databases. The strategy I’ve used in the script is based on a recommendation from Pinal Dave (blog | twitter) in his article  Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script. Just set the $sqlserver and $database variables to something appropriate for your environment. Enjoy!

?View Code POWERSHELL
# Load SMO
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null;
 
# Set sql server and database name here
$sqlserver = "localhost\sql2005";
$database = "AdventureWorks";
 
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $sqlserver;
$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database");
$db = $srv.Databases[$database];
 
# Get table count
$table_count = $db.Tables.Count;
$i = 0;
 
# First script out the drops
foreach($table in $db.Tables)
{
	Write-Progress -Activity "Checking table $table" -PercentComplete (($i / $table_count) * 100) -Status "Processing indexes" -Id 1;
	$i++;
	foreach($index in $table.Indexes)
	{
		$index_name = $index.Name;
		Write-Progress -Activity "Checking table $table" -PercentComplete (($i / $table_count) * 100) -Status "Processing index $index_name" -Id 1;
		# Get the fragmentation stats
		$frag_stats = $index.EnumFragmentation();
 
		# Get the properties we need to work with the index
		$frag_stats | ForEach-Object {
						$Index_Name = $_.Index_Name;
						$Index_Type = $_.Index_Type;
						$Average_Fragmentation = $_.AverageFragmentation;
									};
		Write-Host -ForegroundColor Green "$Index_Type $Index_Name has a fragmentation percentage of $Average_Fragmentation";
 
		# Here we decide what to do based on the level on fragmentation
		if ($Average_Fragmentation -gt 40.00)
		{
			Write-Host -ForegroundColor Red "$Index_Name is more than 40% fragmented and will be rebuilt.";
			$index.Rebuild();
			Write-Host -ForegroundColor Green "$Index_Name has been rebuilt.";
		}
		elseif($Average_Fragmentation -ge 10.00 -and $Average_Fragmentation -le 40.00)
		{
			Write-Host -ForegroundColor Red "$Index_Name is between 10-40% fragmented and will be reorganized.";
			$index.Reorganize();
			Write-Host -ForegroundColor Green "$Index_Name has been reorganized.";
		}
		else
		{
			Write-Host -ForegroundColor Red "$Index_Name is healthy, with $Average_Fragmentation% fragmentation, and will be left alone.";
		}
 
	}
}
Write-Progress -Activity "Finished processing `"$database`" indexes." -PercentComplete 100 -Status "Done" -Id 1;
Start-Sleep -Seconds 2;

AdventureWork Index Management with Powershell


3 Comments

  1. ram says:

    Im looking for a script that rebuilds indexes in tables and views at same time in a single script….if possible could you publish it.

    any thanks,

  2. Rhys says:

    Hi Ram,

    I don’t run Enterprise Edition so there nothing I can try this out on. I would imagine it would be quite simple to do. The $db object will have an array of Views which I’m sure you could iterate over, checking if it’s an indexed view, and then rebuilding it.

    Cheers,

    Rhys

Leave a Reply