If you’re using SQL Server 2016’s awesome new feature, Query Store, there’s a new bug with automatic cleanup. Books Online explains:
Automatic data cleanup fails on editions other than Enterprise and Developer. Consequently, space used by the Query Store will grow over time until configured limit is reached, if data is not purged manually. If not mitigated, this issue will also fill up disk space allocated for the error logs, as every attempt to execute cleanup will produce a dump file.
To work around it, you can manually clean up specific plans with sp_query_store_remove_plan and sp_query_store_remove_query, or just clear the Query Store out entirely with:
|
1 |
ALTER DATABASE (MyDB) SET QUERY_STORE CLEAR; |
We’ve already added an sp_Blitz check for this in the dev branch of the First Responder Kit (zip download), but just be careful using dev branch code since it’s not quite as well-tested as the master branch.
5 Comments. Leave new
I guess this shows that there is much less testing for SE because Azure does not use SE. That makes the new faster release process more risky for SE.
Ouch! That’s doubly hurtful on Express where there isn’t a SQL Agent. Though there is a workaround for *that* using Service Broker if you’re feeling frisky.
I just went searching for this to confirm it’s fixed in SQL Server 2016 CTP1 – appears to be so. Just adding the link to possibly save other readers a search – https://support.microsoft.com/en-us/kb/3178297
Yep! Make that CU1, heh.
Short ‘n’ sweet! But super useful, as always!
I’d been wanting to start using Query Store for a while now, as it seems like a great tool to have for performance tuning. I read Microsoft Docs and it advised me that the defaults are acceptable in the majority of cases.
I enabled QS on several instances and it was proving to be as useful as I expected. Then I had trouble with the third or fourth instance… First day, it was set to default of 100MB. I came into work the following day and found it had filled up. Hmmm… So I increased it to 500MB. Same thing the following day… I increased it to 1GB. It was still filling up! Finally, I decided this was not working as expected. I found your article and had a look over the instances I had enabled QS on. Yep, all Enterprise, except the one I had trouble with… The trouble one in 2016 SP2 CU3.
After reading Erik’s article (https://www.brentozar.com/archive/2018/12/query-store-and-cruel-defaults/), I’ve changed the capture mode setting to Auto. I’ll see how this affects things, but will probably set up and agent job to run the clear command.