{"id":765,"date":"2009-12-03T11:28:00","date_gmt":"2009-12-03T11:28:00","guid":{"rendered":"\/blogs\/paul\/post\/Interesting-2008-partitioned-view-perf-bug-fixed-in-SP1-CU4.aspx"},"modified":"2017-04-13T11:41:26","modified_gmt":"2017-04-13T18:41:26","slug":"interesting-2008-partitioned-view-perf-bug-fixed-in-sp1-cu4","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/interesting-2008-partitioned-view-perf-bug-fixed-in-sp1-cu4\/","title":{"rendered":"Interesting 2008 partitioned view perf bug fixed in SP1 CU4"},"content":{"rendered":"<p>\n<font face=\"verdana,geneva\" size=\"2\">This is an interesting performance bug concerning a broken query optimizer rule in 2008. Thanks to Dan Shargel (<\/font><a href=\"https:\/\/twitter.com\/danshargel\"><font face=\"verdana,geneva\" size=\"2\">Twitter<\/font><\/a><font face=\"verdana,geneva\" size=\"2\">) for sending me info on this and letting me use some of the stuff he sent. <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">The scenario involves using MIN or MAX in a query against a partitioned view. In 2005 the query plan includes a <font face=\"courier new,courier\">TOP (1)<\/font> operator which uses the right index, but in 2008 the optimizer rule was broken and the plan turned into a stream aggregate, much more expensive in this case. <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Here&#39;s the 2005 query plan: <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">&nbsp;<img fetchpriority=\"high\" decoding=\"async\" src=\"\/blogs\/paul\/wp-content\/uploads\/2009\/12\/120309_plan1.jpg\" alt=\"\" width=\"781\" height=\"203\" \/> <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">and here&#39;s the 2008 query plan (before the bug fix): <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">&nbsp;<img decoding=\"async\" src=\"\/blogs\/paul\/wp-content\/uploads\/2009\/12\/120309_plan2.jpg\" alt=\"\" width=\"835\" height=\"207\" \/><\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">You can get the fix in <a href=\"https:\/\/support.microsoft.com\/en-us\/help\/973602\/cumulative-update-package-4-for-sql-server-2008-service-pack-1\">CU4 for 2008 SP1<\/a> (or later) and read a bit more about it in <a href=\"https:\/\/support.microsoft.com\/en-us\/help\/973255\">KB 973255<\/a>.<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Note that you have to turn on trace flag 4199 to enable the fix &#8211; that requirement will be removed in SQL11.<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"<p>This is an interesting performance bug concerning a broken query optimizer rule in 2008. Thanks to Dan Shargel (Twitter) for sending me info on this and letting me use some of the stuff he sent. The scenario involves using MIN or MAX in a query against a partitioned view. In 2005 the query plan includes [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19,65,66],"tags":[],"class_list":["post-765","post","type-post","status-publish","format-standard","hentry","category-bugfixes","category-partitioning","category-performance-tuning"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Interesting 2008 partitioned view perf bug fixed in SP1 CU4 - Paul S. Randal<\/title>\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\/paul\/interesting-2008-partitioned-view-perf-bug-fixed-in-sp1-cu4\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Interesting 2008 partitioned view perf bug fixed in SP1 CU4 - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"This is an interesting performance bug concerning a broken query optimizer rule in 2008. Thanks to Dan Shargel (Twitter) for sending me info on this and letting me use some of the stuff he sent. The scenario involves using MIN or MAX in a query against a partitioned view. In 2005 the query plan includes [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/interesting-2008-partitioned-view-perf-bug-fixed-in-sp1-cu4\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2009-12-03T11:28:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T18:41:26+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/interesting-2008-partitioned-view-perf-bug-fixed-in-sp1-cu4\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/interesting-2008-partitioned-view-perf-bug-fixed-in-sp1-cu4\/\",\"name\":\"Interesting 2008 partitioned view perf bug fixed in SP1 CU4 - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2009-12-03T11:28:00+00:00\",\"dateModified\":\"2017-04-13T18:41:26+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/interesting-2008-partitioned-view-perf-bug-fixed-in-sp1-cu4\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/interesting-2008-partitioned-view-perf-bug-fixed-in-sp1-cu4\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/interesting-2008-partitioned-view-perf-bug-fixed-in-sp1-cu4\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Interesting 2008 partitioned view perf bug fixed in SP1 CU4\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Interesting 2008 partitioned view perf bug fixed in SP1 CU4 - Paul S. Randal","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\/paul\/interesting-2008-partitioned-view-perf-bug-fixed-in-sp1-cu4\/","og_locale":"en_US","og_type":"article","og_title":"Interesting 2008 partitioned view perf bug fixed in SP1 CU4 - Paul S. Randal","og_description":"This is an interesting performance bug concerning a broken query optimizer rule in 2008. Thanks to Dan Shargel (Twitter) for sending me info on this and letting me use some of the stuff he sent. The scenario involves using MIN or MAX in a query against a partitioned view. In 2005 the query plan includes [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/interesting-2008-partitioned-view-perf-bug-fixed-in-sp1-cu4\/","og_site_name":"Paul S. Randal","article_published_time":"2009-12-03T11:28:00+00:00","article_modified_time":"2017-04-13T18:41:26+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/interesting-2008-partitioned-view-perf-bug-fixed-in-sp1-cu4\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/interesting-2008-partitioned-view-perf-bug-fixed-in-sp1-cu4\/","name":"Interesting 2008 partitioned view perf bug fixed in SP1 CU4 - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2009-12-03T11:28:00+00:00","dateModified":"2017-04-13T18:41:26+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/interesting-2008-partitioned-view-perf-bug-fixed-in-sp1-cu4\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/interesting-2008-partitioned-view-perf-bug-fixed-in-sp1-cu4\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/interesting-2008-partitioned-view-perf-bug-fixed-in-sp1-cu4\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Interesting 2008 partitioned view perf bug fixed in SP1 CU4"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/765","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=765"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/765\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=765"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=765"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=765"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}