The latest CUs for SQL Server 2016 and 2017 contain some important Query Store fixes that I thought worth mentioning for those of you on either version or those of you looking to upgrade. As of this writing, the current CU for SQL Server 2016 SP2 is CU5, and for SQL Server 2017 it is CU13. Many times we see fixes that make it into a SQL Server 2017 CU ported back to a SQL Server 2016 build. Interestingly enough, there are some Query Store fixes in 2016 CUs that are not in 2017 CUs. I don’t know if that’s because the issues do not exist in 2017, or if it’s just that they have been fixed yet in 2017. I’m planning to update this post if the fixes are added down the read. So here we go, in descending CU order…
SQL Server 2017 CU13 – FIX: A dump file may be generated when you run the DML internal plan on Query Store enabled database in SQL Server 2017
This will occur for anyone using Automatic Plan Correction (which means you will be on Enterprise Edition), as noted by having FORCE_LAST_GOOD_PLAN enabled. You can read more about Automatic Plan Correction here (I’m a fan, it’s pretty cool and very helpful for those with small DBA teams and lots of databases, or those DBAs who just have too much on their plate and are constantly putting out fires).
SQL Server 2017 CU11 and SQL Server 2016 SP2 CU5 – FIX: Transactions and log truncation may be blocked when you use Query Store in SQL Server 2016 and 2017
I’m aware of multiple companies that have run into this issue and I’m glad to see that the fix was ported back to 2016. I highly recommend getting up to this CU if you can, as the resolution requires a restart (I’ve heard that killing the session_id also works but I haven’t seen that). Also note that the command referenced in the KB article is ALTER DATABASE <databasename> SET QUERY_STORE_CLEAR, but I have also seen this when trying to change one of the Query Store options (e.g. changing size or retention days). I am pretty sure it’s any ALTER DATABASE statement that changes Query Store configuration.
SQL Server 2016 SP2 CU5 – FIX: Query Store enabled database takes long time on startup after you apply cumulative update for a SQL Server version
Note that this is applicable to systems with Availability Groups, and this is a fix that is not listed for any 2017 CU…I’ll keep watching to see if it shows up.
SQL Server 2016 SP2 CU4 – FIX: Access violation when SQL Server 2016 tries to start Query Store Manager during startup
This is also a fix that is not listed for any 2017 CU…
SQL Server 2017 CU5 – FIX: Access violation occurs when Query Store collects runtime statistics in SQL Server 2017
I don’t see this fix in any SQL Server 2016 CU, but I’ll keep watching.
SQL Server 2016 SP2 CU2 – FIX: Slow performance of SQL Server 2016 when Query Store is enabled
There were many performance-related improvements for Query Store in SQL Server 2017, and huge props to the SQL Server team for getting these back-ported to 2016. At a bare minimum, this is the build you should be running, but I would rather see you on CU5 at this point.
Note: If you installed CU2 for SQL Server 2017 at any point, please read: Query Store Fix in SQL Server 2017 for an explanation of what you need to do when you apply a higher CU and why.
You may look at this list of fixes and be concerned. Don’t be. These fixes are a good thing! They tell me that more and more people are using Query Store (which is great) and while they have run into issues, I believe it’s because these are bigger systems (see the access violation fix which is for systems with 256 logical cores) or they are interesting/edge-case workloads, both of which probably don’t exist in Azure, and may be why these issues haven’t been seen previously. I remain, as always, a big proponent of Query Store. If you’re seeing something interesting feel free to comment here or drop me an email. I’ve had some folks do that and I was able to confirm they were seeing a bug and let them know that it would be fixed soon. I’ve had one or two other cases where it’s an issue I haven’t seen and I recommended contacting Product Support. Either way, if you’re unsure about Query Store I have lots of resources to get you started, to get it configured properly, and I answer the question “what about performance?!”. I hope this helps!
- Answering the Question “What Happened?” with Query Store (video)
- Plan Stability Through Plan Forcing (video)
- Query Store Settings
- Query Store Performance Overhead: What you need to know
- ALTER DATABASE SET QUERY_STORE command is blocked
9 thoughts on “Important Query Store Fixes – January 2019”
I have become a big advocate of Query Store myself too, it helps me a lot with custom application stabilization, troubleshooting, code optimization etc. However these ongoing problems and their fixes still are a problem and I see it as a trend of certain quality problems within SQL Server and I see it as a worsening problem lately. If you check the list of fixes within a CU it is frightening what major problems contained within and amazing to see all the stability problems, how the product got released in the first place. A feature like Query Store should not trigger a stability problem in Production systems it’s unacceptable and Microsoft should do better quality assurance as seemingly something is going on. Production patching is still a major problem at customers and you can’t just apply a new CU/SP just because you turned on a feature that impacts your environment. I can hardly see any customer using this feature unfortunately, but if you turn it on and it causes additional and major problems customer trust will suffer for sure. I tend to review *all* known and released fixes *before* using any new features within SQL Server nowadays with extra care and let the customer know about these if they are lacking the fixes.
Thanks for the note. A couple things to consider. First, while Query Store was developed with telemetry from customer systems in Azure, not every scenario is represented in Azure. There are many workloads which don’t work well in Azure currently, and as such, they probably never ran with Query Store enabled. Now, with the increased adoption of SQL Server 2016 and 2017, more customers with a much larger variety of workloads are using the feature, and they are hitting scenarios that are very difficult to recreate and test within the Microsoft environment. I think Microsoft has been extremely responsive to this issues and they have continued to back-port fixes to SQL Server 2016, which is excellent.
Second, the scenarios where I have seen performance change with Query Store enabled are ones with adhoc workloads. I talk about this in my Query Store Performance post: https://www.sqlskills.com/blogs/erin/query-store-performance-overhead/. As a result, there are some workloads where I do *not* recommend enabling Query Store. This isn’t a problem with Query Store, it is related to a problem with the workload. For better or worse, you can compensate for such a workload with hardware, but when enabling Query Store on something that’s high volume and adhoc you cannot just add hardware to fix it.
I see MANY customers using this feature, and I’ll continue to recommend it. I believe customers need to be educated as to what potential issues can exist with ANY feature – Query Store, In-Memory OLTP, Columnstore, etc. You can create poor performance in many different areas of SQL Server – for example, regular nonclustered indexes – if you have the right scenario.
Hi Erin. Quick one for you as I’ve tried everything else (even MS): my query store stopped working in sql 2016, sp2, cu 4, after I failed over a few databases due to maintenance. Even if I turn query store off and on it does not collect more data. It does nothing actually. If I try to drop data nothing happens. If I try to fix a plan it says query store is not enable. I can access data collect prior to the maintenance. Have you ever seem such problem? Any idea how to troubleshoot it or resolve it please? I’m guessing I might have caused it by letting Sql Server failover the dbs automatically during the maintenance, but I cannot get an outage now to try things in production for another month…Just now that I started liking it!
What’s the state of Query Store? If you query sys.database_query_store_options, what do you get in the output? (I’m mostly interested in desired_state and actual_state, but feel free to share the entire output.)
Hello Erin. We understood how to fix the issue where Query Store stops collecting data (and forcing plans!) when the server is restarted (due to maintenance) and mirror is being used: we need to failover the databases to secondary and fail them back to primary. Not elegant, but it solves our problem until we move away from mirroring or MS fixes it.
Did you upgrade to the latest CU for 2016 SP2? I believe the issue you’re seeing might have been fixed in a later CU. I am glad you have a work around, even though it isn’t, as you said, very elegant.