Posted on

sp_query_store_flush_db, Query Store, and Backups

An old fashioned Qual-E Store
An old fashioned Qual-E Store
Quail-E-Store

One of the cool things about Query Store is that you can back it up with the database.

This isn’t perfect for every situation, because you must back up the whole database. If you’ve got a multi-terabyte database, your Query Store isn’t super portable! But still, it’s great to have an option.

There are a few things worth knowing about how Query Store works with backups and restores.

If Query Store is read/write when you back up a database, it restores the same way

Let’s say your production database is collecting data with Query Store. When you restore a full backup elsewhere, the Query Store data from production restores with it, and Query Store stays in the same read-write state.

Maybe you want this, maybe you don’t!

It’s a good idea to check if Query Store is enabled and its settings after a restore. You can do this simply with the following query:

SELECT *
FROM sys.database_query_store_options;
GO

This is a database level query, so use the database before you run it.

If you want to back up very recent activity in Query Store, run sys.sp_query_store_flush_db before the backup

Query Store was designed to be clever, and to minimize its impact on your performance. Query Store only flushes its data from memory to disk periodically. You get to control this by setting the data flush interval in the Query Store settings for a database. (Read more about this in Microsoft Documentation here.)

The default value for Query Store data flush is 15 minutes. That means that in the case of a crash, you might lose up to around 15 minutes of activity.

Even if you’ve lowered this, you might want to make sure that a backup contains the very latest activity, particularly if you’re taking the backup to get Query Store data for someone to look at.

You can manually flush Query Store data to disk before the backup by running the built in procedure, sys.sp_query_store_flush_db in that database.

Want to play around with some sample code?

Here is some very simple code that creates a database, enabled query store, and lets you easily play around with and test sys.sp_query_store_flush_db.

2 thoughts on “sp_query_store_flush_db, Query Store, and Backups

  1. Hi,

    I’m creating stroeprocedure in memory optimized database (native compile).Problem is how to use case condition on the date my regular T-SQL is Look like this.

    DATEDIFF(second, ModifiedOn,GETDATE()) >= 3) THEN 1 ELSE 0 END

    But this command is not supported in SQL Server 2016.

    how can I fixed it:

    1. Hi there,

      I’m not clear on exactly what you’re doing– and this post is on an entirely different topic, so this isn’t the place to answer it.

      For best results, post a repro code sample along with errors that you’re getting — a Q&A site like dba.stackexchange.com or SQL Server Central could probably help you really fast, but you want to give a full repro.

Leave a Reply

Your email address will not be published. Required fields are marked *

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