One of the things that Maintenance Plans do poorly is index maintenance. Unless you create your own scripts, the process simply rebuilds all the indexes in all the databases. This is one of the reasons why I really dislike maintenance plans. (My pre-con attendees learned this very quickly.)
So, I built a script to rebuild or reorg indexes based on their fragmentation levels, and it works fairly well. It rebuilds indexes that are over 30 percent fragmented, and reorgs those (followed by an update statistices) on those that above 10 percent but not over 30 percent fragmented.
Because in general I like using SMO, and iterating through all the indexes is fairly straightforward in SMO, it makes perfect sense to walk through the SMO Databases collection for an instance, then through each database's Tables collection, then into each table's Indexes collection to get to each index. The Index object in SMO provides a method called EnumFragmentation(), which returns a percent value in a datatable.
My script takes advantage of this, and then uses either the Index object's Rebuild() method or its Reorganize() method (followed by the UpdateStatistics() method, of course) based on the logic I described earlier.
The problem comes in when I realized that when there are a relatively small number of pages used by the index, rebuilding or reorganizing the index really doesn't provide any useful benefit. Paul Randal (blog|twitter) suggested a rough estimate of 1000 pages as the sweet spot below which there's no real tangible benefit to doing the index maintenance.
So, I set out to find the SMO Index Object Property which provides the page count for the index. There is no property or function that provides this. This of course was rather upsetting to me, as I'm already connected to the instance in SMO, and I just can't get that value in my already existing connection.
There's really no way around it, so instead of some nice readable code like this:
# Get the Fragmentation information (returns DataTable)
$frag = $ix.EnumFragmentation()
We instead have to make some sort of second connection to SQL Server, execute the sys.dm_db_index_physical_stats DMO so we can evaluate both the fragmentation value and the page count. I choose to use the Invoke-SQLCMD cmdlet to do this, like this:
# Get the Fragmentation and page count information
$q = @"
select avg_fragmentation_in_percent, page_count
from sys.dm_db_index_physical_stats($dbid, $tbid, $ixid, NULL, NULL)
"@
$res = invoke-sqlcmd -ServerInstance $inst -Database $dbname -Query $q
$frval = $res.avg_fragmentation_in_percent
$pgcnt = $res.page_count
Now, I'd also added code before these lines to grab the database, table and index ID values as strings:
$dbid = [string]$db.ID
$tbid = [string]$tb.ID
$ixid = [string]$ix.ID
And then it's easy to add the code to test the page count as well as the fragmentation value:
# Check the fragmentation percent
if ($frval -gt 30 -and $pgcnt -gt 1000) {
# Rebuild the index if fragmentation over 30 percent
Write-Host "Rebuilding " $dbname $tbname $ixname
$ix.Rebuild()
}
elseif ($frval -gt 10 -and $pgcnt -gt 1000) {
# Reorg the index if fragmentation over 10 percent
Write-Host "Reorging " $dbname $tbname $ixname
$ix.Reorganize()
# A reorg doesn't update statistics, so do it manually
$ix.UpdateStatistics()
}
The entire script is attached, and you can download it, TEST IT, and use it as you see fit.
I really hate having to work around holes in the object model like this, and I can see why many people decide to find other ways to get things done.
One last note: it doesn't matter what order you use to update the indexes. I asked Kimberly Tripp (blog|twitter) personally about it, and she assured me that it doesn't matter at all.
Allen