Execution Flow
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
| Parameter | Type | Required | Description |
|---|---|---|---|
| -SqlInstance | String | Optional | Target SQL Server instance. Default: $env:COMPUTERNAME. |
| -SqlCredential | PSCredential | Optional | SQL authentication credentials. |
| -Database | String | Optional | Database name or wildcard (e.g. Sales*). System databases excluded unless named explicitly. Default: *. |
| -Table | String | Optional | Table name or wildcard pattern. Default: *. |
| -Statistics | String | Optional | Statistic name or wildcard pattern. Default: *. |
| -SamplePercent | Int | Optional | Fixed sample for all statistics (0 = FULLSCAN, 1-100 = SAMPLE n PERCENT). Without it the sample is chosen per statistic. |
| -FullScanRowLimit | Long | Optional | Adaptive sample: FULLSCAN up to this many rows, above a sample of about this many rows (min 1 %). Default: 1000000. |
| -OnlyModified | Bool | Optional | Only update statistics that changed since the last update. $false = all matching statistics. Default: $true. |
| -ModificationThreshold | String | Optional | Dynamic: MIN(SQRT(1000 x rows), rows x ModificationPercent). Any: at least one change. Default: Dynamic. |
| -ModificationPercent | Int | Optional | Percentage part of the dynamic threshold (small tables). Default: 10. |
| -MaxAgeDays | Int | Optional | Changed statistics older than this are updated below the threshold too (ascending keys). 0 = off. Default: 30. |
| -Index | Bool | Optional | Include index-backed statistics. Default: $true. |
| -TimeLimitMinutes | Int | Optional | Do not start further updates after this many minutes; the rest is returned as NotStarted. 0 = no limit. Default: 0. |
| -Quiet | Switch | Optional | Suppress 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. |
| -EnableException | Switch | Optional | Throw 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 10Example 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 $falseExample 4, Force update regardless of modification counter
Invoke-sqmUpdateStatistics -Database 'Inventory' -OnlyModified $false -SamplePercent 20Example 5, WhatIf to preview affected statistics with the chosen sample
Invoke-sqmUpdateStatistics -Database 'SalesDB' -WhatIf | Format-Table Table, Statistic, Rows, Modifications, Option, ReasonExample 6, Previous behaviour: every changed statistic WITH FULLSCAN
Invoke-sqmUpdateStatistics -Database 'SalesDB' -SamplePercent 0 -ModificationThreshold AnyExample 7, Maintenance window of 60 minutes
Invoke-sqmUpdateStatistics -SqlInstance "SQL01" -TimeLimitMinutes 60