Search code examples
sql-serverperformancesql-query-store

I don't understand how some parameters of Query Store in MS SQL Server works

According to official documentation: In sys.database_query_store_options we have options which can adjust Query Store workflow and performance.

From documentation:

"flush_interval_seconds - The period for regular flushing of Query Store data to disk in seconds. Default value is 900 (15 min)"

"interval_length_minutes - The statistics aggregation interval in minutes. Arbitrary values are not allowed. Use one of the following: 1, 5, 10, 15, 30, 60, and 1440 minutes. The default value is 60 minutes."

And now i have a problem:

  1. If Query Store flush data to disk every 15min, why do i see query in QS tables in seconds after execution? As i understand QS tables are 'permanent' and they are stored in data base (on disk), so how does this parameter (flush_interval_seconds) work?

  2. The same thing about interval_length_minute - when i saved QS output after 1 minute from last query execution and after 61 minutes i realised that they are more less the same, so what about this aggregation?

Solution
    1. flush_interval_seconds - The period for regular flushing of Query Store data to disk in seconds. That means flushing from memory to disk so that the information wouldn't be lost after server restart. Before the flushing you just read info from memory.
    2. interval_length_minute - this is aggregation interval for query runtime statistics. The lower it is the finer granularity of the runtime statistics becomes.

    None of the options sets a period after which the info will be available.