Get-sqmIndexUsageReport
PerformancesqmSQLTool v1.9.169+ · New🔓 VIEW SERVER STATE + database access
Get-sqmMissingIndexes (what is missing) and Get-sqmIndexFragmentation (state of what exists) with the question: which existing indexes cost more than they bring?Unused: nonclustered, non-unique index without a single read since the last restart, but maintained on every write. Unique indexes and primary keys are never reported, they enforce rules.
Duplicate: same key columns, order, direction and filter as another index on the table. The clustered, primary key or unique index is kept; differing INCLUDE columns are named.
Overlapping: the key columns are the leading part of a wider index. The detail says whether the wider index already covers the INCLUDE columns.
Heap, Disabled and Hypothetical (Tuning Advisor leftovers) complete the picture.
The function never changes an index. DROP suggestions are written to
_DropCandidates.sql, every line commented out. Usage counters start at zero after each restart or failover: with less than -MinUptimeDays of uptime it warns and marks Unused as unreliable. An index used only by the month-end run looks unused for weeks, so check with the application owner first.Parameters
| Parameter | Type | Required | Default | Notes |
|---|---|---|---|---|
| -SqlInstance | string | Optional | $env:COMPUTERNAME | Target SQL Server instance. |
| -SqlCredential | PSCredential | Optional | , | SQL or Windows credential. |
| -Database | string[] | Optional | all user DBs | Database names, wildcards allowed. ReportServer, SSISDB and distribution only when named explicitly. |
| -Finding | string[] | Optional | all | Unused, Duplicate, Overlapping, Heap, Disabled, Hypothetical. |
| -MinUptimeDays | int | Optional | 7 | Below this uptime Unused is flagged as unreliable and a warning is written. |
| -MinSizeMB | double | Optional | 0 | Ignore smaller indexes and heaps. |
| -OutputPath | string | Optional | <config>\IndexUsageReport | Directory for CSV, HTML and the commented DROP script. |
| -NoReport | switch | Switch | $false | Do not write CSV/HTML, only return objects. |
| -NoOpen | switch | Switch | $false | Do not open the HTML report after creation. |
| -EnableException | switch | Switch | $false | Throw exceptions immediately instead of Write-Error. |
Execution Flow
Return Value
PSCustomObject per finding: DatabaseName, SchemaName, TableName, IndexName, IndexType, Finding, Detail, RelatedIndex, KeyColumns, IncludeColumns, Filter, Reads, Writes, LastUserRead, Rows, SizeMB, UptimeDays, DropStatement.
Examples
All findings in all user databases
Get-sqmIndexUsageReport -SqlInstance "SQL01"
Only duplicates and overlaps in the shop databases
Get-sqmIndexUsageReport -SqlInstance "SQL01" -Database "Shop*" -Finding Duplicate, Overlapping
Large unused indexes as a list
Get-sqmIndexUsageReport -SqlInstance "SQL01" -Finding Unused -MinSizeMB 500 -NoReport |
Sort-Object SizeMB -Descending | Format-Table DatabaseName, TableName, IndexName, SizeMB, Writes