Microsoft maintains a list of supported trace flags and I noticed that there are two new ones related to Query Store: 7745 and 7752. The descriptions for these Query Store Trace Flags are pretty straight-forward, but for those of you not familiar with Query Store, I thought I’d provide some context and details.
When you enable and configure Query Store, one setting to consider is
DATA_FLUSH_INTERVAL_SECONDS. This setting defaults to 900 (15 minutes), and it determines how frequently Query Store data will be flushed to disk. As a reminder, some Query Store data is stored in memory as an optimization. If Query Store had to flush data to disk every time a query executed, Query Store would quickly become your biggest performance problem, therefore data is temporarily stored in memory. The
DATA_FLUSH_INTERVAL_SECONDS setting controls the maximum amount of time SQL Server will wait to flush data from memory to disk, but know that data could be flushed more frequently (also as an optimization). When I first learned about Query Store, it was my understanding that this setting was a tradeoff between “how much Query Store data are you willing to lose if your server unexpectedly shuts down” and “how much of a performance impact do you want to introduce by having Query Store write data to disk (internal tables in the user database) more frequently”? Because the Query Store data is not business critical, my advice has been that 15 minutes is a good balance. However, I now know that when you initiate a shutdown of SQL Server, by default it will wait to write that Query Store data to disk – and I’ll be honest in that I don’t know how long it will wait. In some cases (e.g. a fail over in a HA/DR scenario), you might not want to wait any longer than absolutely necessary for SQL Server to shut down. In that case, you can either use SHUTDOWN WITH NOWAIT (not what I would recommend at all) or trace flag 7745 to bypass writing any Query Store data still in memory to disk. Using 7745 means that you can lose some Query Store data.
When you start SQL Server, it loads some data from the Query Store internal tables into memory (again, this is an optimization to make specific capabilities of Query Store complete quickly). In some cases this is a small amount of data, in other cases, it’s larger (potentially a few GB), and as such, it can take seconds or minutes to load. As a result, if you execute a query before the Query Store data has finished loading, the query will not execute until the data has been loaded. To load the Query Story asynchronously, and still allow queries to execute, use trace flag 7752. With this trace flag enabled, queries can execute while the Query Store data is being loaded asynchronously in the background, however, Query Store is in a read-only state. No query information will be written to Query Store until after the data has been loaded into memory. If you don’t know if Query Store is taking a long time to load and thus impacting your queries, set up an Extended Events session that captures the WAIT_INFO event, filtering on the QDS_LOADDB wait_type, and have it run at startup (and also have a job that stop the XE session 10 or 15 minutes after SQL Server starts up – you don’t need to continuously run this session). After one or more restarts, check the XE output and see if you have any events for the QDS_LOADDB wait type, and if so, add up the total duration for the wait type and compare it against the startup duration for the instance.
Finally, know that the behavior of trace flag 7752 may become the default behavior in a future release – that’s yet to be determined – but if it did, there will ideally be a status change to Query Store to signify that during the asynchronous data load it is in a read-only state.
I always expected that I could potentially lose Query Store data, and so I’m still ok with that. When I ask SQL Server to shut down, I want it to shut down as quickly and efficiently as it always has, so I’m pretty sure I’m going to be enabling 7745.
I will also enable trace flag 7752. While I can check to see if I’m experiencing the QDS_LOADDB, if this will become the default behavior in the future, I might as well adopt it now!
Update September 2019: The behavior of trace flag 7752 will be the default behavior in SQL Server 2019.