What to consider when setting flush_interval_seconds in SQL Query Store?

Question

Most of the settings for Query Store are fairlying straight forward. But as best as I can tell the one most likely to cause performance issues (waits) and with the least clear documentation is flush_interval_seconds

I have read Erin Stellato's, SQL Server Query Store: Default Settings

I’d also probably drop DATA_FLUSH_INTERVAL_SECONDS to something lower than 900 seconds (15 minutes) if my storage can support it. This setting determines how often Query Store data is flushed to disk. If it’s every 15 minutes, then I could potentially lose 15 minutes of Query Store data if my server happened to crash before it could be written to disk.

Microsoft's Best Practice with the Query Store does not even mention it.

Microsoft's sys.database_query_store_options (Transact-SQL) says (for on Premisis);

Defines period for regular flushing of Query Store data to disk. Default value is 900 (15 min).

The above implies (possibly) Query Store data is held in memory until a regular 15 minutes flush occurs then it is pushed/flushed to disk

But the documents for Azure are slightly different. Operating the Query Store in Azure SQL Database

Specifies maximum period during which captured runtime statistics are kept in memory, before flushing to disk (emphasis is mine)

The Azure description implies Query Store data moves to disk as regularly as normal data, but if it has not moved by the age, then it is forced to disk.

I know that Query Store data is part of the database, I am not clear if logs are updated only when the data is pushed to disk, or in real time.

Some points I am not clear on that could impact the decision. (Maybe some of these should be separate questions?)

• Is query store data written to the log? If so would a restore let us recover any Query Store data, that had not been pushed to disk?

• If I have an AlwaysOn AG, do the secondaries get the Query Store data, real time or are they dependent on it actually getting written to disk on the primary? Rephrased if my Query Store data has not been written on the primary for 15 minutes, and the primary gets unplugged will the secondary capture it?

• Can a Query Store disk write have wait conflicts with users OLPT data? If so what are they?

• What does changing the flush_interval_seconds value really do?

I have this scenario in my mind where, there is a long running query using all the system resources for an hour+ and Query Store hit the 15 Minute flush interval and now it is in conflict with the users application for resources. I can't work out what the scenario really is and how it impacts the user application. How do I justify my decision for the value I set?

Answer 1

Is query store data written to the log? If so would a restore let us recover any Query Store data, that had not been pushed to disk?

Query Store is persisted to disk, so it survives a crash. But Query Store is also Async, it means if the data has not been persisted and a crash happens in the meantime, the data will be lost. Although, if SQL Server must be restart because of a maintenance routine you could force Query Store to be flush using the procedure: sp_query_store_flush_db.

If I have an AlwaysOn AG, do the secondaries get the Query Store data, real time or are they dependent on it actually getting written to disk on the primary? Rephrased if my Query Store data has not been written on the primary for 15 minutes, and the primary gets unplugged will the secondary capture it?

Secondary replicas get the query store data from PRIMARY ONLY. You cant do force plans,etc on secondary. Since data is written in Async fashion, there will be data loss.

Can a Query Store disk write have wait conflicts with users OLPT data? If so what are they?

I dont get what you mean by wait conflicts. I have not see any conflicts on a very busy OLTP system handling 60K trans/sec.

What does changing the flush_interval_seconds value really do?

As per BOL - Defines period for regular flushing of Query Store data to disk. e.g. how often the data from memory is flushed onto disk automatically rather than you running sp_query_store_flush_db manually.