Does enabling QueryStore flush the plan cache?

Question

Hi I think the answer to my question is "yes" from what i've seen in 2 test environments but is that the case, does enabling QueryStore flush the plans from cache for the DB you're enabling QueryStore on and if so what's the rationale behind it? And is there anyway of avoiding it.

Thanks

Answer 1

A quick test on SQL 2017 confirms your suspicion. I dont' want to speculate in why they are doing it.

Answer 2

Thanks Erland

I asked Erin Stellato on one of the articles she'd done on QueryStore yesterday too and she indicated it's fixed in later versions of SQL2016 eg SP1 CU2 and although I was testing it originally on SP1, i've since updated a local test envt to SQL2016 SP1 CU9 and I still see the same issue...and as you say you still see it in 2017.

Although I think it will still be beneficial introducing QueryStore to envts I have in mind despite the above it's not a great sell that there'll have to be an initial performance hit whilst everything is re-cached.

Answer 3

Hey Erland-

Can you provide a repro, as I would like to test?  This is not expected at all, and I haven't seen this same behavior, so I would be very interested in trying to recreate the scenario.

Thanks!

Erin

---

Erin Stellato http://www.erinstellato.com

Answer 4

What I did was to run some queries in a database so that I got some cache entries. (As this is an instance home there is not much in the cache to start with.) I then ran

SELECT COUNT(*)
FROM   sys.dm_exec_query_stats qs
CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle)
WHERE  qs.dbid = db_id()

The number returned was 9. I then enabled Query Store for that database and re-ran the query above, which now returned 0.

Of course, it could be that general spooky cache-eviction this that has been observed by several. But my desktop has 64 GB of RAM, but that should be sufficient, even if I had quite a few VMs up at the time.

I can't retest on that machine now, since I'm away from home. On my laptop the cache-eviction thing certainly have been observed, so I am not trying here. (And I am soon off to bed.

Answer 5

Ok, but to be clear, you're only seeing the plan cache flushed when you ENABLE Query Store.  Not at any other time, correct?

Thanks,

E

---

Erin Stellato http://www.erinstellato.com

Answer 6

Ok, but to be clear, you're only seeing the plan cache flushed when you ENABLE Query Store.  Not at any other time, correct?

Not that has been related to Query Store to my knowing. (There are other issues that causes the plan cache to shrink or be flushed entirely.)

Answer 7

Hi I think the answer to my question is "yes" from what i've seen in 2 test environments but is that the case, does enabling QueryStore flush the plans from cache for the DB you're enabling QueryStore on and if so what's the rationale behind it? And is there anyway of avoiding it.

Thanks

With all these tests did you made sure that SQL Server service account had locked pages in memory privilege and you had sufficient memory. I had not tested it on SQL Server 2017 but yes the issue was in SQL Server 2016. I never got hold of any MS blog which authoritatively states this is fixed. Did I missed any such blog ?

---

Cheers,

Shashank

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My TechNet Wiki Articles
MVP

Answer 8

I did some testing and checked with MS, the database plan cache will be cleared when you enable Query Store, or change a setting:

https://www.sqlskills.com/blogs/erin/query-store-and-the-plan-cache-flushing/

---

Erin Stellato http://www.erinstellato.com

Answer 9

Thanks Erin for the post!

I seem to recall that I have changed INTERVAL_LENGTH_MINUTES for troubleshooting a performance issue in a nightly batch. That is, by setting the interval to suitable length, I could be quite sure that I could capture the problematic procedure in a specific interval.

I'll make a mental note of that this clears the plan cache for the database, so I think twice next time I do it, or recommend it to someone else.