Execution Flow
Synopsis
Comprehensive Query Store management for one, multiple, or all user databases. Supports three combinable operating modes: Configure (ALTER DATABASE SET QUERY_STORE), Query (top-N query analysis), and Diagnose (READ_ONLY detection, memory pressure, plan regression, forced plan failures, execution variance). Without explicit switches, Query + Diagnose are active (report mode).
Requires: dbatools, VIEW DATABASE STATE. Available from SQL Server 2016 (compatibility level ≥ 130).
Syntax
Invoke-sqmQueryStore [-SqlInstance <String>] [-SqlCredential <PSCredential>] [-Database <String[]>] [-All] [-Configure] [-Query] [-Diagnose] [-OperationMode <READ_WRITE|READ_ONLY|OFF>] [-FlushIntervalSeconds <Int>] [-IntervalLengthMinutes <Int>] [-MaxStorageSizeMB <Int>] [-QueryCaptureMode <ALL|AUTO|NONE>] [-SizeBasedCleanupMode <OFF|AUTO>] [-MaxPlansPerQuery <Int>] [-TopN <Int>] [-OrderBy <String>] [-LookbackHours <Int>] [-MinExecutionCount <Int>] [-StorageWarningPct <Int>] [-MaxPlansWarning <Int>] [-OutputPath <String>] [-EnableException]
Parameters
| Parameter | Type | Default | Description |
|---|---|---|---|
| -SqlInstance | String | $env:COMPUTERNAME | SQL Server instance to connect to. |
| -SqlCredential | PSCredential | — | Credential for the SQL connection. |
| -Database | String[] | — | One or more databases. Ignored when -All is set. |
| -All | Switch | false | Process all accessible user databases. |
| -Configure | Switch | false | Enable and configure Query Store (ALTER DATABASE SET QUERY_STORE). |
| -Query | Switch | false | Read top-N queries from the Query Store. |
| -Diagnose | Switch | false | Detect issues and return them as Issue objects. |
| -OperationMode | String | READ_WRITE | Query Store mode: READ_WRITE, READ_ONLY, OFF. |
| -FlushIntervalSeconds | Int | 900 | Frequency of flushing data to disk (60–86400 s). |
| -IntervalLengthMinutes | Int | 60 | Length of one statistics interval in minutes. |
| -MaxStorageSizeMB | Int | 1000 | Maximum Query Store size in MB (10–10240). |
| -QueryCaptureMode | String | AUTO | ALL, AUTO, or NONE. |
| -SizeBasedCleanupMode | String | AUTO | OFF or AUTO — auto cleanup under storage pressure. |
| -MaxPlansPerQuery | Int | 200 | Maximum execution plans stored per query. |
| -TopN | Int | 25 | Number of top queries to return (1–1000). |
| -OrderBy | String | Duration | Sort column: Duration, CPU, LogicalReads, ExecutionCount, Memory. |
| -LookbackHours | Int | 24 | How many hours back to look (1–8760). |
| -MinExecutionCount | Int | 5 | Minimum executions for a query to appear in results. |
| -StorageWarningPct | Int | 80 | Storage fill percentage that triggers a Warning issue. |
| -MaxPlansWarning | Int | 5 | Plan count per query that triggers a PlanInstability issue. |
| -OutputPath | String | Config\QueryStore | Directory for CSV and TXT report files. |
| -EnableException | Switch | false | Throw terminating exceptions instead of writing errors. |
Return Value
Array of [PSCustomObject] — one entry per database:
| Property | Type | Description |
|---|---|---|
| SqlInstance | String | Target SQL Server instance. |
| DatabaseName | String | Database name. |
| QSOptions | DataRow | Current Query Store options from sys.database_query_store_options. |
| ConfigureStatus | String | 'Success' or 'Failed: …' — only populated when -Configure is used. |
| TopQueries | DataRow[] | Top-N query records from the Query Store. |
| Issues | PSCustomObject[] | Detected issues with Severity, Category, Description, Detail. |
| IssueCount | Int | Total number of issues found. |
| ReportFile | String | Path to the written TXT issues report, if any. |
Examples
# Report mode: Query + Diagnose for all databases Invoke-sqmQueryStore -All # Configure and immediately query + diagnose Invoke-sqmQueryStore -Database "SalesDB","CRM" -Configure -Query -Diagnose # Top 50 queries by CPU consumption, last 48 hours Invoke-sqmQueryStore -Database "SalesDB" -Query -TopN 50 -OrderBy CPU -LookbackHours 48 # Diagnostics with lower storage threshold and custom report path Invoke-sqmQueryStore -All -Diagnose -StorageWarningPct 70 -OutputPath "D:\Reports\QS"
Diagnose Issue Categories
| Category | Severity | Condition |
|---|---|---|
| QSDisabled | Critical | Query Store is OFF — cannot run Query or Diagnose. |
| ReadOnly | Critical | ActualState = READ_ONLY. Decodes ReadOnlyReason code (1=size, 2=compat, 4=recovery, 8=ALTER, 65=combined). |
| StoragePressure | Warning/Critical | StoragePct ≥ StorageWarningPct (Critical at ≥ 95%). |
| PlanInstability | Warning/Critical | Query has ≥ MaxPlansWarning distinct plans. Critical if regression > 200%. |
| ForcedPlanFailure | Critical | is_forced_plan=1 AND force_failure_count > 0. |
| HighVariance | Warning | Execution time variation > 300% over lookback period. |
Notes
Requires dbatools and VIEW DATABASE STATE permission on each target database. Query Store is available from SQL Server 2016 (compatibility level ≥ 130).
If no operating switch (-Configure / -Query / -Diagnose) is specified, the function runs in report mode: both -Query and -Diagnose are activated automatically.