SQL Server Query Store – Multiple Plans for Same Query

performancequery-performancequery-storesql-server-2016

I'm a newbie regarding the query store and have some problems understanding what I'm seeing.
We have a third-party application (running on SQL Server 2016 Enterprise SP1 CU7) that uses the query-hints KEEP PLAN and KEEPFIXED PLAN. When I do some monitoring in the querystore I see that some queries has multiple plan ids in the plan summary window.

Why can a query that uses KEEP PLAN and KEEPFIXED PLAN have multiple plans (sometimes completely different, sometimes the same)?

And the second question, if there are plans that look exactly the same (same physical operators, same set options, same queryhash), how can one plan have a missing index and the second plan not?

This are two anonymised plans: Plan 1 Plan 2

Best Answer

I'll Start with the second question

The reason for the 'missing index' advice for Plan 2 is the subtree cost calculated The parameters passed results in a subtree cost (right click/hoover on the nested loop join) that is 10 times higher than that for Plan 1, and it is because the estimated number of rows is 149, instead of 16. What is suggested as missing is a covering index, so that it doesn't have to make 149 keylookups to get the missing values from the index. For Plan 1 it was considered ok to make 16 keylookups

What you are experiencing is what Erland Sommaskog is calling a “morally equivalent plan”

Kendra Litttle explains it in this article https://sqlworkbooks.com/2018/03/what-is-a-morally-equivalent-execution-plan-and-why-is-it-good/ Using the feature of Query store to force a plan, but i believe it is the same when using KEEPFIXED PLAN

As to why several plans for the same query exists. Many reasons including restart, cache getting cleared due to memory pressure, changing the structure or schema of a table referenced by the query.

This will cause the plan to be recompiled

Normally also updating the statistics, but since you have KEEPFIXED PLAN that is not he case.

Having KEEPFIXED PLAN can cause performance degregation since as your data changes, the statistics change and the optimal plan may be different, but KEEPFIXED PLAN is forcing the same plan all the time.

i am guessing, but is the setting 

OPTION (KEEP PLAN, KEEPFIXED PLAN, LOOP JOIN)

used on all queries ? then the DBA will have to monitor if optimal plans are used (if data distribution have changed much since the plan was created)

An extra comment. Having both KEEP PLAN and KEEPFIXED PLAN in the hints is not needed. KEEPFIXED PLAN is overruling KEEP PLAN. KEEP PLAN, disables the “6 rows” rule, as far as statistics invalidation is concerned. KEEPFIXED PLAN disables recompilations due to statistics changes completely.

Related Question