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';

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 */
      WHEN [q].[object_id] = 0 THEN 'Ad-hoc'
      ELSE OBJECT_NAME([q].[object_id])
   END AS [Object],
   [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;

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.

T-SQL Tuesday #99: Life Outside SQL Server

T-SQL Tuesday

T-SQL Tuesday is a blog party held the second Tuesday of each month.

I am late to the party (I am often late to the party, just ask Aaron Bertrand, this month’s host).  I chose door #1 for this month’s T-SQL Tuesday Dealer’s Choice party.

If you follow me on Twitter, you know I’m a runner.  You might also know that I like spin.  And maybe you know that I’m a spin instructor at Psycle, a studio here in Ohio?

The bike...

The bike…












My regular class is on Tuesday mornings, but I sub for other instructors when my schedule allows.  Teaching spin is big in some places – like Soul Cycle which some say started the whole spin craze – to the point where instructing is a full-time job and it includes benefits.

I’m not in it for the money.

The extra cash is nice, but what I love is watching people evolve, and get better.

I’m a coach at heart.

I have found that I love not thinking about how I’m doing, but rather thinking about how to get someone else to do their best.

The studio...

The studio…










This is why I teach

Both spin, and SQL Server.  Off the bike my favorite course is IE0, for the Accidental/Junior DBA.  The first day everyone is pretty quiet – they don’t want to share what they don’t know.  By the last day everyone has realized that no one is an expert, and they will tell stories and ask the fun “why” questions.

This happens in spin.

People show up the first ride and they are afraid they’ll be the worst rider in the room.  A few rides later and they’re asking questions about their bike set up, or sharing how they’re feeling about class.  Weeks, months, even years go by and you see those individuals get stronger, faster, more efficient…and in some cases become instructors themselves.

Rock Hall Ride (thanks Colleen!)

Rock Hall Ride (thanks Colleen!)












That happened to me.

I started riding to become a stronger runner, but I found that I loved the dark room, loud music, and sanctity of the spin studio just as much as I loved the fresh air, sunshine, and the sound of my feet running.  It is, at our studio, therapy on a bike.  Some may scoff at the notion, others attend and find it isn’t their thing.  And that’s cool.  You do you, you find your thing and go all in, and spread that light wherever you can.  But if you find yourself in Cleveland and you’re up for a ride, let me know 🙂