Invoke-sqmHealthReport
MonitoringsqmSQLTool v1.9.170+ · New🔓 VIEW SERVER STATE (sysadmin covers every section)
HealthReport_<Instance>_<Timestamp>\index.html starts with a findings list sorted by severity (Critical, Warning, Info), followed by a compact table per section. The full detail reports of the individual functions land in Details\ and are linked from each section, together with Findings.csv, optional .sqlplan files and a ZIP for the ticket. The idea follows PSBlitz; it is built from sqmSQLTool's own functions, and nothing is installed on the instance.Sections: Instance (facts + best-practice checks), Waits, Activity (running sessions, blocking), OpenTransactions (user transactions open for minutes, with log usage;
sleeping and older than 10 minutes is Critical), TempDb, Databases (CHECKDB, backups, VLFs), Backups (backup control: thresholds, sqm_BackupExclude, databases created after the last backup run), PlanCache, Indexes (usage + missing), FileLatency, Deadlocks, Security (sysadmins), Disk.Every section runs on its own: if one fails because of a missing permission or an old version, the report says so in that section and the others still run. Only an unreachable instance stops the run.
-SampleSeconds measures waits and file latency live in the same time window; -InDepth adds all seven plan cache metrics and a 30-second window.Parameters
| Parameter | Type | Required | Default | Notes |
|---|---|---|---|---|
| -SqlInstance | string | Optional | $env:COMPUTERNAME | Target SQL Server instance. |
| -SqlCredential | PSCredential | Optional | , | SQL or Windows credential. |
| -Section | string[] | Optional | all | Instance, Waits, Activity, OpenTransactions, TempDb, Databases, Backups, PlanCache, Indexes, FileLatency, Deadlocks, Security, Disk. |
| -SkipSection | string[] | Optional | , | Sections to leave out, e.g. Indexes on an instance with thousands of databases. |
| -Database | string[] | Optional | all | Restricts PlanCache and Indexes to these databases (wildcards). |
| -SampleSeconds | int (0-600) | Optional | 0 | 0 = cumulative; otherwise waits and file latency measured live in the same window. |
| -InDepth | switch | Switch | $false | All plan cache metrics and, without -SampleSeconds, a 30 s window. |
| -ExportPlans | switch | Switch | $false | Plan cache plans as .sqlplan to Details\Plans. |
| -DeadlockDays | int | Optional | 15 | Days of deadlock history (ring buffer and .xel). |
| -OpenTranMinutes | int | Optional | 1 | Minimum age of listed open transactions. |
| -Top | int | Optional | 10 | Rows per section table and statements per plan cache metric. |
| -OutputPath | string | Optional | <config>\HealthReport | Parent directory of the report folder. |
| -Zip | switch | Switch | $false | Packs the report folder into a .zip next to it. |
| -NoOpen | switch | Switch | $false | Do not open the report after creation. |
| -EnableException | switch | Switch | $false | Throw if the instance cannot be reached. Section errors never throw. |
Execution Flow
Return Value
PSCustomObject: SqlInstance, Status (Critical, Warning or OK), Critical, Warning, Info, Findings (Severity, Section, Finding, Detail), Sections (Section, Status, Findings, DurationSec, DetailFiles, Error), DurationSec, Folder, HtmlFile, ZipFile.
Examples
Everything, cumulative values
Invoke-sqmHealthReport -SqlInstance "SQL01"
In depth, 30 s live window, plans exported, zipped for the ticket
Invoke-sqmHealthReport -SqlInstance "SQL01" -InDepth -ExportPlans -Zip
Quick look without the expensive parts
Invoke-sqmHealthReport -SqlInstance "SQL01" -SkipSection Indexes, Deadlocks, PlanCache
Only critical findings, e.g. in a scheduled run
(Invoke-sqmHealthReport -SqlInstance "SQL01" -NoOpen).Findings | Where-Object Severity -eq 'Critical'