In the previous post, we learned about the basics of the Query Store. In this session, we will learn how Query Store works behind the scenes.
When a query is submitted to the SQL Server, it checks if there is already a plan available for the query in the procedure cache. If the plan is not found, Query goes through the compilation and optimization phases.
The query is checked for recompilation. During this phase, if the plan in the procedure cache is not similar to the forced plan for that query (If the plan has been forced for that query), the query goes through the recompilation and a similar forced plan is generated (i.e, the forced plan is applied just like the PLAN Hint). If the plan in the cache is the same as the forced plan, it will go ahead and use the same plan.
Before the query executes, the query text and the query plan are stored in the plan store which is in-memory. This information is stored asynchronously. Once the query executes, the run time stats information is captured in the runtime stats store which is in memory as well and stored asynchronously. The runtime stats are captured in memory and flushed out to the Query Store schema asynchronously as per the Data Flush Interval (Minutes) settings for the Query store. The query text and the query plan information are sent to the Query store schema with minimal latency (Source: Microsoft). If there is memory pressure, the runtime stats information can be sent to the disk before hitting the data flush interval.
Know the Query store settings and what each setting actually mean:
When you enabled Query Store, you cannot always depend on the defaults. It is very important to know what each of the setting means and why it is important to set it right based on your workload.
DATA_FLUSH_INTERVAL_SECONDS (type: bigint) determines how often the data that has been collected in the memory is flushed out to the disk and stored on the primary data file of the database and this is depended on the interval we chose. By default, the interval is 15 minutes.
CLEANUP_POLICY determines how long you want the Query Store to save the data. The default is 30 days (STALE_QUERY_THRESHOLD_DAYS) which can be changed as per your needs.
MAX_STORAGE_SIZE_MB (bigint) determines the amount of space the Query Store can use. By default, it is 100MB for SQL Server 2016/2017. For SQL Server 2019, the default size is 1GB. Remember that, the size of the max storage size is checked when ever the data is flushed to disk based on the interval settings. If the size is full meanwhile, the query store will to be changed to read-only mode. If you chose the SIZE_BASED_CLEANUP_MODE is set then the cleanup mechanism is triggered automatically. Once the cleanup is completed, the Query Store will be change back to read-write (Source: Microsoft Docs)
INTERVAL_LENGTH_MINUTES is the interval where the runtime statistics are aggregated. If we capture the runtime stats for every query individually, that will fill up the Query store size pretty quickly. For that reason, the aggregate information is collected. For example, if you have a query being run 100 times in the interval of 15 minutes, there is only one entry and one row allocated for this query with the aggregated information between that interval. Lets say the interval number is 1. The next interval number 2 starts after 15minutes. For that interval number 2, if the same query runs again, it will get a single row entry no matter how many times it executes during that interval number 2.
SIZE_BASED_CLEANUP_MODE is AUTO by default. The cleanup process will be automatically triggered when the MAX size of the Query Store reaches 90%. This option helps Query store not to go to read only mode. This process cleans up the oldest queries and the least expensive data first. It is suggested not to turn off this option.
Summary:
In this blog post, we learnt about how the Query Store actually works. We are going to see the advancements made to the Query store feature in SQL Server 2022, which we are going to learn in the coming blog posts.
It is important to know what and how we setup the options and settings for the Query store. For the Azure SQL database, once the defaults are applied and enabled it is difficult to change these configuration values without effecting the Query store operations. Remember, the Query store cannot be disabled on the Azure SQL database and elastic pool.
Thanks for reading!
Resources:
- https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store?view=sql-server-ver15
- https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-ver15
- https://docs.microsoft.com/en-us/sql/relational-databases/performance/how-query-store-collects-data?view=sql-server-ver15
Deepthi Goguri's SQL Server Blog 