Performance tuning tips from the SQLGrease team

Where did that execution plan come from?

I’ve had a lot of cases where I have a developer send over an execution plan and ask me to look at their query. I’m really hesitant to waste time looking at an estimated execution plan rather than a (real) plan retrieved from the plan cache. For a variety of reasons it is best to work with plans from the plan cache as opposed to estimated plans. Fortunately starting in SQL Server 2012 there is a simple way to see if the plan was retrieved from the cache.

How to view this?

Open the execution plan as XML. Nested within the StmtSimple element you will see the attribute RetrievedFromCache.

If this value is true the plan was retrieved from the cache or by executing the statement in SSMS with the “Include Actual Plan” button checked.

Something important to note with query store. Query store sometimes shows RetrievedFromCache as false. Even though the query store plan is an accurate representation of a plan a query executed under, it does not always indicate that it was retrieved from the plan cache. I have not been able to determine what causes this. This is just a guess, but maybe this is related to a plan that is not yet cached (i.e. executing for the first time).

One other important thing to look at is whether or not the plan is an “Actual” plan as it was run from SSMS versus pulled from the plan cache. This is pretty obvious when you look at nodes in the execution plan and see “Actual” values along with the estimate values.