Enumerating Index Fragmentation with Powershell and SMO

I wish I could say that this was a simple task, but as I dove into SMO and some of its limitations, I soon discovered this would more difficult than I had imagined. I’m happy to share some background and some of the trials I had to overcome so that you might avoid having to take the same path.

Several months ago, I had a client that wanted to receive an index fragmentation report that showed the fragmentation of all indexes across their enterprise in one place. They planned to use this to audit the effectiveness of the maintenance procedures.

Given the flexibility of Powershell for outputting the report, I started there using SMO. I soon realized that there were some instances that were taking a very long time to return results. Unfortunately, in some of these cases, even the less than evil IS lock was creating issues as exclusive table lock escalation was being attempted and denied.

I was very excited to find that the Index class has an EnumFragmentation method. So, I decided that instead of getting the fragmentation an instance at a time, I could get it an index at a time. I could then set an SMO timeout and prevent the locks and IO from taking over the system completely while larger tables were interrogated.

Armed with the new found method, I wrote my loop and eventually had

$Index.EnumFragmentation()

I ran this on a couple of test servers and was confused when I had entire databases that had no information. Even small indexes were not reporting. However, other databases on the same instance had no issue. Not only did other databases have no issue, if a database had any results, it got all of the results. It was all or nothing.

After debugging a little, I soon realized that the 120 second timeout I had put in place was being hit for every index – regardless of size – within a particular database. Those indexes that were providing information were not timing out.

While this seemed odd, my first attempt was to increase the timeout and try again – maybe they were having IO issues the first run. Unfortunately, the second run had the same results. Every index in entire databases was timing out.

I wasn’t experiencing this issue locally, so I ran the code with with a SQL Trace running. Here’s what is run for an individual index:

exec sp_executesql N'
declare @database_id int
select @database_id = db_id()

SELECT
i.name AS [Index_Name],
CAST(i.index_id AS int) AS [Index_ID],
fi.index_depth AS [Depth],
fi.page_count AS [Pages],
fi.record_count AS [Rows],
fi.min_record_size_in_bytes AS [MinimumRecordSize],
fi.max_record_size_in_bytes AS [MaximumRecordSize],
fi.avg_record_size_in_bytes AS [AverageRecordSize],
fi.forwarded_record_count AS [ForwardedRecords],
fi.avg_page_space_used_in_percent AS [AveragePageDensity],
fi.index_type_desc AS [IndexType],
fi.partition_number AS [PartitionNumber],
fi.ghost_record_count AS [GhostRows],
fi.version_ghost_record_count AS [VersionGhostRows],
fi.avg_fragmentation_in_percent AS [AverageFragmentation]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tbl.object_id)
INNER JOIN sys.dm_db_index_physical_stats(@database_id, NULL, NULL, NULL, ''LIMITED'') AS fi ON fi.object_id=CAST(i.object_id AS int) AND fi.index_id=CAST(i.index_id AS int)
WHERE
(i.name=@_msparam_2)and((tbl.name=@_msparam_3 and SCHEMA_NAME(tbl.schema_id)=@_msparam_4))
ORDER BY
[Index_Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'IX_MyTestTable_1',@_msparam_3=N'MyTestTable',@_msparam_4=N'dbo'

I missed the problem at first, but eventually realized that this was pulling the index fragmentation for the entire database each time it was running! Here is the portion of the command extracted from the trace that is the crux of the issue:

sys.dm_db_index_physical_stats(@database_id, NULL, NULL, NULL, 'LIMITED')

The results coming back to the client were filtered as part of a query predicate, not being fed as inputs to the function. That explained why the entire database worth of indexes timed out – I was scanning the entire database every time I thought I was scanning an index!

That really bugged me. I thought I had come up with an elegant solution and the code behind the scenes made it impossible to use. One of the reasons I generally revert to SMO is that it generally works across different versions of SQL Server and is generally upgrade resistant. So, I tried my command against a SQL 2000 database. To my surprise, the same problem was not present. In the SQL 2000 branch of the code, they use DBCC SHOWCONTIG and limit it in the command – not in the query following.

Ah, but never fear – the flexibility of Powershell is about to shine through. While it is inconvenient, I decided to write my own T-SQL to gather the index fragmentation information. I can use the ExecuteWithResults method of the database object to get the same information I would receive from the EnumFragmentation method of the index without the full database scan penalty.

Below is a script that I use to gather index fragmentation metrics from an instance.

For SQL 2000 instances, it will use the standard EnumFragmentation method. However, for 2005 and above, it will use the SQL string that I initialize in the begin portion of the script.

Back in December, I filed a Connect to Microsoft – the only feedback received is that they are “investigating”. SMO and Powershell are great tools, but when something like this doesn’t work as well as it could, it should really be fixed. If you would like to see this fixed, give it some votes.

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter

param(
    [string]$ClientName = "AnAwesomeClient",
    [string]$ReportFileLocation = "$Env:UserProfile\IndexReport.csv",
    [string]$ConnectionString = ("Data Source=.\SQL2008R2;
        Initial Catalog=master;Integrated Security=SSPI;
        Application Name=PTI Index Fragmentation Report")
)

process
{
    $sqlConnection = New-Object -TypeName `
        System.Data.SqlClient.SqlConnection ($connectionString)
    $srvConnection= New-Object -TypeName `
        Microsoft.SqlServer.Management.Common.ServerConnection ($sqlConnection)
    $srv = New-Object -TypeName `
        Microsoft.SqlServer.Management.SMO.Server ($srvConnection)
    
    #Timeout in Seconds
    $srv.ConnectionContext.StatementTimeout = 120
    
    $IndexInfos = @()
    
    foreach($database in $srv.Databases)
    {
        if(("master", "model", "tempdb") -notcontains $database.Name)
        {
            
            foreach($table in $database.Tables)
            {
                foreach($index in $table.Indexes)
                {

                    if($srv.Version.Major -eq 8)
                    {
                        $IndexFragInfo = ($index.EnumFragmentation()).Rows[0];
                    }
                    elseif($srv.Version.Major -ge 9)
                    {
                        $IndexFragInfo = ($database.ExecuteWithResults(
                            ($IndexFragQuery -f $database.ID, $table.ID, 
                                $index.ID))).Tables[0].Rows[0]
                    
                    }
                    
                    $IndexInfo = New-IndexInfo
                    
                    $IndexInfo.Client = $ClientName
                    $IndexInfo.Instance = $srv.Name
                    $IndexInfo.DatabaseName = $database.Name
                    $IndexInfo.TableName = $table.Name
                    $IndexInfo.IndexName = $index.Name
                    $IndexInfo.IndexColumns = (
                        [string]::join(",", ($index.IndexedColumns | 
                        sort-object @{Expression={$_.ID}; Ascending=$false} | 
                            %{$_.Name})))
                    $IndexInfo.IsClustered = $index.IsClustered
                    $IndexInfo.IsUnique = $index.IsUnique
                    $IndexInfo.SpaceUsed = $index.SpaceUsed
                    $IndexInfo.Pages = $IndexFragInfo.Pages
                    $IndexInfo.FillFactor = $index.FillFactor
                    $IndexInfo.AverageFragmentation = (
                        $IndexFragInfo.AverageFragmentation)
                    
                    $IndexInfos += $IndexInfo
                }
            }
        }
    }

    $IndexInfos | Export-Csv -NoTypeInformation -Path $ReportFileLocation

}



begin
{
    #Load SMO
    Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, 
        Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
    Add-Type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, 
        Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" `
        -ErrorAction Stop
    
    Function New-IndexInfo
    {
        $IndexInfo = "" | SELECT Client, Instance, DatabaseName, TableName, `
            IndexName, IndexColumns, IsClustered, IsUnique, SpaceUsed, Pages, `
            Rows, FillFactor, AverageFragmentation
            
        $IndexInfo
    }
    
    $IndexFragQuery = "SELECT
        index_depth AS [Depth],
        page_count AS [Pages],
        record_count AS [Rows],
        min_record_size_in_bytes AS [MinimumRecordSize],
        max_record_size_in_bytes AS [MaximumRecordSize],
        avg_record_size_in_bytes AS [AverageRecordSize],
        forwarded_record_count AS [ForwardedRecords],
        avg_page_space_used_in_percent AS [AveragePageDensity],
        index_type_desc AS [IndexType],
        partition_number AS [PartitionNumber],
        ghost_record_count AS [GhostRows],
        version_ghost_record_count AS [VersionGhostRows],
        avg_fragmentation_in_percent AS [AverageFragmentation]
    FROM
        sys.dm_db_index_physical_stats({0}, {1}, {2}, NULL, 'LIMITED')"
}