Query Store Fundamentals: Did you know?

Did you enable Query store or have a plan to enable this amazing feature on your databases? There are many points that need to be remembered for effectively using Query Store on your databases. I have gathered some of the points as I learn about the Query Store. Hope the below list will be informative to you as well.

1. Query Store feature cannot be disabled in Azure SQL Database single database and Elastic Pool. When you are using on-prem environment, you need to enable on database you would like to collect the query information.
2. Use TF 7745 to avoid writing QS data to disk before SQL Server shutdown or during the failover. The general behavior of SQL Server is to write the Query Store data collected to disk before the SQL Server shutdown. This can take time to flush all collected data to disk and restart the SQL Server or during the failover situations. Using TF 7745 can cause the data collected be lost until the flush out interval period mentioned in the settings. If you can afford losing the data for that period of time, SQL Server restart or failover can take less time. SHUTDOWN WITH NOWAIT can also be used in the place of TF 7745 during SQL Server shutdown.
3. Use TF 7752 to avoid synchronous load of Query Store while database is in the process of starting up and be available online. Regular behavior of synchronous load can take time to recover the Query Store in full. Until the Query Store is completely recovered, Query executions will be in hault and cannot execute. This is not a good situation if your database is huge. By using the TF 7752, asynchronous load can happen later after database becomes online. This trace flag is enabled by default in SQL Server 2019
4. For in-memory OLTP, run time stats information is collected only when you enable using sys.sp_xtp_control_query_exec_stats (Transact-SQL). This setting needs to be enabled on each server restart as it does not persist on SQL Server restarts.
5. Did you know what the circle, square and triangle mean in Query Store? Size of the circle also have a message. Circle represents the successful execution, the more number of executions has the bigger size, rectangle represents a cancelled execution, triangle means an error caused during execution. For the below example, I executed the stored procedure 20 times, so the size of the circle you see is big and I stopped the same query execution in between to show a rectangle.

6. If your workloads have many ad-hoc queries, use Optimize for Ad hoc Workloads option to save cache memory as recommended by Microsoft. There is also a force parameterization option that you can enable at the database level to parameterize the ad-hoc queries so they reuse the plans. This can be an alternative option for Optimize for Ad hoc workloads. Randolph West and BrentOzar have explained the reasons why one option is better than the other and when to use one over the other. Thanks to Randolph and Brent for your detailed explanation! Check these posts for sure to know more!

7. Max plans per query are set as 200 execution plans per query by default. You can change this setting based on your workload and requirements.

8. If MAX_STORAGE_SIZE_MB is reached, Query Store goes into read-only mode. Remember, the storage size is only checked at the flush-out intervals. If the storage size is reached to the maximum limit before reaching the flush-out intervals, that’s when the Query store goes to read-only mode. In read-only mode, the Query Store can read the forced plan information and apply a forced plan but cannot write any information into the Query Store.

9. To avoid reaching the maximum storage size, make sure the “size-based cleanup mode” is always set to AUTO which is a default. This option when set in AUTO mode avoids Query Store from going to read-only mode by deleting the old data from Query Store once it reaches 90% capacity of its Query Store maximum storage size limit. It clears up until it reaches 80% of space and this avoids the need of changing to read-only mode.

10. The QS QUERY_CAPTURE_MODE, AUTO will avoid ad-hoc queries with small resource consumption by default. This is recommended mode by default. This mode is enabled by default in Azure SQL Database, SQL Server 2019

11. Deleting plan cache data doesn’t delete the Query Store data. Query Store data is persisted upon the SQL Server restarts. The cleanup policy can be set by using the STALE_QUERY_THRESHOLD_DAYS setting.

12. Last by not least, there is a query that can delete the ad hoc queries from the Query Store. This will avoid query store space from filling up quickly. Follow this article to find the query that can delete ad-hoc queries

Thanks for reading!

Resources:

1. https://docs.microsoft.com/sql/relational-databases/performance/best-practice-with-the-query-store
2. https://bornsql.ca/blog/dont-optimize-for-ad-hoc-workloads-as-a-best-practice/
3. https://www.brentozar.com/archive/2018/03/why-multiple-plans-for-one-query-are-bad/
4. https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver15
5. https://docs.microsoft.com/en-us/sql/relational-databases/performance/using-the-query-store-with-in-memory-oltp?view=sql-server-ver15
6. https://docs.microsoft.com/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store
7. https://docs.microsoft.com/sql/relational-databases/performance/best-practice-with-the-query-store