I first came across the value for RetrievedFromCache when I was reading a confusing execution plan. At first, I thought this could be really useful. If this value was true, you could assume that another query had compiled the execution plan that your query used and your query had retrieved that plan from the cache.
What I came to realize is that you can’t trust this value to be correct. Your database settings and type of query will change whether it’s true or not.
Here’s a couple tests that show how unpredictable this value is
I start each test by running DBCC FREEPROCCACHE to clear the plan cache on my test SQL Server. Don’t run this test in prod.
Starting the tests with an ad hoc SQL query.
SELECT PostTypeId, Tags,Body
FROM Posts where PostTypeId = 3
Okay, this test looks good on the surface. There’s two more things you might be interested in. This query is not considered a trivial execution plan, but it did qualify for simple parameterization. Take a look at the circled parameter here.
Let’s add some complexity so that the value isn’t parameterized, again clearing the plan cache.
Now I’m just confused. The value 3 wasn’t parameterized, but evidently this query was retrieved from an empty plan cache? Let’s check the plan cache for that query hash and see what it has to say.
Looking into the plan cache
Hmmm. So at the very least, this RetrievedFromCache value is unreliable. If I had to guess, I’d say that this value is true if the plan can be stored in cache, not if it was retrieved. Maybe that’s just a slight typo?
I’d like to dig in more to what this value means but I don’t know where to start. I hope this post helps shed some light on where this value might be coming from.
Thanks for reading and stay tuned!