--- title: "Find-DbaDbQueryStoreRegression" description: "Detects query performance regressions from Query Store runtime statistics using an execution-weighted baseline." url: "https://dbatools.io/Find-DbaDbQueryStoreRegression/" availability: "Windows, Linux, macOS" tags: ["QueryStore", "Performance", "Diagnostic"] author: "Deepesh Dhake" source: "https://github.com/dataplat/dbatools/blob/master/public/Find-DbaDbQueryStoreRegression.ps1" last_updated: "2024-01-01" --- # 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 ```powershell Find-DbaDbQueryStoreRegression [[-SqlInstance] ] [[-SqlCredential] ] [[-Database] ] [[-ExcludeDatabase] ] [[-InputObject] ] [[-BaselineStartDaysAgo] ] [[-BaselineEndDaysAgo] ] [[-SlowdownThreshold] ] [[-MinExecutionCount] ] [[-MinTotalDurationMs] ] [-EnableException] [] ``` ## Examples ### Example 1: Finds queries in AdventureWorks that ran at least 50% slower in the last day versus the prior 7-to-1-day... ```powershell 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 ```powershell 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 ```powershell PS C:\> Get-DbaDatabase -SqlInstance sql2017 | Find-DbaDbQueryStoreRegression | Sort-Object SlowdownFactor -Descending ``` ### Optional Parameters ##### -SqlInstance The target SQL Server instance or instances. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | true (ByValue) | | Default Value | | ##### -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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -Database The database(s) to process. If unspecified, all Query Store-enabled user databases are processed. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -ExcludeDatabase The database(s) to exclude. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | true (ByValue) | | Default Value | | ##### -BaselineStartDaysAgo Start of the historical baseline window, in days before now. Default: 7. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | 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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | 1 | ##### -SlowdownThreshold Minimum ratio of current duration to baseline duration for a query to be flagged. Default: 1.5 (50% slower). | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | 1.5 | ##### -MinExecutionCount Minimum executions in the current window for a query to be considered. Default: 20. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | 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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | 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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | False | ## 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 --- Part of [dbatools](https://dbatools.io/), a free and open source PowerShell module for SQL Server administration. Full command index: https://dbatools.io/commands/