6

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?

5

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.

  • So why choose any but the shortest option of 1 minute or 1 second? – James Jenkins Jan 27 '18 at 9:13
  • You can test the impact of flushing every 1 mins .. on a very busy oltp system, IO will be a concern where you flush too frequently. Test it and go with whatever best suits your env. – Kin Jan 27 '18 at 15:35
  • Thank you; after much (over) thinking, I am going to stay at the default value of 15 minutes, unless there is reason to change on a specific system. I think I have a handle now on the risks and benefits. If you have a very specific issue, there maybe a reason to go with any different value. But routinely there is no reason to change. – James Jenkins Jan 29 '18 at 14:12
  • I would say when doing controlled failover, just run the ` sp_query_store_flush_db` as part of failover pre steps. – Kin Jan 29 '18 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.