3

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:

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

Query Store screen capture

Why isn't only one plan being used?

2

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.

  • I guess the question from OP is - why sql server generates different plans for the same parameters supplied (parameters are no changing) ? – Kin Shah Dec 14 '18 at 16:54
  • Fair enough, I'll add some color in that area – LowlyDBA Dec 14 '18 at 17:04
  • 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? – Mark Freeman Dec 17 '18 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. – LowlyDBA Dec 17 '18 at 21:01
  • Understood. Further investigation shows that the number of rows in the two referenced tables are constantly changing as well. Thanks. – Mark Freeman Dec 18 '18 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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