I see the following query in an Azure SQL Database (that has Forced Parameterization = FORCED, Parameter Sniffing = ON, and Query Optimizer Fixes = ON) from within Query Store:

delete from [CMS_WebFarmTask] 
where [TaskIsAnonymous] = @0 
and [TaskID] not in ( select [TaskID] from [CMS_WebFarmServerTask] ) 
and [TaskCreated] < dateadd ( minute , @1 , getdate ( ) )

It has a large number of plans (17 in the last hour), despite both parameters being integers with identical values in each plan:

          <ColumnReference Column="@1" ParameterDataType="int" ParameterCompiledValue="(-3)" />
          <ColumnReference Column="@0" ParameterDataType="int" ParameterCompiledValue="(0)" />

Query Store screen capture

Why isn't only one plan being used?

1 Answer 1


Forced parameterization <> forced single plan.

However, the Query Store does have a feature to force a single plan if that's what you're after. You can see the button for it right in your screenshot.

But, if your concern is more general as to why you're getting multiple plans in the first place, you should start by making sure the same query is being executed each time. In your case, since you're using GETDATE(), the query is actually different every time it runs, so I wouldn't expect it to yield the same plan necessarily based on that alone.

You also need to be aware of the fact that the statistics of the underlying tables could be changing over time as well, which can heavily impact plan estimations. There are lots of other factors that play into it - this article digs deeper into what goes into an execution plan and what can affect them. The database is a living thing and plan changing shouldn't be inherently viewed as a bad thing, unless it is causing obvious performance issues.

  • The key does seem to be that the number of minutes (always the same value) is being passed as parameter @1. If the value of "dateadd ( minute , @1 , getdate ( ) )" was passed as a parameter instead (eliminating the getdate() call), then we might see only one plan for this query, assuming no significant change in statistics or other more extraordinary events. Does that sound correct? Commented Dec 17, 2018 at 20:45
  • @MarkFreeman No, because your query is still operating off a different time value for each run. If you removed the date portion of the query and the underlying data was static, then you'd probably be likely to see a single (or very few) different plans. Commented Dec 17, 2018 at 21:01
  • Understood. Further investigation shows that the number of rows in the two referenced tables are constantly changing as well. Thanks. Commented Dec 18, 2018 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.