Query Store Fix in SQL Server 2017

There is a Query Store fix in the latest Cumulative Updates of SQL Server 2017, and if you are keeping current with the latest cumulative updates, there’s a script you need to run if you’ve installed CU2 at any point.

First, if you are still on CU2, you might want to consider upgrading to CU4 (released February 17, 2018).

Second, if you previously had CU2 installed (and are now on CU3 or CU4), you want to make sure that you’ve run the script included with CU3 and CU4 release notes.

The script removes plans from Query Store that were captured when running CU2.  If you want to know for certain whether your database is affected before you execute the script, you can run the following query against the databases with Query Store enabled:

/* execute against EACH database that has Query Store enabled */
SELECT COUNT([plan_id])
FROM [sys].[query_store_plan]
WHERE [engine_version] = '14.0.3008.27';
GO

As an aside, if you aren’t using Query Store, notice that one of the things it captures for the plan is SQL Server version…how cool is that in terms of testing, upgrading, and troubleshooting?

If you have plans from CU2, they need to be removed from Query Store, and this is done in the script using the sys.sp_query_store_remove_plan function.  Understand that in addition to removing the query plan, it will also remove the runtime stats tied to that plan from Query Store.  But, more important than that, if that plan was forced for a query, it will be un-forced before it is removed.

Therefore, before you run the script from Microsoft, I strongly recommend you not just check to see if you have plans from CU2, but you also look to see if any of those are forced:

/* execute against EACH database that has Query Store enabled */
SELECT
   [p].[query_id],
   [p].[plan_id],
   CASE
      WHEN [q].[object_id] = 0 THEN 'Ad-hoc'
      ELSE OBJECT_NAME([q].[object_id])
   END AS [Object],
   [qt].[query_sql_text],
   [q].*, TRY_CONVERT(XML, [p].[query_plan]) AS [QueryPlan_XML]
FROM [sys].[query_store_plan]  [p]
JOIN [sys].[query_store_query] [q]
   ON [p].[query_id] = [q].[query_id]
JOIN [sys].[query_store_query_text] [qt]
   ON [q].[query_text_id] = [qt].[query_text_id]
WHERE  [engine_version] = '14.0.3008.27'
   AND [p].[is_forced_plan] = 1;
GO

This script will list any queries that have forced plans, and if they are part of an object (e.g. stored procedure) it will also list the object name.  If no rows return from this query, then you don’t have any forced plans which are affected and you can run the script from Microsoft.

If you do have any queries with forced plans, I recommend that you save a copy of the forced plan as a .sqlplan file, so that you have documentation of what plan was forced.  There are two ways to do this:

  1. Within the Query Store page for the user database in SSMS, run the Queries with Forced Plans report. In the grid, sort the list by query_id.  For each query_id identified by the query above, select the forced plan, then right-click on it and save.
  2. Within the Query Store page for the user database in SSMS, open the Tracked Queries report. Individually enter each query_id identified by the query above, select the forced plan, then right-click on it and save.

Once you have saved off a copy of every forced plan, then you can execute the script from Microsoft.

Note: If you are using Automatic Plan Correction, this will also un-force those plans (which would have been forced automatically).

At this point, queries that previously had a forced plan may have performance issues.  It’s possible that changes in your data distribution, changes in statistics, or perhaps even changes in the optimizer have caused a different plan to be generated that might be acceptable performance-wise.  If that is not the case, there is no ability to import plans into Query Store.  Each query that had a forced plan, that doesn’t get a new, “good” plan, may need to be run again to get the same plan into Query Store.  One of the easiest things to do is to find compiled values within the plan using the ParameterCompiledValue attribute, then re-run the query using those values.  You can then use data from Query Store, and compare against the saved plan, to verify it’s a consistent/stable plan.

Finding input parameters from the query plan
Finding input parameters from the query plan

 

 

 

 

 

 

 

 

 

 

 

 

 

I definitely recommend updating to the current Cumulative Update (and this is a general recommendation, not just because of the issue I’ve discussed here), and part of preparing for that upgrade means checking to see if you’re affecting by this issue, and addressing it as part of the upgrade process, rather than down the road.

One thought on “Query Store Fix in SQL Server 2017

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

A Fond Farewell

If you haven’t guessed from the title, I’m writing this post because I am leaving SQLskills. This Friday, January 14th, is my last day, and

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.