Get-sqmMissingIndexes Performance

Queries the SQL Server missing index DMVs for high-impact index recommendations, computes an ImpactScore, generates ready-to-run CREATE INDEX statements, and exports CSV and HTML reports. Excludes system databases automatically.

Execution Flow

Synopsis

Reads the three missing-index DMVs (dm_db_missing_index_details, dm_db_missing_index_groups, dm_db_missing_index_group_stats) and ranks results by ImpactScore. A private helper New-sqmCreateIndexStatement generates a CREATE NONCLUSTERED INDEX statement for each recommendation, including INCLUDE columns and a date-stamped name. The CSV is also copied to the central path via Copy-sqmToCentralPath. The backward-compatible alias Build-sqmCreateIndexStatement maps to the same helper.

Requires dbatools. Index recommendations survive SQL Server restarts only until the DMV is cleared. ImpactScore is an estimate — always validate recommendations on a test system before applying. System databases are excluded in the SQL WHERE clause.

ImpactScore Formula

# Impact = estimated cumulative query cost savings
ImpactScore = ROUND(
    (user_seeks + user_scans)
    * avg_total_user_cost
    * (avg_user_impact / 100.0),
2)

Syntax

Get-sqmMissingIndexes
    [-SqlInstance <String>]
    [-SqlCredential <PSCredential>]
    [-Database <String[]>]         # wildcards; PS-side filter
    [-MinImpactScore <Double>]     # default 10
    [-MinSeeks <Int>]              # default 50
    [-Top <Int>]                    # default 50
    [-OutputPath <String>]
    [-EnableException]

Parameters

Parameter	Type	Default	Description
-SqlInstance	String	$env:COMPUTERNAME	SQL Server instance to query.
-SqlCredential	PSCredential	—	SQL connection credential.
-Database	String[]	all user DBs	Wildcard filter applied after query. Example: `@('Sales*','HR')`.
-MinImpactScore	Double	10	Minimum computed ImpactScore to include a row.
-MinSeeks	Int	50	Minimum combined user_seeks + user_scans count.
-Top	Int	50	Maximum rows to return after scoring and filtering.
-OutputPath	String	Get-sqmConfig 'OutputPath'	Directory for CSV and HTML report files.
-EnableException	Switch	false	Throw terminating exceptions.

Return Value

Property	Description
DatabaseName	Database containing the table.
TableName	Schema-qualified table name.
EqualityColumns	Columns referenced in equality predicates.
InequalityColumns	Columns referenced in range predicates.
IncludedColumns	Columns needed for covering the query (SELECT list).
ImpactScore	Computed impact estimate (higher = more beneficial).
UserSeeks / UserScans	Query execution counts that triggered this recommendation.
CreateIndexStatement	Ready-to-run CREATE NONCLUSTERED INDEX T-SQL.

Examples

Example 1 — Top 20 high-impact indexes, local instance

Get-sqmMissingIndexes -Top 20

Example 2 — Filter to specific database, higher thresholds

Get-sqmMissingIndexes -SqlInstance "SQL01" -Database "Sales" -MinImpactScore 100 -MinSeeks 200

Example 3 — Export CREATE INDEX scripts to file

Get-sqmMissingIndexes | Select-Object CreateIndexStatement | Out-File "create_indexes.sql"

⬆ Index

All Commands