{"id":1047,"date":"2019-09-11T07:45:13","date_gmt":"2019-09-11T14:45:13","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=1047"},"modified":"2019-09-11T07:45:13","modified_gmt":"2019-09-11T14:45:13","slug":"why-you-need-query-store-part-ii-plan-forcing","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-ii-plan-forcing\/","title":{"rendered":"Why You Need Query Store, Part II: Plan forcing"},"content":{"rendered":"

We hear a lot about all the data that Query Store captures<\/a>, but it is tempting to argue that the best part of Query Store is the ability to force a specific plan for a query.\u00a0 Arguably, if the data didn\u2019t exist, we would have a harder time figuring out what<\/em> plan to force, but plan forcing with Query Store is light-years easier than using a plan guide (the historical method for getting a query to use a specific plan).\u00a0 Further, tracking what plans are forced and how the queries are performing is easy too, using the Queries With Forced Plans report in SSMS.<\/p>\n

Items to Consider<\/h2>\n

Before you go too crazy trying to stabilize performance with hundreds of forced plans, here are a couple things worth mentioning about plan forcing.<\/p>\n

    \n
  1. I think of plan forcing as a temporary solution. I do not view it as a \u201cset it and forget it\u201d option.<\/li>\n
  2. Use T-SQL for forcing and un-forcing (versus the UI) so you can track when it was done, and by whom, in change control. Nothing is entered in the ERRORLOG, or anywhere else, and there is currently no event that captures it.<\/li>\n
  3. Evaluate both the execution statistics (average and total) and the different plans for the query to determine the \u201cbest\u201d plan to force.<\/li>\n<\/ol>\n

    Why do I view plan forcing as temporary?\u00a0 Because if I have plan variability, I\u2019d really like to address it through code or schema changes.\u00a0 Maybe there\u2019s an index to add.\u00a0 Maybe a section of the query needs a re-write.\u00a0 To me, forcing the plan gives me a way to \u201cstop the bleeding\u201d \u2013 I can stabilize performance right now \u2013 until I can determine the right long-term solution and implement it.<\/p>\n

    For those of you with third-party applications, who cannot change code or indexes, you still want to monitor performance of those forced plans.\u00a0 While you can\u2019t change the code or the schema, the vendor can (and most likely will at some point), and your data will change.<\/em>\u00a0 Changes in data and its distribution could have a greater impact on plan choice than schema and code, and you may be preventing a better plan from being used by forcing the one that was good six months ago.<\/p>\n

    Deciding What Plan to Force<\/h2>\n

    When you are looking at plan performance, deciding which plan to force can get tricky, depending on how many plans you have for a query.\u00a0 I start by looking at the plans \u2013 does one of them stand out as a \u201cbetter\u201d plan because of the operators it uses (or doesn\u2019t use), the lack of warnings in the plan, etc.\u00a0 Then I look at the execution statistics, and this is where you want to make sure you know what metric is displayed in the UI.\u00a0 Examine graph A below, and decide what plan you would force before you continue reading.<\/p>\n

    \"Total
    Total plan duration over time<\/figcaption><\/figure>\n

    Graph A shows plan performance for query_id 137512 across a 48-hour window.\u00a0 They y-axis is duration, and notice that it\u2019s TOTAL duration.<\/p>\n

    Now take a look at graph B, below, which shows plan performance for the same query_id across the exact same window of time, but the y-axis is AVERAGE duration.\u00a0 Does your forcing decision change?<\/p>\n

    \"Average
    Average plan duration over time<\/figcaption><\/figure>\n

    When we look at average duration across the different plans, it\u2019s quite consistent.\u00a0 So much so that I probably wouldn\u2019t force a plan for this query.\u00a0 I may investigate the plans in more detail to understand what the variation is, but for the most part, I\u2019d probably leave it.\u00a0 Remember that total duration represents the total amount of time spent executing the query during a time interval.\u00a0 So the plans that had a lower total duration most likely were not executed as many times as those with a higher total duration.\u00a0 This becomes evident when you hover over one of the circles and look at the metrics for that specific data point, as shown below.<\/p>\n

    \"Query
    Query performance details for a highlighted plan<\/figcaption><\/figure>\n

    <\/h2>\n

    Summary<\/h2>\n

    I think plan forcing is a fantastic feature in SQL Server, whether you\u2019re manually forcing plans, or using Automatic Plan Correction<\/a>.\u00a0 If you are forcing plans, take the time to decide the \u201cbest\u201d plan to force by not just reviewing the plan and the execution statistics, but also by testing the plan with different input parameters.\u00a0 This can really to understand what plan consistently provides stable performance for a query.\u00a0 I recommend setting up an Extended Events session to monitor for forcing failures, and regularly re-visit what plans are forced to see if they are still appropriate.\u00a0 Finally, remember that if you turn off Query Store for any reason, plan forcing is not longer in effect.\u00a0 But if Query Store happens to flip to READ_ONLY, as long it\u2019s enabled, your plans will still be forced.<\/p>\n

     <\/p>\n

     <\/p>\n","protected":false},"excerpt":{"rendered":"

    We hear a lot about all the data that Query Store captures, but it is tempting to argue that the best part of Query Store is the ability to force a specific plan for a query.\u00a0 Arguably, if the data didn\u2019t exist, we would have a harder time figuring out what plan to force, but […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[46],"tags":[],"yoast_head":"\nWhy You Need Query Store, Part II: Plan forcing - Erin Stellato<\/title>\n<meta name=\"description\" content=\"The Query Store feature includes plan forcing capabilities - you can manually, or automaticall, force a plan for a specific query to stabilize performance.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-ii-plan-forcing\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Why You Need Query Store, Part II: Plan forcing - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"The Query Store feature includes plan forcing capabilities - you can manually, or automaticall, force a plan for a specific query to stabilize performance.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-ii-plan-forcing\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2019-09-11T14:45:13+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2019\/09\/QS_TotalDuration-1024x343.jpg\" \/>\n<meta name=\"author\" content=\"Erin Stellato\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Erin Stellato\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-ii-plan-forcing\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-ii-plan-forcing\/\",\"name\":\"Why You Need Query Store, Part II: Plan forcing - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2019-09-11T14:45:13+00:00\",\"dateModified\":\"2019-09-11T14:45:13+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"The Query Store feature includes plan forcing capabilities - you can manually, or automaticall, force a plan for a specific query to stabilize performance.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-ii-plan-forcing\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-ii-plan-forcing\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-ii-plan-forcing\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Why You Need Query Store, Part II: Plan forcing\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\",\"name\":\"Erin Stellato\",\"description\":\"The SQL Sequel\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\",\"name\":\"Erin Stellato\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"caption\":\"Erin Stellato\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/erin\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Why You Need Query Store, Part II: Plan forcing - Erin Stellato","description":"The Query Store feature includes plan forcing capabilities - you can manually, or automaticall, force a plan for a specific query to stabilize performance.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-ii-plan-forcing\/","og_locale":"en_US","og_type":"article","og_title":"Why You Need Query Store, Part II: Plan forcing - Erin Stellato","og_description":"The Query Store feature includes plan forcing capabilities - you can manually, or automaticall, force a plan for a specific query to stabilize performance.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-ii-plan-forcing\/","og_site_name":"Erin Stellato","article_published_time":"2019-09-11T14:45:13+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2019\/09\/QS_TotalDuration-1024x343.jpg"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-ii-plan-forcing\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-ii-plan-forcing\/","name":"Why You Need Query Store, Part II: Plan forcing - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2019-09-11T14:45:13+00:00","dateModified":"2019-09-11T14:45:13+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"The Query Store feature includes plan forcing capabilities - you can manually, or automaticall, force a plan for a specific query to stabilize performance.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-ii-plan-forcing\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-ii-plan-forcing\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-ii-plan-forcing\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Why You Need Query Store, Part II: Plan forcing"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/","name":"Erin Stellato","description":"The SQL Sequel","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158","name":"Erin Stellato","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","caption":"Erin Stellato"},"sameAs":["http:\/\/3.209.169.194\/blogs\/erin"],"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/1047"}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/comments?post=1047"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/1047\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=1047"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=1047"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=1047"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}