THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Scripting Index Maintenance with PowerShell

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
          elseif ($frval -gt 10 -and $pgcnt -gt 1000) {
            # Reorg the index if fragmentation over 10 percent
            Write-Host "Reorging " $dbname $tbname $ixname

            # A reorg doesn't update statistics, so do it manually

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.


Published Saturday, November 13, 2010 9:20 PM by AllenMWhite
Filed under: ,




Scott R. said:


Great script and very concise definition in PowerShell of the preferred index rebuild process.

A minor enhancement suggestion: Allow the three key threshold values (low fragmentation threshold = 10%, high fragmentation threshold = 30%, minimum # of pages before considering any fragmentation thresholds = 1,000 pages) to be specified in command line parameters to the PowerShell script (as is done for the DB instance name), and use the stated values by default if not given.  That way, most uses that prefer the defaults will get them without extra work, but those uses that need to override the defaults can easily do so (especially the fragmentation thresholds for reorg versus rebuild).

I have heard that there may be benefits in choosing more optimal threshold values for a given application (based on the DBA's better knowledge of that application and it's indexes, processing patterns, and business cycles), and that some companies actively use different threshold values for these reasons.  Lacking any extra knowledge of the application, the default threshold values serve as a decent baseline for the index rebuild process.

Again, thanks for this most useful contribution.  I enjoyed your PowerShell pre-con session at SQL PASS last week and discussing PowerShell issues with you.

Scott R.

November 14, 2010 11:18 AM

AllenMWhite said:

Thanks, Scott.  Your suggestions are good ones, but one of the reasons I do my presentations is so that the attendees/students can adapt the scripts to their needs, and use the methodology to create their own scripts.  With that in mind I'll leave your suggestions as an exercise to the reader. ;-)

November 14, 2010 5:37 PM

AaronStanley73 said:

Thanks for posting this Allen.  Your pre-con session was great and this follows perfectly with the examples you talked about.  

November 14, 2010 10:38 PM

Chad Miller said:


Not sure I understand why the SMO object model isn't working of you. This code works correctly and uses the object model...

$ix.EnumFragmentation() | foreach {

                           # If frag > 10 and < 30 and pages > 1000 , or choose reorg

                           if ($_.AverageFragmentation -ge  10 -and $_.AverageFragmentation -le 30  -and $_.Pages -ge 1000)

                           { $ix.Reorganize() }

                           # if frag > 30 and pages > 1000 or chosse rebuild

                           elseif ($_.AverageFragmentation -gt 30 -and $_.Pages -ge 1000)

                           { $ix.Rebuild() }


November 15, 2010 3:10 PM

AllenMWhite said:

Chad, DOH! In looking for the pages property I was looking at the Index object itself and never even considered looking at the DataTable returned by EnumFragmentation().  Thanks.

November 15, 2010 8:21 PM

Chad Miller said:

No problem.

BTW we do have a SQLMaint module as part of SQLPSX

November 16, 2010 9:53 AM

Kevin Rankin said:

Hi Allen,

I was wondering if you know whether SMO can be used to enumerate indexes on SQL 2000? I have installed the required SMO libraries on a test SQL 200O server and all seems to work ok except it only returns what appear to be system indexes. eg.




This after using your code as a basis to enumerate through each database, table etc.

Any ideas? Do you know of any limitations of SMO on SQL2000? From what I have read, it seems that it is compatible.



December 15, 2010 9:06 AM

AllenMWhite said:

Kevin, SMO supports SQL 2000 and up.

December 15, 2010 9:16 AM

Kevin Rankin said:

Thanks Allen, I figured it out. It does work, it's just me and my horrible use of the }'s in power shell which made the logic all messed up. I'm a newbie at PS and SMO but am really excited at what can be achieved with it. I also like the fact that you can write scripts for 2000, 2005, 2008 and 2008R2 all in one go. (with some tweaking).

December 15, 2010 10:06 AM

Kevin Rankin said:

Hi Allen,

Hope you had a great Christmas and New year!

I have stumbled into what I think is a legitimate issue with using SMO on SQL 2000. The EnumFragmentation() function makes use of the following underlying SQL statement:

DBCC SHOWCONTIG (' + QUOTENAME(object_name(@TableId), '''') + ',' + QUOTENAME(@IdxName, '''') + ') WITH TABLERESULTS, FAST

The problem is that the object_name() function returns only the table name and not the 'owner.table name' as is required for the DBCC SHOWCONTIG command to work. So this results in default dbo owned tables to work ok but nothing else and because the data is all fed into a cursor it breaks at the first occurrence of a non dbo owned table. I am not sure why they decided to use the object_name() function as the raw @TableId works just fine. Since the underlying code of the SMO EnumFragmentation() function can't be changed and it will be next to impossible to have the ownership of the tables changed (albeit preferable) I am scratching my head on how to get around this. It's frustrating as I was hoping to be able, through the use of SMO,  to use one standard script for SQL 2000 through 2008 R2.

Do you have any ideas?



January 5, 2011 5:54 AM

Ian R said:

Thanks Allen and thanks Chad. I had been messing around with EnumFragmentation() in my posh reindex script and had major problems with speed. The EnumFragmentation() command took miles longer than the actual index rebuild, so I ditched it and just rebuilt everything (fortunately they were small databases). Did you have this experience or can you explain why EnumFragmentation() should take so long?

February 15, 2011 6:11 PM

Kyle Neier said:

Hello Ian - I too found the Index.EnumFragmentation() method to be dreadfully slow on larger databases for 2005 and up.

After some research, it appears that SMO isn't sending any parameters to the dm_db_index_physical_stats, only limiting the results after the full database has been analyzed. Unfortunately, it seems that if you are looping over every index in the database, you are getting the entire database fragmentation information once for every index.

I opened the following connect in an attempt to get this resolved. Until then, I also posted a workaround there if you're using Powershell.

December 13, 2011 12:19 PM
New Comments to this post are disabled

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog


Privacy Statement