Here’s a quick script to retrieve index fragmentation via powershell. Two actually. This uses sqlpsx for both. Do not run these during the day on your production box (you shouldn’t run them at all unless you test them on a test box first to gauge impact). I like the first one because you can strip off the –dbname parameter if you like and collect all the indexes from all your databases. This uses LIMITED option (::fast), but it’s still pretty heavy-duty. Use at your own risk.
First, here is the powershell-object oriented variant:
Import-Module SqlServer
$outputs = @();
$srv = 'ServerName'
Get-SqlDatabase -sqlserver $srv -dbname 'dbName' | %{
$dbName = $_.Name
Get-SqlTable -Database $_ | where{$_.IsSystemObject -ne $true} | %{
$tableName = $_.Name
foreach($index in $_.Indexes)
{
$index.EnumFragmentation([Microsoft.SqlServer.Management.Smo.FragmentationOption]::Fast) | %{
$output = New-Object -TypeName PSObject -Property @{
DatabaseName = $dbName
TableName = $tableName
IndexName = $index.Name
IndexType = $_.IndexType
AvgFragmentationInPercent = $_.AverageFragmentation
SizeMB = [Math]::Truncate(($_.pages*8)/1024)
FillFactor = $index.FillFactor
}
$outputs += $output
}
}
}
}
$outputs | SELECT DatabaseName, TableName, IndexName, IndexType, AvgFragmentationInPercent, SizeMB, FillFactor | Format-Table -AutoSize
Nice and clean. Unfortunately, if you run this while doing a trace, you’ll see that it’s doing much more work than necessary, but it’s very concise and easy to see/debug.
First, it executes this:
exec sp_executesql N'SELECT
i.name AS [Name]
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)
WHERE
(tbl.name=@_msparam_2 and SCHEMA_NAME(tbl.schema_id)=@_msparam_3)
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'TableName',@_msparam_3=N'SchemaName'
Then this:
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'IndexName',@_msparam_3=N'TableName',@_msparam_4=N'SchemaName'
Essentially, one hit to the database per table, and one per index on said table.
You could use this powershell to do one hit to the database to get all the tables, then only suffer one hit per index:
cls
Import-Module SqlServer
$outputs = @();
$srv = 'ServerName'
Get-SqlDatabase -sqlserver $srv -dbname 'DatabaseName' | %{
$dbName = $_.Name
$idxQuery = "
SELECT
DB_ID() AS DBID,
i.object_id,
OBJECT_NAME(i.object_id) AS TableName,
i.name,
i.index_id,
SUM(dps.used_page_count) * 8
FROM
sys.dm_db_partition_stats dps
INNER JOIN sys.indexes i ON dps.[object_id] = i.[object_id] AND dps.index_id = i.index_id
GROUP BY i.name, i.index_id, i.object_id
"
$indexes = Get-SqlData -sqlserver $srv -dbname $dbName -qry $idxQuery
foreach($row in $indexes)
{
$objectID = $row.object_id
$indexID = $row.index_id
$databaseID = $row.DBID
$idxFragQuery = "
SELECT
i.name,
i.type,
dmf.page_count*8 AS 'size',
ROUND(dmf.avg_fragmentation_in_percent,0) AS avg_fragmentation_in_percent,
i.fill_factor
FROM
sys.dm_db_index_physical_stats($databaseID, $objectID, $indexID, null, 'limited') dmf
INNER JOIN sys.indexes i ON ((i.object_id = dmf.object_id) AND (i.index_id = dmf.index_id))
WHERE
(dmf.alloc_unit_type_desc = 'IN_ROW_DATA')
"
$idxFragRows = Get-SqlData -sqlserver $srv -dbname $dbName -qry $idxFragQuery
$output = New-Object -TypeName PSObject -Property @{
DatabaseName = $dbName
TableName = $idxFragRows.TableName
IndexType = $idxFragRows.type
Size = $idxFragRows.size
AvgFragmentationInPercent = $idxFragRows.avg_fragmentation_in_percent
FillFactor = $idxFragRows.fill_factor
}
$outputs += $output
}
}
$outputs | Format-Table -AutoSize
But, it’s not nearly as clean. I wouldn’t run either of these on a production database during the day, but my preference would be to suffer the performance for the cleaner code, unless you have a gigantic number of tables in your database. YMMV. Use at your own risk.
Sql Server Powershell 