If you’re working with Azure SQL Database or you’ve moved into SQL Server 2016, one of the biggest new tools is the Query Store. It provides a mechanism of capturing query performance over time and persisting it with the database. You also get the execution plans for those queries. Finally, you can choose to have the Query Store override execution plan selection by use of Plan Forcing. I’ve written about Query Store a few times:

Query Store, Force Plan and “Better” Plans
Query Store, Force Plan and Dropped Objects
Precedence Goes to Query Store or Plan Guide
Query Store, Forced Plans and New Plans
Query Store and Optimize For Ad Hoc
Query Store and Recompile
Finding Your Query in Query Store
Removing All Query Store Data
Monitor Query Performance

OK, maybe I’m a little obsessed with Query Store. I can’t help it. It’s one of the most exciting developments in query tuning within SQL Server in quite some time. If you’re working with Azure SQL Database or SQL Server 2016, you should be using it. If you’re not, you’re missing a trick. If you’re preparing to move into Azure SQL Database or SQL Server 2016, you should learn this now.

Why?

You’ll note that I’ve written about Force Plan or Plan Forcing multiple times. The single biggest reason for this is because of one of the most common problems with query performance in SQL Server is when parameter sniffing goes bad and Force Plan lets you fix bad parameter sniffing without changing your code. That’s a huge win.

But…

And you knew there was a but coming.

But, what else does Force Plan do for you? What if you never experience bad parameter sniffing (you do, but I’m not going to argue the point)? Is there something else that Force Plan can do for you? Heck yes! The whole point of creating the Query Store was in order to address Plan Regression. What the heck is plan regression? When Microsoft makes any change to the Query Optimizer, and those changes come all the time, it’s possible that you might see a change in your execution plans. Most of the time, it’s going to be a positive change. That’s why they’re changing the Optimizer after all, to improve it. However, sometimes, you’re benefiting from the old behavior of the Optimizer and that new plan doesn’t work as well as the old plan. This is plan regression. When Microsoft changed the Cardinality Estimation engine in SQL Server 2014, more than a few people experienced the new estimator giving row estimates that resulted in a different execution plan that didn’t perform as well as the old plan. This is plan regression. What to do?

QUERY STORE! Ta-da!

Testing For Regression

Here’s what you’re supposed to do. You’ve decided to finally retire that SQL Server 2000/2005/2008R2/2012 database and join the future in SQL Server 2016 (what I’m about to describe actually doesn’t help for migrations to Azure SQL Database). Good. Backup your database (always, backup your database), restore it to that shiny new 2016 instance, but leave the compatibility mode in whatever version you’re coming from. Don’t change it to the new compatibility mode yet. This means it will use the old Cardinality Estimator and not implement new Query Optimizer choices, yet. Enable Query Store because it’s not on by default. Run a bunch of tests against your database. Run all the queries you can with as many different parameter values that you can. Now, change the compatibility level to 2016. Run the tests again. Use the nifty GUI (actually, I dislike the GUI, but it’s kind of cool to see this) to run the Regressed Queries report. That will show you queries that have run slower over time as you can see below (click to embiggen):

I have highlighted the fourth query in the report because the first three all have identical plans, even though the execution times varied for those queries (blocking, resource contention, who knows). That one has two different plans, which I can see by the plan listing:

Now I know that my plan changed over time. The trick is, investigate the plans and determine which plans changed because of the change in the compatibility level. You can tell this by clicking on the properties of the first operator (the plans shown in the report can be explored just like any other plan within SSMS) and look for the CardinalityEstimationModelVersion property:

Any plans that you like, that run better, based on the old model, you can choose, after testing and due consideration, please be careful with this, to force the plan simply by clicking a button:

I can’t emphasize enough, be sure that you have tested the plan choice adequately. You can easily unforce a plan, but that may only occur to you after you have performance issues in production. This is a great new toy, uh, I mean tool, but you have to be careful when using it. You can force a plan, but it can be the wrong plan. Here are the steps in simple order:

1. Backup the database on the old system
2. Restore it to the new system
3. Enable Query Store
4. Run lots of tests
5. Update the compatibility level
6. Run the lots of tests again
7. Check the Regression report
8. Where applicable, use plan forcing

Conclusion

Query Store and the ability to force a plan is going to change the way we do query tuning. In some instances, but not all or even a majority, we won’t have to modify our code or add indexes in order to arrive at superior performance. We’ll be able to pick an appropriate plan, force it, and get better performance.

If you want to get started on Query Store and you’re attending PASS Summit 2016, I’ll be doing a session there on this topic on Wednesday, Oct 26 at 3:15 PM.

The post Query Store and Plan Forcing: What Do You Use It For appeared first on Home Of The Scary DBA.