Removing All SQL Server Query Store Data

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…

15 thoughts on “Removing All SQL Server Query Store Data

  • Anon

    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.

      • Anon

        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.

          • Anon

            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.

  • Kanishka Basak

    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.

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.