James Jenkins (imported from SE)
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](https://www.sqlskills.com/blogs/erin/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](https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store) does not even mention it. Microsoft's [sys.database_query_store_options (Transact-SQL)](https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/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](https://docs.microsoft.com/en-us/azure/sql-database/sql-database-operate-query-store) > 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?