Performance

Invoke-sqmUpdateStatistics

Updates SQL Server statistics across one or more databases with configurable scan options. 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 -OnlyModified && ModCounter=0? Yes → skip ShouldProcess? UPDATE STATISTICS [schema].[table]([stat]) WITH FULLSCAN / SAMPLE n% Status=Failed DONE
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: *.
-SamplePercentIntOptionalSample percentage (0 = FULLSCAN). Default: 0.
-OnlyModifiedBoolOptionalOnly update statistics with modification_counter > 0. Default: $true.
-IndexBoolOptionalInclude index-backed statistics. Default: $true.
-EnableExceptionSwitchOptionalThrow exceptions immediately instead of logging and continuing.

Return Value

Returns one PSCustomObject per updated (or failed) statistic: SqlInstance, Database, Table, Statistic, Status (Success / Failed), Message.

Examples

Example 1 — Update all modified statistics in one database

Invoke-sqmUpdateStatistics -Database 'SalesDB' -SamplePercent 10

Example 2 — FULLSCAN 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

Invoke-sqmUpdateStatistics -Database 'SalesDB' -WhatIf