Summary
Most Query Store settings are changed through SMO, but MaxPlansPerQuery, WaitStatsCaptureMode and the four CustomCapturePolicy* options are changed with an ALTER DATABASE statement. After that statement the SMO object is not refreshed, so the object the command returns still carries the values from before the change.
This is not the SMO caching that a reused connection brings with it - it happens on every call, even when the command opens the connection itself, because the command reads $db.QueryStoreOptions before it runs the T-SQL and that populates the cache.
Follow-up to #10560, which went at this from the wrong end.
Steps to Reproduce
Every connection here is opened by the command itself:
$null = New-DbaDatabase -SqlInstance $instance -Name $dbName
$null = Set-DbaDbQueryStoreOption -SqlInstance $instance -Database $dbName -State ReadWrite
$result = Set-DbaDbQueryStoreOption -SqlInstance $instance -Database $dbName -MaxPlansPerQuery 555 -WaitStatsCaptureMode Off
$result | Select-Object MaxPlansPerQuery, WaitStatsCaptureMode
Invoke-DbaQuery -SqlInstance $instance -Database $dbName -Query "SELECT max_plans_per_query, wait_stats_capture_mode_desc FROM sys.database_query_store_options"
returned by Set : MaxPlansPerQuery=200 WaitStatsCaptureMode=On <-- the values from before the change
instance really has: MaxPlansPerQuery=555 WaitStatsCaptureMode=OFF
Verified on SQL Server 2019 and 2022, with dbatools 2.8.4 and current development. SQL Server 2016 is not affected, because none of these options exist there.
Copy-DbaDbQueryStoreOption runs into the same thing, because it copies the settings by calling this command.
Root cause
The command already does the right thing for the SMO path - Set-DbaDbQueryStoreOption.ps1:247-249:
$db.QueryStoreOptions.DesiredState = $State
$db.QueryStoreOptions.Alter()
$db.QueryStoreOptions.Refresh()
The T-SQL path further down never got the same treatment. $db.Refresh() at :344 runs before the statement is executed at :347, and nothing reads the values back afterwards. The trailing Get-DbaDbQueryStoreOption at :355 then reports what the cached object holds.
Suggested fix
Refresh right after the statement was executed:
if ($query -ne "") {
$db.Query($query, $dbName)
$db.QueryStoreOptions.Refresh()
}Verified on SQL Server 2022:
returned by Set for its own change: MaxPlansPerQuery=444 WaitStatsCaptureMode=On <-- was 200/On
instance really has now : MaxPlansPerQuery=444 WaitStatsCaptureMode=ON
That is one round trip per database, and only for the calls that actually use one of these options. What a reused connection reports about changes made elsewhere is unaffected, as it should be.
Note for whoever picks this up: #10558 changes the line that executes the statement, so the refresh belongs after whatever that line looks like at the time.
Test
Per tests/CLAUDE.md, a regression test that sets -MaxPlansPerQuery and -WaitStatsCaptureMode and expects the returned object to carry the values that were just set. No connection reuse needed - the old behaviour fails this with a fresh connection as well.
This text was created by Claude and reviewed by Andreas Jordan.
Summary
Most Query Store settings are changed through SMO, but
MaxPlansPerQuery,WaitStatsCaptureModeand the fourCustomCapturePolicy*options are changed with anALTER DATABASEstatement. After that statement the SMO object is not refreshed, so the object the command returns still carries the values from before the change.This is not the SMO caching that a reused connection brings with it - it happens on every call, even when the command opens the connection itself, because the command reads
$db.QueryStoreOptionsbefore it runs the T-SQL and that populates the cache.Follow-up to #10560, which went at this from the wrong end.
Steps to Reproduce
Every connection here is opened by the command itself:
Verified on SQL Server 2019 and 2022, with dbatools 2.8.4 and current
development. SQL Server 2016 is not affected, because none of these options exist there.Copy-DbaDbQueryStoreOptionruns into the same thing, because it copies the settings by calling this command.Root cause
The command already does the right thing for the SMO path -
Set-DbaDbQueryStoreOption.ps1:247-249:The T-SQL path further down never got the same treatment.
$db.Refresh()at:344runs before the statement is executed at:347, and nothing reads the values back afterwards. The trailingGet-DbaDbQueryStoreOptionat:355then reports what the cached object holds.Suggested fix
Refresh right after the statement was executed:
Verified on SQL Server 2022:
That is one round trip per database, and only for the calls that actually use one of these options. What a reused connection reports about changes made elsewhere is unaffected, as it should be.
Note for whoever picks this up: #10558 changes the line that executes the statement, so the refresh belongs after whatever that line looks like at the time.
Test
Per
tests/CLAUDE.md, a regression test that sets-MaxPlansPerQueryand-WaitStatsCaptureModeand expects the returned object to carry the values that were just set. No connection reuse needed - the old behaviour fails this with a fresh connection as well.This text was created by Claude and reviewed by Andreas Jordan.