· Uwe Janke
Commands / Get-sqmIndexHealth

Get-sqmIndexHealth

Performance sqmSQLTool v1.9.196+ · Diagnostics
Read-only report on the state of all indexes, and what Invoke-sqmIndexOptimize would do with each of them. Same thresholds, same rules, nothing is changed.

Per index (per partition for partitioned indexes): rows, pages, size, fragmentation (columnstore: share of deleted rows), fill factor, reads and writes since the last restart, and a status:

• Ok, Reorganize (from 5 %), Rebuild (from 30 %)
• Small (below -MinPageCount, fragmentation not measured), Disabled, NotMeasured (with -SkipFragmentation)

Fragmentation is read with sys.dm_db_index_physical_stats in LIMITED mode, only for indexes at or above -MinPageCount pages. Works on read-only databases and AG secondaries. For unused, duplicate and overlapping indexes see Get-sqmIndexUsageReport.

Execution Flow

START Get-DbaDatabase (exclude system, tempdb) Apply -Database wildcard filter via -like Per database: inventory from metadata sys.indexes + sys.dm_db_partition_stats + sys.dm_db_index_usage_stats Unless -SkipFragmentation: read fragmentation dm_db_index_physical_stats (LIMITED), only indexes ≥ -MinPageCount pages Columnstore: share of deleted rows in compressed row groups Rate each index like Invoke-sqmIndexOptimize Ok / Reorganize / Rebuild / Small / Disabled / NotMeasured -OnlyProblems keeps Reorganize and Rebuild only -Summary: group by database Indexes, size, counts per status, page-weighted average fragmentation Unless -OutputPath is empty: write CSV + HTML report Open the HTML report unless -NoOpen Return [PSCustomObject[]] one row per index/partition, or per database with -Summary

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringOptional$env:COMPUTERNAMETarget SQL Server instance.
-SqlCredentialPSCredentialOptional, Credentials for the connection.
-DatabasestringOptional* = all user DBsDatabase name or wildcard pattern. System databases only when named explicitly.
-TablestringOptional*Table name or wildcard pattern.
-FragmentationLowintOptional5From this fragmentation (%) the status is Reorganize.
-FragmentationHighintOptional30From this fragmentation (%) the status is Rebuild.
-MinPageCountintOptional1000Minimum index size in pages (8 KB) for the fragmentation measurement.
-ColumnstoreDeletedPercentintOptional20Columnstore: Reorganize from this share of deleted rows (%).
-SkipFragmentationswitchOptional$falseMetadata only (size, fill factor, usage). Fast, reads no index pages.
-OnlyProblemsswitchOptional$falseOnly indexes with status Reorganize or Rebuild.
-SummaryswitchOptional$falseOne row per database instead of one row per index.
-OutputPathstringOptional<OutputPath config>\IndexHealthDirectory for CSV + HTML report. Pass an empty string to skip the files.
-NoOpenswitchOptional$falseDo not open the HTML report after creation.
-EnableExceptionswitchOptional$falseThrow exceptions immediately instead of logging and continuing.

Examples

What would the next index maintenance do?
Get-sqmIndexHealth -SqlInstance 'SQL01' -OnlyProblems -OutputPath '' |
    Format-Table Database, Table, Index, PageCount, FragmentationPercent, Status
One line per database
Get-sqmIndexHealth -SqlInstance 'SQL01' -Summary -OutputPath ''
Fast overview without reading index pages
Get-sqmIndexHealth -Database 'SalesDB' -SkipFragmentation -OutputPath ''
← Back
Command Index
Next
Get-sqmStatisticsHealth