Plan Forcing in SQL Server

Last month I was in Portugal for their SQLSaturday event, and I spent a lot of time talking about Plan Forcing in SQL Server – both manual and automatic (via the Automatic Plan Correction feature). I had some really great questions from my pre-con and regular session and wanted to summarize a few thoughts on Plan Forcing functionality.

Forcing plans in SQL Server provides a very easy method for DBAs and developers to stabilize query performance.  But plan forcing is not a permanent solution.  Consider the premise on which plan forcing relies: multiple plans exist for a query and one of them provides the most consistent performance.  If I have high variability in query performance, ideally, I want to address that in the code or through schema changes (e.g. indexing).  Forcing a plan for a query is a lot like creating a plan guide – they are similar but they are two separate features – in that it’s a temporary solution.  I also view adding OPTION (RECOMPILE) as a temporary solution. Some of you might be shocked at that, but when I see a RECOMPILE on a query, I immediately ask why it was added, when it was added, and I start looking at what can be done to remove it.

Knowing that this is how I view plan forcing, how do I decide when to force a plan?  When the query has variability in performance.

Consider Query A, which generates multiple plans, but they’re all about the same in terms of duration, I/O, and CPU.  The performance across the different plans is consistent.  I won’t force a plan for that query.

Query with multiple, consistent plans
Query with multiple, consistent plans

Next consider Query B, which also generates different plans, and some are stable but a couple are over the place in terms of duration, I/O, and CPU.  Maybe a couple plans provide good performance, but the rest are awful.  Would I force one of the “good plans”?  Probably – but I’d do some testing first.

Query with multiple plans that have variable performance
Query with multiple plans that have variable performance

Understand that if I force a plan for a query, that’s the plan that’s going to get used unless forcing fails for some reason (e.g. the index no longer exists).  But does that plan work for all variations of the query?  Does that plan provide consistent performance for all the different input parameters that can be used for that query?  This requires testing…and oh by the way, concurrent with any testing/decision to force a plan I’m talking to the developers about ways to address this long-term.

Now, out of my entire workload, if I have many queries that have multiple plans, where do I start?  With the worst offenders.  If I’m resource-bound in some way (e.g. CPU or I/O), then I would look at queries with the highest resource use and start working through those.  But I also look for the “death by a thousand cuts” scenario – the queries which execute hundreds or thousands of times a minute.  As an aside, during the pre-con in Portugal one of the attendees had me look at a query in Query Store in the production environment.  There was concern because the query had multiple plans.  I pointed out that the query had executed 71,000 times in an hour…which is almost 20 times a second.  While I want to investigate multiple plans, I also want to know why a query executes so often.

Thus far, I’ve talked about a workload…one workload.  What about the environment where you support hundreds of SQL Server instances?  You can obviously take the approach I’ve detailed above, which requires a review of poor-performing queries with multiple plans and deciding which plan (if any) to force until development addresses the issue.  Or, if you’re running SQL Server 2017 Enterprise Edition, you could look at Automatic Plan Correction, which will force a plan for a query (without human intervention) if there’s a regression.  I wrote a post (Automatic Plan Correction in SQL Server) on SQLPerformance.com about this feature, so I’m not going to re-hash the details here.

Whether you force plans manually, or let SQL Server force them with the Automatic Plan Correction feature, I still view plan forcing as a temporary solution.  I don’t expect you to have plans forced for years, let alone months.  The life of a forced plan will, of course, depend on how quickly code and schema changes are ported to production.  If you go the “set it and forget it route”, theoretically a manually forced plan could get used for a very long time.  In that scenario, it’s your responsibility to periodically check to ensure that plan is still the “best” one for the query.  I would be checking every couple weeks; once a month at most.  Whether or not the plan remains optimal depends on the tables involved in the query, the data in the tables, how that data changes (if it changes), other schema changes that may be introduced, and more.

Further, you don’t want to ignore forced plans because there are cases where a forced plan won’t be used (you can use Extended Events to monitor this).  When you force a plan manually, forcing can still fail.  For example, if the forced plan uses an index and the index is dropped, or its definition is changed to the point where it cannot be used in plan in the same manner, then forcing will fail.  Important note: if forcing fails, the query will go through normal optimization and compilation and it will execute; SQL Server does not want your query to fail!  If you’re forcing plans and not familiar with the reasons that it can fail, note the last_force_failure_reason values listed for sys.query_store_plan.  If you have manually forced a plan for a query, and the force plan fails, it remains forced.  You have to manually un-force it to stop SQL Server from trying to use that plan.  As you can see, there are multiple factors related to plan forcing, which is why you don’t just force a plan and forget it.

This behavior is different if you’re using Automatic Plan Correction (APC).  As mentioned in the Automatic tuning documentation, if a plan is automatically forced, it will be automatically un-forced if:

  • forcing fails for any reason
  • if there is a performance regression using the forced plan
  • if there is a recompile due to a schema change or an update to statistics.

With APC, there is still work to be done – here you want to use Extended Events or sys.dm_db_tuning_recommendations to see what plans are getting forced, and then decide if you want to force them manually.  If you force a plan manually it will never be automatically un-forced.

There are a lot of considerations when you embrace plan forcing – I think it’s an excellent alternative to plan guides (much easier to use, not schema bound) and I think it’s absolutely worth a DBA or developer’s time to investigate what plan to force, and then use that as a temporary solution until a long-term fix can be put in place.  I hope this helps those of you that have been wary to give it a try!

6 thoughts on “Plan Forcing in SQL Server

        1. My apologies, the X-axis is date, the Y-axis is the resource! In the two graphs shown here, that Y-axis is Total CPU.

          Erin

  1. Is there any retention to forced plan? I mean, for how long the plan will remain enforced for a query.

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.