Query Store plan does not include parameter list

Question

I use the query store to get an overview of executed queries on our databases and to generate performance tests where I execute those queries in parallel on multiple hosts.

My task is to test how many concurrent users we can sustain before database response times increase.

In all databases that I looked at with query store, the XML query plans included a tag "ParameterList" from which I read out all the parameters for the query.

Now I stumbled across a database where not a single query captured in the query-store has its parameter values listed in the query plan.

What is be the reason for that?

If its relevant: The database in question is used by Microsoft Dynamics.

Server is 13.0.4259.0. Compatibility Level of the DB is 2016(130')

The queries in the Query Store look like this:

(@0 int,@1 nvarchar(20),@2 int)SELECT "39"."timestamp",...... WITH(READUNCOMMITTED) 
ON ("39"."Document Type" = "39_e1"."Document Type") 
AND ("39"."Document No_" = "39_e1"."Document No_") 
AND ("39"."Line No_" = "39_e1"."Line No_") 
WHERE ("39"."Document Type"=@0 
AND "39"."Document No_"=@1 AND "39"."Line No_"=@2) 
OPTION(OPTIMIZE FOR UNKNOWN)

Answer 1

ugly

Using the optimize for unknown hint will remove the parameter list from the query plan XML in the same way that using local variables will. Note that this only applies to local variables in cached plans, and plans in query store. They will still show up in actual execution plans.

DECLARE
    @ick int = 1;

SELECT
    u.*
FROM dbo.Users AS u
WHERE u.Id = @ick;


SELECT
    u.*
FROM dbo.Users AS u
WHERE u.Id = 1
OPTION(OPTIMIZE FOR UNKNOWN);

baffling

OPTIMIZE FOR UNKNOWN is considered a worst practice by most people who care about database performance.

It can be disabled in the settings of Dynamics globally along with other bizarre query hint choices.

Answer 2

When OPTIMIZE FOR UNKNOWN or USE HINT ('DISABLE_PARAMETER_SNIFFING') is used, or the database scoped configuration option is set not to perform parameter sniffing, the Parameter List only shows up in locally executed post-execution ('actual') plans.

The plan cache and query store contain a version of the plan without the Parameter List. This appears to be intentional behaviour, probably because the plans do not depend on parameter values in those cases. One could argue this is unhelpful, but it is what it is.

There is one option to produce a plan based on average distribution statistics while retaining the Parameter List: Enable documented trace flag 4136 instead of using the query hints. See KB980653 for details.

This can be done at the query level with OPTION (QUERYTRACEON 4136) or enabled at the session, global, and startup scopes.

Trace flag 4136 does require admin permissions to set but if the queries are known in advance, you could use a plan guide to apply the trace flag as a query hint without the caller needing those elevated permissions.

In SQL Server 2022, it is possible to apply OPTIMIZE FOR UNKNOWN or USE HINT('DISABLE_PARAMETER_SNIFFING') as a query store hint while retaining the Parameter List.