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.
7745
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.
7752
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 Store 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.
My thoughts
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.
19 thoughts on “Query Store Trace Flags”
Wow, these are very important. I can’t believe they aren’t the default from the start.
SQL Server is a great product, but it’s a shame that the most optimized options like these are not used by default. Obviously SLA has a much higher priority than some query store data! I would love to see the person who made the wrong decision get fired!
Tom-
Really? You think someone should be fired over this? I think that’s a bit extreme. There are always things to sort out with a V1 release of a feature – no person or team is perfect.
Regardless, there is a way to address it, so I think that’s acceptable going forward.
Erin
I know we can set these traces flags as startup parameters, but since that requires a restart, do you know if we can enable these just using DBCC TRACEON? (And then, of course, add them to the start up parameters so they persist through a reboot.)
You can enable the trace flags globally using DBCC TRACEON, so they are enabled until the next instance restart (but obviously you want them as startup parameters so they persist).
Hi Erin,
Do you know if the defaults you mention were implemented with the SQL 2017 changes?
Hi Ray-
They were not implemented in SQL Server 2017, and I don’t see that it’s in the next release (2019) either.
Erin
Hi
I have configure query store and database under always on.
After restart log file growing and out of control log file.
Query store retion period 5 days.
Any idea why log file out of control ?
How to control?
I take backup every 10 minutes.
Vipul-
What version and build of SQL Server are you running? There is a known issue with log truncation being blocked when using Query Store which is fixed in SQL Server 2017 CU11 and SQL Server 2016 SP2 CU5. See: https://support.microsoft.com/en-us/help/4461562/transactions-and-log-truncation-may-be-blocked-when-using-query-store
Erin
Erin,
are u familiar with trace Flag 7770? We are in sql 2016 sp2 cu12 and have a lot of ad hoc queries. We had some performance issues (cmenthread) and MS engineer pointed out it was related to the query store. He introduced this trace flag 7770 to address our heavy plan cache consumption from those ad hoc queries. Just want to hear your thought on this trace flag.
Wing
Wing-
I am not familiar with TF 7770, I have no idea what it does, and it is not listed as a supported trace flag.
If you have a highly ad hoc workload on SQL Server 2016, you may not be able to use Query Store – you might have better luck on SQL 2019 where you have the CUSTOM capture mode and you can set the thresholds for query collection.
Erin
Erin, for trace flag 7745, is it still applicable in SQL 2022 QS so I need to enable the trace flag?