· Uwe Janke
Commands / Invoke-sqmUpdateStatistics
Performance

Invoke-sqmUpdateStatistics

Updates SQL Server statistics with adaptive defaults (since v1.9.178.0): only where enough rows changed and with a sample that fits the table. Supports wildcard filters for databases, tables, and statistics. Uses server-side filtering via sys.stats and sys.dm_db_stats_properties before issuing any UPDATE STATISTICS commands.

Module: sqmSQLTool
Requires: dbatools
ShouldProcess: Yes
Output: PSCustomObject per statistic

Execution Flow

START dbatools available? No throw error Yes Get-DbaDatabase (wildcard -Database, ExcludeSystem) foreach database sys.stats JOIN sys.objects JOIN sys.schemas OUTER APPLY sys.dm_db_stats_properties → ModCounter foreach statistic !-Index && IsIndexStat? Yes → skip Below threshold and age? Yes → skip ShouldProcess? FULLSCAN up to 1M rows, else SAMPLE ~1M rows (min 1 %), keep NORECOMPUTE Status=Failed DONE
Defaults. A statistic is updated when modification_counter >= MIN(SQRT(1000 x rows), rows x 10 %) (100 M rows: from 316,228 changes, 0.3 %), or when it has changes and is older than 30 days. Sample: FULLSCAN up to 1,000,000 rows, above that a sample of about that many rows, at least 1 % (10 M rows: 10 %, 100 M rows: 1 %). A persisted sample rate (PERSIST_SAMPLE_PERCENT) and NORECOMPUTE are kept. Read-only databases, AG secondaries and temporary statistics are skipped. The previous behaviour (every changed statistic WITH FULLSCAN) is -SamplePercent 0 -ModificationThreshold Any.
Statistics are determined server-side before any updates, the Table and Statistics parameters use SQL LIKE patterns (PowerShell wildcards */? are converted to %/_). System databases are excluded unless named explicitly. Requires sysadmin or db_owner on targets.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalTarget SQL Server instance. Default: $env:COMPUTERNAME.
-SqlCredentialPSCredentialOptionalSQL authentication credentials.
-DatabaseStringOptionalDatabase name or wildcard (e.g. Sales*). System databases excluded unless named explicitly. Default: *.
-TableStringOptionalTable name or wildcard pattern. Default: *.
-StatisticsStringOptionalStatistic name or wildcard pattern. Default: *.
-SamplePercentIntOptionalFixed sample for all statistics (0 = FULLSCAN, 1-100 = SAMPLE n PERCENT). Without it the sample is chosen per statistic.
-FullScanRowLimitLongOptionalAdaptive sample: FULLSCAN up to this many rows, above a sample of about this many rows (min 1 %). Default: 1000000.
-OnlyModifiedBoolOptionalOnly update statistics that changed since the last update. $false = all matching statistics. Default: $true.
-ModificationThresholdStringOptionalDynamic: MIN(SQRT(1000 x rows), rows x ModificationPercent). Any: at least one change. Default: Dynamic.
-ModificationPercentIntOptionalPercentage part of the dynamic threshold (small tables). Default: 10.
-MaxAgeDaysIntOptionalChanged statistics older than this are updated below the threshold too (ascending keys). 0 = off. Default: 30.
-IndexBoolOptionalInclude index-backed statistics. Default: $true.
-TimeLimitMinutesIntOptionalDo not start further updates after this many minutes; the rest is returned as NotStarted. 0 = no limit. Default: 0.
-QuietSwitchOptionalSuppress the progress output (console lines, progress bar with remaining time). Without it the function reports per database, one line per statistic with the remaining time estimate, and a summary. Results and log are unchanged.
-EnableExceptionSwitchOptionalThrow exceptions immediately instead of logging and continuing.

Return Value

Returns one PSCustomObject per updated (or failed) statistic: SqlInstance, Database, Table, Statistic, Rows, Modifications, LastUpdated, Option (e.g. FULLSCAN, SAMPLE 17 PERCENT, NORECOMPUTE), Reason (Threshold / Age / Modified / All), Status (Success / Failed / WhatIf / NotStarted), Duration_s, Message.

Examples

Example 1, Fixed 10 % sample for all changed statistics above the threshold

Invoke-sqmUpdateStatistics -Database 'SalesDB' -SamplePercent 10

Example 2, Adaptive defaults on all user databases

Invoke-sqmUpdateStatistics -SqlInstance "SQL01"

Example 3, Only column statistics (no index stats), all modified

Invoke-sqmUpdateStatistics -Database 'Sales*' -Index $false

Example 4, Force update regardless of modification counter

Invoke-sqmUpdateStatistics -Database 'Inventory' -OnlyModified $false -SamplePercent 20

Example 5, WhatIf to preview affected statistics with the chosen sample

Invoke-sqmUpdateStatistics -Database 'SalesDB' -WhatIf | Format-Table Table, Statistic, Rows, Modifications, Option, Reason

Example 6, Previous behaviour: every changed statistic WITH FULLSCAN

Invoke-sqmUpdateStatistics -Database 'SalesDB' -SamplePercent 0 -ModificationThreshold Any

Example 7, Maintenance window of 60 minutes

Invoke-sqmUpdateStatistics -SqlInstance "SQL01" -TimeLimitMinutes 60