· Uwe Janke
Commands / Get-sqmStatisticsHealth

Get-sqmStatisticsHealth

Performance sqmSQLTool v1.9.196+ · Diagnostics
Read-only report on the state of all statistics, and which of them Invoke-sqmUpdateStatistics would update. The same rule decides NeedsUpdate: changes since the last update reach MIN(SQRT(1000 x rows), rows x 10 %), or at least one change and older than 30 days.

Per statistic: rows, sampled rows and sample percent, histogram steps, modifications, threshold, last update and age, NORECOMPUTE, persisted sample, filter, and a status:

• Ok, Stale (update due), NeverUpdated (no data, skipped by Invoke-sqmUpdateStatistics), NoRecompute (changes, but SQL Server never updates it automatically)

Every row also carries the database settings AUTO_UPDATE_STATISTICS, its ASYNC variant and AUTO_CREATE_STATISTICS, so a database with auto update off shows up directly.

Execution Flow

START Get-DbaDatabase (exclude system, tempdb) Apply -Database wildcard filter via -like Per database: read the database settings AUTO_UPDATE_STATISTICS, ASYNC, AUTO_CREATE_STATISTICS Per database: read the statistics sys.stats + sys.dm_db_stats_properties (rows, sample, steps, changes, age) Decide NeedsUpdate and Status Same helper as Invoke-sqmUpdateStatistics: threshold and age rule Ok / Stale / NeverUpdated / NoRecompute -Summary: group by database Statistics, counts per status, oldest update, most changes, settings Unless -OutputPath is empty: write CSV + HTML report Open the HTML report unless -NoOpen Return [PSCustomObject[]] one row per statistic, 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.
-StatisticsstringOptional*Statistic name or wildcard pattern.
-ModificationThresholdDynamic | AnyOptionalDynamicDynamic: MIN(SQRT(1000 x rows), rows x percent / 100). Any: at least one change.
-ModificationPercentintOptional10Percentage part of the dynamic threshold.
-MaxAgeDaysintOptional30With at least one change and older than this, a statistic counts as stale. 0 = off.
-IndexboolOptional$true$false: column statistics only.
-OnlyProblemsswitchOptional$falseOnly Stale, NeverUpdated and NoRecompute statistics with changes.
-SummaryswitchOptional$falseOne row per database instead of one row per statistic.
-OutputPathstringOptional<OutputPath config>\StatisticsHealthDirectory 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 statistics run update?
Get-sqmStatisticsHealth -SqlInstance 'SQL01' -OnlyProblems -OutputPath '' |
    Format-Table Database, Table, Statistic, Rows, Modifications, AgeDays, Reason
One line per database, including the auto-update settings
Get-sqmStatisticsHealth -SqlInstance 'SQL01' -Summary -OutputPath ''
← Back
Command Index
Next
Invoke-sqmUpdateStatistics