Find-DbaDbQueryStoreRegression

Synopsis

Detects query performance regressions from Query Store runtime statistics using an
execution-weighted baseline.

Description

Reads sys.query_store_runtime_stats and flags queries whose recent performance has
regressed against a historical baseline.

Rather than a naive average-vs-average comparison, each plan’s historical duration is
weighted by execution count, and low-frequency / low-total-impact queries are filtered
out, so genuine regressions surface instead of noise from a handful of slow one-off
executions.

This command is read-only: it queries Query Store and returns objects. It does not force
plans, clear the store, or change any configuration.

Query Store must be enabled on the target database(s) (SQL Server 2016+).

Syntax

Find-DbaDbQueryStoreRegression
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Database] <Object[]>]
    [[-ExcludeDatabase] <Object[]>]
    [[-InputObject] <Database[]>]
    [[-BaselineStartDaysAgo] <Int32>]
    [[-BaselineEndDaysAgo] <Int32>]
    [[-SlowdownThreshold] <Double>]
    [[-MinExecutionCount] <Int32>]
    [[-MinTotalDurationMs] <Int64>]
    [-EnableException]
    [<CommonParameters>]

Examples

Example 1: Finds queries in AdventureWorks that ran at least 50% slower in the last day versus the prior 7-to-1-day...

PS C:\> Find-DbaDbQueryStoreRegression -SqlInstance sql2017 -Database AdventureWorks

Finds queries in AdventureWorks that ran at least 50% slower in the last day versus the
prior 7-to-1-day baseline, considering only queries executed 20 or more times.

Example 2: Only flags queries in Sales that at least doubled in duration and ran 50 or more times

PS C:\> Find-DbaDbQueryStoreRegression -SqlInstance sql2017 -Database Sales -SlowdownThreshold 2.0 -MinExecutionCount 50

Example 3: Pipes databases in and returns all regressions across the instance, worst first

PS C:\> Get-DbaDatabase -SqlInstance sql2017 | Find-DbaDbQueryStoreRegression | Sort-Object SlowdownFactor -Descending

Optional Parameters

-SqlInstance

The target SQL Server instance or instances.

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell
credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and
Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-Database

The database(s) to process. If unspecified, all Query Store-enabled user databases are
processed.

-ExcludeDatabase

The database(s) to exclude.

-InputObject

Database objects piped in from Get-DbaDatabase. Use this to narrow the analysis with the
full Get-DbaDatabase filter set before the Query Store statistics are read.

-BaselineStartDaysAgo

Start of the historical baseline window, in days before now. Default: 7.

-BaselineEndDaysAgo

End of the historical baseline window, in days before now. Default: 1. The baseline
window is therefore BaselineStartDaysAgo..BaselineEndDaysAgo, and the current window is
BaselineEndDaysAgo..now.

-SlowdownThreshold

Minimum ratio of current duration to baseline duration for a query to be flagged.
Default: 1.5 (50% slower).

-MinExecutionCount

Minimum executions in the current window for a query to be considered. Default: 20.

-MinTotalDurationMs

Minimum total current duration in milliseconds (summed across executions) for a query to
be considered. Filters queries that are individually slow but negligible to the overall
workload. Default: 100.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a
friendly warning message. This avoids overwhelming you with “sea of red” exceptions, but
is inconvenient because it basically disables advanced scripting.
Using this switch turns this “nice by default” feature off and enables you to catch
exceptions with your own try/catch.

Outputs

PSCustomObject

Returns one object per query flagged as regressed, ordered by SlowdownFactor descending within each database. Nothing is returned for a database with no regression.

Default display properties (via Select-DefaultView):

• SqlInstance: The full SQL Server instance name (computer\instance)
• Database: Name of the database the query ran in
• QueryId: The Query Store query_id, usable with sys.query_store_query and the Query Store reports
• BaselineDurationMs: Execution-weighted average duration over the baseline window, in milliseconds
• CurrentDurationMs: Execution-weighted average duration over the current window, in milliseconds
• SlowdownFactor: CurrentDurationMs divided by BaselineDurationMs
• PlanChanged: True when the query ran under more plans in the current window than in the baseline, or under more than one plan in the current window
• CurrentExecCount: Number of executions in the current window

Additional properties available:

• ComputerName: The computer name of the SQL Server instance
• InstanceName: The SQL Server instance name
• BaselineExecCount: Number of executions in the baseline window