Share via

statistics callection intervel in query store ?

rajesh yadav 291 Reputation points
2025-09-18T07:36:26.6366667+00:00

Statistics Collection Interval: Defines the level of granularity for the collected runtime statistic, expressed in minutes. The default is 60 minutes. Consider using a lower value if you require finer granularity or less time to detect and mitigate issues. Keep in mind that the value directly affects the size of Query Store data. Use SQL Server Management Studio or Transact-SQL to set a different value for Statistics Collection Interval:

q1)does this mean that query store would run after 60 seconds ,please explain?

Stale Query Threshold (Days): Time-based cleanup policy that controls the retention period of persisted runtime statistics and inactive queries, expressed in days. By default, Query Store is configured to keep the data for 30 days, which might be unnecessarily long for your scenario.

Avoid keeping historical data that you don't plan to use. This practice reduces changes to read-only status.

Q2) if 30 days are not expired and 1000 mb size has not reached, does it change the read only status , question is when this state query threshold is going to change the status to read only.

is this sate query is any way related to Size Based Cleanup Mode?

  1. if QUERY_CAPTURE_MODE = CUSTOM: is set with
  2. STALE_CAPTURE_POLICY_THRESHOLD = 1 hour with
  3. EXECUTION_COUNT=30
  4. that meas if a query executes 30 times in an hour then only it will be sotred.
  5. my question is if a query is not stored as it executed 29 time only. then will get the benefit of "Quickly find and fix a plan performance regression by forcing the previous query plan. Fix queries that have recently regressed in performance due to execution plan changes."
  6. an other question is above quote unquote means it does it automaticlay or we have to check the regression tab to force a plan?
  7. when 1000 size is reached it deletes 20% of queires and when 30 days expires it cleans up, my question is if i have foreced some queries ,does it stores them or i have to force them again?
SQL Server Database Engine

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 127.7K Reputation points MVP Volunteer Moderator
    2025-09-18T21:02:49.1066667+00:00

    q1)does this mean that query store would run after 60 seconds ,please explain?

    No, that is not what it says.

    Query Store does not save data about individual executions, but data for executions of the same plan within an interval is aggregated into one row. The default length of an interval is one hour. There can be advanced scenarios where it may make senses to change the interval. Or if you want to test certain behaviour, and you don't want to wait an hour for the next interval. For normal production use, one hour is a good choice.

    Q2) if 30 days are not expired and 1000 mb size has not reached, does it change the read only status , question is when this state query threshold is going to change the status to read only.

    The documentation for ALTER DATABASE SET Options says this:

    MAX_STORAGE_SIZE_MB Determines the space issued to the Query Store. MAX_STORAGE_SIZE_MB is type bigint. The default value is 100 MB for SQL Server (SQL Server 2016 (13.x) through SQL Server 2017 (14.x)). Starting with SQL Server 2019 (15.x), the default value is 1000 MB. MAX_STORAGE_SIZE_MB limit isn't strictly enforced. Storage size is checked only when Query Store writes data to disk. This interval is set by the DATA_FLUSH_INTERVAL_SECONDS option or the Management Studio Query Store dialog option Data Flush Interval. The interval default value is 900 seconds (or 15 minutes). If the Query Store has breached the MAX_STORAGE_SIZE_MB limit between storage size checks, it transitions to read-only mode. If SIZE_BASED_CLEANUP_MODE is enabled, the cleanup mechanism to enforce the MAX_STORAGE_SIZE_MB limit is also triggered.

    my question is if a query is not stored as it executed 29 time only. then will get the benefit of "Quickly find and fix a plan performance regression by forcing the previous query plan. Fix queries that have recently regressed in performance due to execution plan changes."

    If the query is not captured in Query Store, we cannot force plans for it.

    an other question is above quote unquote means it does it automaticlay or we have to check the regression tab to force a plan?

    There is a feature Automatic Plan Correction which performs this automatically. However, this feature is only active in Azure SQL Database, not on-prem. See further free.blessedness.top/en-us/sql/relational-databases/automatic-tuning/automatic-tuning?view=sql-server-ver17.

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.