{"id":945,"date":"2013-03-11T13:57:19","date_gmt":"2013-03-11T20:57:19","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/joe\/?p=945"},"modified":"2013-03-11T13:57:19","modified_gmt":"2013-03-11T20:57:19","slug":"validating-instance-level-index-view-and-merge-usage","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/validating-instance-level-index-view-and-merge-usage\/","title":{"rendered":"Validating Instance-Level Index View and MERGE Optimization Activity"},"content":{"rendered":"<p>You may have\u00a0read the blog posts a few weeks ago regarding indexed views and MERGE statement\u00a0usage and incorrect results. If not, please see the following blog posts:<\/p>\n<ul>\n<li><a href=\"http:\/\/www.sqlperformance.com\/2013\/02\/t-sql-queries\/another-merge-bug\" target=\"_blank\">If you are using indexed views and MERGE, please read this!<\/a> (<a href=\"http:\/\/www.sqlperformance.com\/author\/abertrand\" target=\"_blank\">blog<\/a> | <a href=\"https:\/\/twitter.com\/AaronBertrand\" target=\"_blank\">@AaronBertrand<\/a>)<\/li>\n<li><a href=\"http:\/\/sqlblog.com\/blogs\/jamie_thomson\/archive\/2013\/02\/04\/noexpand-query-hint-returns-wrong-results-cu-fix-now-available.aspx\" target=\"_blank\" class=\"broken_link\">NOEXPAND query hint returns wrong results \u2013 CU fix now available <\/a>(<a href=\"http:\/\/sqlblog.com\/blogs\/jamie_thomson\/default.aspx\" target=\"_blank\" class=\"broken_link\">blog<\/a> | <a href=\"https:\/\/twitter.com\/jamiet\" target=\"_blank\">@jamiet<\/a>)<\/li>\n<li><a href=\"http:\/\/sqlblog.com\/blogs\/paul_white\/archive\/2013\/02\/06\/incorrect-results-with-indexed-views.aspx\" target=\"_blank\" class=\"broken_link\">Incorrect Results with Indexed Views <\/a>(<a href=\"http:\/\/sqlblog.com\/blogs\/paul_white\/default.aspx\" target=\"_blank\" class=\"broken_link\">blog<\/a> | <a href=\"https:\/\/twitter.com\/SQL_Kiwi\" target=\"_blank\">@SQL_Kiwi<\/a>)<\/li>\n<\/ul>\n<p>I won&#8217;t rehash what they have collectively\u00a0already covered thoroughly &#8211; but just a quick tip about identifying index view and MERGE\u00a0optimization activity\u00a0via the sys.dm_exec_query_optimizer_info DMV&#8230;\u00a0 The following query shows counter\u00a0name\u00a0and\u00a0occurrences of optimizations for MERGE statements and indexed views having been matched\u00a0since the SQL Server instance last restarted:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT\u00a0 &#x5B;counter],\r\n&#x5B;occurrence]\r\nFROM sys.&#x5B;dm_exec_query_optimizer_info]\r\nWHERE counter IN\r\n('merge stmt',\r\n'indexed views matched');\r\n<\/pre>\n<p>I see this as a &#8220;first cut&#8221; check &#8211; but there are some key limitations to why this would only be a starting data point and not the &#8220;end all, be all&#8221; approach:<\/p>\n<ul>\n<li>As mentioned earlier,\u00a0occurrences are as of the last restart of SQL Server &#8211; and you could still be impacted by the issue\u00a0&#8211; but a specific workload may not have been executed yet<\/li>\n<li>The counters\u00a0are instance level &#8211; so you will not know which databases\u00a0were specifically associated with the activity\u00a0and whether this is an actual MERGE + indexed view combo as described in the aforementioned blog posts<\/li>\n<\/ul>\n<p>Even with the limitations, if you see non-zero values for the counters, this might accelerate your investigation and application of the appropriate <a href=\"http:\/\/support.microsoft.com\/kb\/2756471\" target=\"_blank\" class=\"broken_link\">cumulative update<\/a>.\u00a0 I prefer keeping up with serious issues in this case, but if you need to prioritize what gets patched in larger environments with thousands of SQL Server instances, this may help drive that prioritization.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>You may have\u00a0read the blog posts a few weeks ago regarding indexed views and MERGE statement\u00a0usage and incorrect results. If not, please see the following blog posts: If you are using indexed views and MERGE, please read this! (blog | @AaronBertrand) NOEXPAND query hint returns wrong results \u2013 CU fix now available (blog | @jamiet) [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[24,34],"tags":[],"class_list":["post-945","post","type-post","status-publish","format-standard","hentry","category-indexing","category-t-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Validating Instance-Level Index View and MERGE Optimization Activity - Joe Sack<\/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\/joe\/validating-instance-level-index-view-and-merge-usage\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Validating Instance-Level Index View and MERGE Optimization Activity - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"You may have\u00a0read the blog posts a few weeks ago regarding indexed views and MERGE statement\u00a0usage and incorrect results. If not, please see the following blog posts: If you are using indexed views and MERGE, please read this! (blog | @AaronBertrand) NOEXPAND query hint returns wrong results \u2013 CU fix now available (blog | @jamiet) [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/validating-instance-level-index-view-and-merge-usage\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2013-03-11T20:57:19+00:00\" \/>\n<meta name=\"author\" content=\"Joseph Sack\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Joseph Sack\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/validating-instance-level-index-view-and-merge-usage\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/validating-instance-level-index-view-and-merge-usage\/\",\"name\":\"Validating Instance-Level Index View and MERGE Optimization Activity - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2013-03-11T20:57:19+00:00\",\"dateModified\":\"2013-03-11T20:57:19+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/validating-instance-level-index-view-and-merge-usage\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/validating-instance-level-index-view-and-merge-usage\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/validating-instance-level-index-view-and-merge-usage\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Indexing\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/indexing\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Validating Instance-Level Index View and MERGE Optimization Activity\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\",\"name\":\"Joe Sack\",\"description\":\"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\",\"name\":\"Joseph Sack\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"caption\":\"Joseph Sack\"},\"description\":\"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.\",\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/joe\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Validating Instance-Level Index View and MERGE Optimization Activity - Joe Sack","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\/joe\/validating-instance-level-index-view-and-merge-usage\/","og_locale":"en_US","og_type":"article","og_title":"Validating Instance-Level Index View and MERGE Optimization Activity - Joe Sack","og_description":"You may have\u00a0read the blog posts a few weeks ago regarding indexed views and MERGE statement\u00a0usage and incorrect results. If not, please see the following blog posts: If you are using indexed views and MERGE, please read this! (blog | @AaronBertrand) NOEXPAND query hint returns wrong results \u2013 CU fix now available (blog | @jamiet) [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/validating-instance-level-index-view-and-merge-usage\/","og_site_name":"Joe Sack","article_published_time":"2013-03-11T20:57:19+00:00","author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/validating-instance-level-index-view-and-merge-usage\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/validating-instance-level-index-view-and-merge-usage\/","name":"Validating Instance-Level Index View and MERGE Optimization Activity - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2013-03-11T20:57:19+00:00","dateModified":"2013-03-11T20:57:19+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/validating-instance-level-index-view-and-merge-usage\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/validating-instance-level-index-view-and-merge-usage\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/validating-instance-level-index-view-and-merge-usage\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/"},{"@type":"ListItem","position":2,"name":"Indexing","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/indexing\/"},{"@type":"ListItem","position":3,"name":"Validating Instance-Level Index View and MERGE Optimization Activity"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/","name":"Joe Sack","description":"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648","name":"Joseph Sack","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","caption":"Joseph Sack"},"description":"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.","sameAs":["http:\/\/3.209.169.194\/blogs\/joe","https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack"],"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/945","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/comments?post=945"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/945\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=945"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=945"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=945"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}