Plan change regression happens when SQL Database changes a plan for some T-SQL query, and the new plan has the worse performance than the previous one. SQL Server 2017 has Automatic Tuning feature that enables you to easily find plan change regressions and fix them. In this post you will see the demo script that you can use to cause plan change regression and manually fix it using new sys.dm_db_tuning_recommendations view.
If you are not familiar with plan regressions and new tuning recommendations in SQL Server 2017, I would recommend to read these two posts:
- What is plan regression in SQL Server?
- How to find query plan choice regressions with SQL Server 2017 CTP2
This would be enough to understand steps in this demo.
Our experience with plan regression recommendations has been uniformly positive so far. Those tests have been in dev and QA environments, but so far, there hasn’t been a terrible recommendation.
Starting today in Azure SQL Database and from CTP 2.0 of SQL Server 2017 wait stats per query are available in Query Store. Now you can exactly identify why and how much every plan waited for some resource. Information about wait times are persisted so you can also analyze through the history what was the problems and why queries waited for resources
This is a welcome improvement for query tuners on 2017.
The first release was published at the end of June 2017 and provides a background collection of query execution statistics in a database of your choice. The v1.0 release supports SQL Server from 2008 to 2014 and all editions (Express, Standard, Enterprise). There is also a PowerShell installer for those that are so inclined, which will grab the code from GitHub and install OQS into a specified database.
There is also a custom report which can be installed in SSMS (2016 and 2017), which will display information that OQS has collected.
This is a really cool community solution if you’re stuck on an older version of SQL Server for a while.
In SQL Server 2016, Microsoft introduced to us the Query Store. This has been proven to be a very beneficial feature especially at my job, saved the day on Christmas Eve. One of the limitations if see is when you have server with several databases on it trying to find the one that has the problem. So until Microsoft gives use a better tool or someone builds one I wrote a quick PowerShell function to query across all the databases and return data to a grid so you can sort and find that troublesome query.
To call it simply provide the server, which metric you want, the top number of queries to return, who many hours to look back, and the minimum execution count.
This is one of two issues I have with Query Store, the other being that its data gets saved to the primary filegroup without any recourse. Click through for the script.
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.
Click through for the descriptions of these two trace flags.
A small but nice little feature I have been using recently can be found within Query Store.
Let’s say you have 2 Plan IDs for a query, naturally you want to view the execution plan for the different plans. In the past I did it a manual way, by that I mean by individually clicking on the Plan ID to see the plan then moving on to the next one.
It is much easier to explain with some images.
Click through to see those images.
If we rerun the query and then take a look at the first operator in the execution plan, we can see that the Plan Guide is in use… and that the query hash has changed. It no longer matches the original query. Now it matches the query that included the query hint. This actually makes perfect sense. The Plan Guide is basically changing the query from the first example above, into the second.
Now, what happens when we toss in the Query Store
The query behavior is exactly what you want, but some of the metadata is no longer correct.
In CTP2.0 version is added new system view sys.dm_db_tuning_recommendations that returns recommendations that you can apply to fix potential problems in your database. This view contains all identified potential performance issues in SQL queries that are caused by the SQL plan changes, and the correction scripts that you can apply. Every row in this view contains one recommendation that you can apply to fix the issue. Some of the information that are shown in this view are:
Id of the query, plan that caused regression, and the plan that that might be used instead of this plan.
Reason that describes what kind of regression is detected (e.g. CPU time for the query is changed from 17ms to 189ms)
T-SQL script that can be used to force the plan.
Information about the current plan, and previous plan that had better performance.
In the “surgical scalpel to chainsaw” range of query tuning options, this rates approximately guillotine. I think it’ll be a very useful tool for finding issues, but it wouldn’t be wise to start lopping off all the heads just because the optimizer tells you to. In this context, I imagine this DMV to be about as useful as the missing indexes DMV and for the same reasons.
I love the questions I get while I’m presenting because they force me to think and learn. The question in the title is one I received recently. The answer, now that I’m not standing in front of people, is easy. Of course the space is not pre-allocated. Query Store tables are just system tables. They have a limit on how big they can grow (100mb by default), but that space isn’t going to be pre-allocated in any way. The space will just get used as and when it’s needed, just like any other system table. However, don’t take my word for it, let’s prove that.
Read on for the proof.
A question came up recently about plan guides and compatibility mode, and it got me thinking about forced plans in Query Store and compatibility mode. Imagine you upgraded to SQL Server 2016 and kept the compatibility mode for your database at 110 to use the legacy Cardinality Estimator. At some point, you have a plan that you force for a specific query, and that works great. As time goes on, you do testing with the new CE and eventually are ready to make the switch to compatibility mode 130. When you do that, does the forced plan continue to use compatibility mode 110? I had a guess at the answer but thought it was worth testing.
There are some interesting results here.