While setting up example code for my presentation at SQL Cruise (which is going to be a fantastic event), I realized I wanted to purge all the data from my Query Store, just for testing. I did a series of searches to try to track down the information and it just wasn’t there. So, I did what anyone who can phrase a question in less than 140 characters should do, I posted a question to Twitter using the #sqlhelp hash tag.
Jamey Johnston (t|b) came through… and it was right there in the documentation that I had been reading, over and over. In fact, it was in the documentation in two different places. Reading is clearly a problem for me today.
Just so that you know, it’s actually really easy:
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE CLEAR;
By the way, Query Store is absolutely amazing. Did you know that you can set up an extended event to fire when Query Store detects a query regression? Yep. The extended event query_store_aprc_regression will do just that. Query Store is coming with SQL Server 2016, but it’s already in production in Azure SQL Database.
Oh yes, we’re moving into some very interesting times for query tuning. Now if I can just get my reading comprehension to keep up…
[…] Grant Fritchey shows how to clear the Query Store in SQL Server 2016: […]
[…] Removing All SQL Server Query Store Data – Grant Fritchey (Blog|Twitter) […]
Unfortunately the QUERY_STORE CLEAR command is blocked by “QUERY STORE BACKGROUND FLUSH DB” background task and never completes (or I had no patience waiting because other queries started to suffer and this was a production machine). It’s 2017 CU11.
Yeah, if your system is hanging trying to flush the cache to disk, it’s certainly going to block the clear command. Normally, clear just works. However, on a system under high load, there could be blocking. I would assume it would clear normally, so something else may be going on here.
I just thought it’s some kind of “TRUNCATE” and can pre-empt the flushing task to complete quickly.
The problem is also I cannot change the state to READ_ONLY or OFF, it also lasts for hours and never completes.
Just to let you know what we’re talking about – the query store size was set to 1 GB, it already occupies 2.3 GB, there are over 230.000 queries in it. The procedure sp_query_store_remove_query needs around 10 seconds to remove just one query (I thought this method would be the emergency exit to remove all data one by one). Right now I’m stuck, we’re trying to find the right moment to bounce the server and maybe then it will allow us to turn off and clear Query Store. Users say the performance has become ‘sluggish’ and this is what I observe from SSMS as well.
Well, short answer, you already know, Query Store isn’t working for you, turn it off. Longer answer, is harder, much harder. The remove query command is for detailed work, pulling a single query out. Stop trying with it. You’re just adding to the overhead. I’d focus on getting the thing turned off and nothing else at the moment. After that, There are a whole bunch of knobs that can be tweaked to adjust the behavior, but you’re going to want to walk through them carefully. Not many people have a system with so much load that Query Store sends it over the edge. It’s possible you’re in that camp. It also might be, maybe, a configuration issue.
Thanks Grant. I only hope that starting SQL Server in single user mode will let me clean this stuff faster. I’ll be using trace flags 7745 and 7752 to not load Query Store data on startup synchronously and not run periodic flush.
Funny thing is this “sluggish” performance. I’m starting to wonder if this is caused by too many queries/plans stored in Query Store and access times becoming more and more of an issue, or maybe it’s just a coincidence. Just a food for thought. We’ll see after disabling QS.
Anyway, thanks again.
I’m assuming you’re not all that interested in troubleshooting this, rather getting out from under right now. However, if you are interested, there are QDS wait stats that will tell you where the pain is likely to be. Combine those with the standard wait stats and performance metrics, you can get an idea of why things are running slow. Again, probably not of interest now, but assuming you go forward and do more testing, that’s where I’d focus to start.
Grant, I have come across a similar problem as above and we had to turn it off. We did try with some settings change and that worked but not long. The client has a lot of adhoc loads.
So, if the adhoc workload is really high what do we have to do to get things running and not hit a problem like this.
We have allocated Query Store around 5TB and it exceeded much more than that and also slowed the server.
First thing I’d try is setting the capture mode to custom. Then make it so that you have to execute a query at least twice before Query Store captures it. I think that would be a bit help. There’s some other suggestions here: https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store?view=sql-server-ver15
Thanks Grant. Currently I am on SQL 2016 so not sure custom capture would hold true here. I presume that is on SQL 2019.
Is there any way to do a custom capture in SQL 2016 anyway?
Apart from this what does the query store read only reason 327680 mean?
I am not able to get anything for 327680 in MS Documentation
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-query-store-options-transact-sql?view=sql-server-ver15
Ah, 2016. You can switch the capture mode from all, to auto. That will change the behavior, not the same as custom, but close.
It set to Auto already to make sure we do not capture everything, but no respite.
our settings are
data flush interval 60
statistics collection interval 1hour
max size 5TB
query store capture mode auto
size based cleanup mode auto
stale query threshold days 60
SQL server version Microsoft SQL Server 2016 (SP2-CU15-GDR) (KB4583461)
Sorry. You might just not be able to use Query Store in this case. I’d probably reduce the retention from 60 days to 45 & see if that helps at all.
Thank you.. Its really an unique mess! Will try with the retention days and see if that helps.