Query Store Best Practices for Mission Critical or Busy SQL Server

Query Store feature helping lot of SQL Admin in isolating top resource consuming queries quickly and also troubleshooting plan choice regressed scenario which is difficult to isolate or time consuming process. Now with powerful report and using few clicks you can check top resource consuming query and force optimum plan. In this article I would like to highlight some of best practices if you are enabling Query Store on mission critical and high transactions Server

So as you already know that Query Store have following capture Mode for data collection:

All – Captures all queries. This is the default option.

Auto – Infrequent queries and queries with insignificant compile and execution duration are ignored. Thresholds for execution count, compile and runtime duration are internally determined.

None – Query Store stops capturing new queries.

QDS store runtime stats in cache until they are persisted on disk asynchronously, you could track memory usage using memory clerk USERSTORE_QDSSTMT & CACHESTORE_QDSRUNTIMESTATS. So write operation from cache to disk is very fast and you may not see large memory allocation against these memory clerks however workload with a lot of frequent ad hoc/unparameterized queries would make cache to grow.

If are running QDS with capture mode “ALL” on busy Server or mission CRITICAL Server then consider following best practices to save cache memory and reduce database recovery time:

1. Considering switching capture mode to “Auto” if you are not troubleshooting any ongoing issue for which you needs to capture all queries stats.
2. If your workload contain lot of ad-hoc batch then considering using “auto” capture mode as capturing detail for ad-hoc workload will not provide much benefit.
3. Enable Trace Flag 7745 which Forces Query Store to not flush data to disk on database shutdown. Note: Using this trace may cause Query Store data not previously flushed to disk to be lost in case of shutdown. For a SQL Server shutdown, the command SHUTDOWN WITH NOWAIT can be used instead of this trace flag to force an immediate shutdown.
4. Enable Trace Flag 7752 to Enables asynchronous load of Query Store. Note: Use this trace flag if SQL Server is experiencing high number of QDS_LOADDB waits related to Query Store synchronous load (default behavior).

References

Best Practice with the Query Store /en-us/sql/relational-databases/performance/best-practice-with-the-query-store

Query Store Usage Scenarios /en-us/sql/relational-databases/performance/query-store-usage-scenarios

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql

Vikas Rana | Twitter | Linkedin | Support Escalation Engineer
Microsoft India GTSC

• Anonymous
  October 23, 2017
  Very informative article, thanks Vikas for sharing
• Anonymous
  October 24, 2017
  Hi Vikas,Useful post.I wonder if it worth clarifying that Trace Flag 7752 is related to asynchronous load of Query Store only on startup of SQL Server, people may read this and think it means that the normal writes to the store are not asynchronous even though they are, and rush to enable the flag.Regards,Matthew.