Get-sqmPlanCacheReport
PerformancesqmSQLTool v1.9.169+ · New🔓 VIEW SERVER STATE required
sys.dm_exec_query_stats once per metric you choose with -SortBy (CPU, Duration, Reads, Writes, Executions, MemoryGrant, Spills or All) and returns the top N of each list. A statement that is expensive in several ways is returned once, with all its ranks, for example CPU#1, Reads#4. Each row carries the share of the instance-wide total in percent, averages per execution and the findings read from the plan XML: ImplicitConversion, MissingIndex, Spill, KeyLookup, NoJoinPredicate and ParallelPlan.With
-ExportPlans every statement plan is saved as .sqlplan: open it in SSMS or hand it straight to Get-sqmPlanInsights for the detailed analysis. Nothing is installed on the instance; the idea follows sp_BlitzCache from Brent Ozar's First Responder Kit, built from the DMVs directly.Keep in mind: the plan cache only knows what is still cached. Restarts, failovers, memory pressure and
OPTION (RECOMPILE) remove statements. For history use Invoke-sqmQueryStore. MemoryGrant and Spills need SQL Server 2016 SP2 / 2017 or later and are skipped with a warning on older versions.Parameters
| Parameter | Type | Required | Default | Notes |
|---|---|---|---|---|
| -SqlInstance | string | Optional | $env:COMPUTERNAME | Target SQL Server instance. |
| -SqlCredential | PSCredential | Optional | , | SQL or Windows credential. |
| -Database | string[] | Optional | all | Only these databases (wildcards). Resolving the database for the whole cache costs extra CPU on very large plan caches. |
| -SortBy | string[] | Optional | CPU, Duration, Reads | CPU, Duration, Reads, Writes, Executions, MemoryGrant, Spills, All. Only values above 0 get a rank. |
| -Top | int (1-500) | Optional | 10 | Statements per metric. |
| -MinutesBack | int | Optional | 0 | Only statements executed within the last N minutes. 0 = whole cache. |
| -ExportPlans | switch | Switch | $false | Save each statement plan as .sqlplan to <OutputPath>\Plans; the path is in PlanFile. |
| -OutputPath | string | Optional | <config>\PlanCacheReport | Directory for CSV, HTML and plans. |
| -NoReport | switch | Switch | $false | Do not write CSV/HTML, only return objects. |
| -NoOpen | switch | Switch | $false | Do not open the HTML report after creation. |
| -EnableException | switch | Switch | $false | Throw exceptions immediately instead of Write-Error. |
Execution Flow
Return Value
PSCustomObject per statement: Ranks, DatabaseName, ObjectName (or (ad hoc)), StatementText, Executions, TotalCpuMs, AvgCpuMs, CpuPercent, TotalDurationMs, AvgDurationMs, DurationPercent, TotalReads, AvgReads, ReadsPercent, TotalWrites, TotalGrantKB, MaxGrantKB, TotalSpills, Findings, PlanCreated, LastExecution, QueryHash, PlanFile.
Examples
Default: top 10 by CPU, duration and reads
Get-sqmPlanCacheReport -SqlInstance "SQL01"
All metrics, top 5 each, with plan export
Get-sqmPlanCacheReport -SqlInstance "SQL01" -SortBy All -Top 5 -ExportPlans
What hurt in the last hour in the shop databases
Get-sqmPlanCacheReport -SqlInstance "SQL01" -Database "Shop*" -MinutesBack 60 -SortBy CPU,Reads
Analyze the worst CPU statement in detail
$r = Get-sqmPlanCacheReport -SqlInstance "SQL01" -SortBy CPU -Top 1 -ExportPlans -NoReport Get-sqmPlanInsights -Path $r[0].PlanFile