{"id":951,"date":"2018-10-03T11:26:04","date_gmt":"2018-10-03T18:26:04","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=951"},"modified":"2019-03-04T10:58:27","modified_gmt":"2019-03-04T18:58:27","slug":"plan-forcing-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/plan-forcing-in-sql-server\/","title":{"rendered":"Plan Forcing in SQL Server"},"content":{"rendered":"

Last month I was in Portugal for their SQLSaturday event, and I spent a lot of time talking about Plan Forcing in SQL Server \u2013 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.<\/p>\n

Forcing plans in SQL Server provides a very easy method for DBAs and developers to stabilize query performance.\u00a0 But plan forcing is not a permanent solution.<\/strong>\u00a0 Consider the premise on which plan forcing relies: multiple plans exist for a query and one of them provides the most consistent performance.\u00a0 If I have high variability in query performance, ideally, I want to address that in the code or through schema changes (e.g. indexing).\u00a0 Forcing a plan for a query is a lot like creating a plan guide \u2013 they are similar but they are two separate features<\/em> \u2013 in that it\u2019s a temporary solution.\u00a0 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<\/strong> it was added, when<\/strong> it was added, and I start looking at what can be done to remove it.<\/p>\n

Knowing that this is how I view plan forcing, how do I decide when to force a plan? \u00a0When the query has variability in performance.<\/p>\n

Consider Query A, which generates multiple plans, but they\u2019re all about the same in terms of duration, I\/O, and CPU.\u00a0 The performance across the different plans is consistent.\u00a0 I won\u2019t force a plan for that query.<\/p>\n

\"Query<\/a>
Query with multiple, consistent plans<\/figcaption><\/figure>\n

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.\u00a0 Maybe a couple plans provide good performance, but the rest are awful.\u00a0 Would I force one of the \u201cgood plans\u201d?\u00a0 Probably \u2013 but I\u2019d do some testing first.<\/p>\n

\"Query<\/a>
Query with multiple plans that have variable performance<\/figcaption><\/figure>\n

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

Now, out of my entire workload, if I have many queries that have multiple plans, where do I start?\u00a0 With the worst offenders.\u00a0 If I\u2019m 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.\u00a0 But I also look for the \u201cdeath by a thousand cuts\u201d scenario \u2013 the queries which execute hundreds or thousands of times a minute.\u00a0 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.\u00a0 There was concern because the query had multiple plans.\u00a0 I pointed out that the query had executed 71,000 times in an hour\u2026which is almost 20 times a second.\u00a0 While I want to investigate multiple plans, I also want to know why a query executes so often.<\/em><\/p>\n

Thus far, I\u2019ve talked about a workload\u2026one workload<\/strong>.\u00a0 What about the environment where you support hundreds of SQL Server instances?\u00a0 You can obviously take the approach I\u2019ve 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.\u00a0 Or, if you\u2019re 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\u2019s a regression.\u00a0 I wrote a post (Automatic Plan Correction in SQL Server<\/a>) on SQLPerformance.com about this feature, so I\u2019m not going to re-hash the details here.<\/p>\n

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<\/em>.\u00a0 I don\u2019t expect you to have plans forced for years, let alone months.\u00a0 The life of a forced plan will, of course, depend on how quickly code and schema changes are ported to production.\u00a0 If you go the “set it and forget it route”, theoretically a manually forced plan could get used for a very long time.\u00a0 In that scenario, it\u2019s your<\/strong> responsibility to periodically check to ensure that plan is still the \u201cbest\u201d one for the query.\u00a0 I would be checking every couple weeks; once a month at most.\u00a0 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.<\/p>\n

Further, you don\u2019t want to ignore forced plans because there are cases where a forced plan won\u2019t be used (you can use Extended Events to monitor this).\u00a0 When you force a plan manually, forcing can still fail.\u00a0 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. \u00a0Important 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!<\/em>\u00a0 If you\u2019re 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<\/a>.\u00a0 If you have manually forced a plan for a query, and the force plan fails, it remains forced.\u00a0 You have to manually un-force it to stop SQL Server from trying to use that plan.\u00a0 As you can see, there are multiple<\/strong> factors related to plan forcing, which is why you don\u2019t just force a plan and forget it.<\/p>\n

This behavior is different if you\u2019re using Automatic Plan Correction (APC).\u00a0 As mentioned in the Automatic tuning documentation<\/a>, if a plan is automatically forced, it will be automatically un-forced if:<\/p>\n