Get-DbaDbQueryStoreOption - Queries MaxPlansPerQuery and WaitStatsCaptureMode on SQL Server 2019 and later, then reports something else

[andreasjordan]

Summary

For SQL Server 2017 and later the command runs a query against sys.database_query_store_options because some values are not read back through SMO. On SQL Server 2019 and later that query fetches six values, but only the four CustomCapturePolicy* ones are added to the output object. MaxPlansPerQuery and WaitStatsCaptureMode are dropped, so the two properties are reported from the SMO object instead - out of a query that has already fetched the correct values and runs either way.

The SQL Server 2017 branch directly above adds all of them. So the same command reports live values on 2017 and whatever SMO holds on 2019 and later.

This is not about refreshing anything - see #10560, which was closed for going that way. It is about which of two values that are already in hand gets used.

Steps to Reproduce

$server = Connect-DbaInstance -SqlInstance $instance
$null = Set-DbaDbQueryStoreOption -SqlInstance $server -Database $dbName -State ReadWrite

# Change the settings from a separate connection
$splatChange = @{
    SqlInstance = $instance
    Database    = "master"
    Query       = "ALTER DATABASE [$dbName] SET QUERY_STORE = ON (MAX_PLANS_PER_QUERY = 321, WAIT_STATS_CAPTURE_MODE = OFF)"
}
$null = Invoke-DbaQuery @splatChange

# What the command itself queries internally
$splatRead = @{
    SqlInstance = $instance
    Database    = $dbName
    Query       = "SELECT max_plans_per_query AS MaxPlansPerQuery, wait_stats_capture_mode_desc AS WaitStatsCaptureMode FROM sys.database_query_store_options"
}
Invoke-DbaQuery @splatRead

# What it reports
Get-DbaDbQueryStoreOption -SqlInstance $server -Database $dbName | Select-Object MaxPlansPerQuery, WaitStatsCaptureMode

Output on SQL Server 2019 (v15) and SQL Server 2022 (v16), dbatools 2.8.4 and current development:

the query the command runs      : MaxPlansPerQuery=321 WaitStatsCaptureMode=OFF
what the command reports        : MaxPlansPerQuery=200 WaitStatsCaptureMode=On

A connection that was used before makes this visible, but the discarded values are discarded on every call.

Root cause

Get-DbaDbQueryStoreOption.ps1:134 queries six values for SQL Server 2019 and later, and :148-153 adds four of them. The SQL Server 2017 branch at :144-147 adds MaxPlansPerQuery and WaitStatsCaptureMode from the same query. Both properties stay in Select-DefaultView, so they are displayed either way - just from a different source depending on the version.

Two ways out

1. Add the two Add-Member lines to the SQL Server 2019 branch. Both branches then report these two properties from the query, at no extra cost, since the query runs anyway.
2. Drop the two columns from the SQL Server 2019 query. Then SMO is the single source on 2019 and later - but 2017 still reports them from its query, so the inconsistency between the branches remains unless that branch goes too.

Option 1 keeps the two versions in step and is one line each. Option 2 is only complete if SMO delivers these values on SQL Server 2017 as well, which is presumably why that branch exists in the first place. Settling that needs a SQL Server 2017 instance, which the lab used for this report does not have - it runs 2019, 2022 and 2025.

Test

Per tests/CLAUDE.md, whichever way it goes: read the options, change MAX_PLANS_PER_QUERY from a separate connection, and assert what the command reports through a server object that was used before.

---

This text was created by Claude and reviewed by Andreas Jordan.

[andreasjordan]

@potatoqualitee Do you have a 2017 instance in you lab?