Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Query Store and Log Backups

DZone's Guide to

Query Store and Log Backups

Let's take a quick look at how log backups affect the Query Store in SQL Server as well as explore some code.

· Database Zone ·
Free Resource

Download the Scale-Out and High Availability whitepaper. Learn why leading enterprises choose the Couchbase NoSQL database over MongoDB™ after evaluating side by side.

A question that came up recently around Query Store is what happens when there are log backups in use on the database. Let's talk about it.

Query Store and Log Backups

The core of the answer is very simple. Query Store, like any other data written to a database, whether a system table or a user table, is a logged operation. So, when you back up the database, you're backing up Query Store data. When you back up the logs, you're also backing up Query Store data. A point in time will include all the data written to the Query Store at that point.

However, that's the kicker. At what point was the Query Store information written to disk? By default, there's a fifteen-minute cycle before the Query Store moves the data from memory to disk. After it gets written, it will be available through your log backups. Before it gets written though, if you take a log backup, then whatever is in memory is not going to be included. So it does come to down to getting the right point in time.

You can control things though. If you wanted to, let's say as part of taking the tail log backup, ensure that anything in memory for Query Store was written to disk first, you can. Run the following command and everything in memory for Query Store gets flushed to disk:

EXEC sys.sp_query_store_flush_db;

For a very tiny amount of additional detail, read here.

Conclusion

There's really only a very minor gotcha around the Query Store when it comes to log backups. If it's on the disk for the point in time you're recovering to, it's available. If it was in memory at that point in time, it's not. If you are concerned about the Query Store and log backups, just make sure you flush the data to disk more frequently or using the command.

I have lots more information on the Query Store to share. Please let me know your thoughts or any questions you may have in the comments. 

The Forrester Wave™: Big Data NoSQL report. See how the top NoSQL providers stack up. Download now.

Topics:
database ,sql server ,query store ,logs ,backups

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}