{"id":1748,"date":"2009-07-27T12:27:23","date_gmt":"2009-07-27T16:27:23","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/jonathan\/?p=1748"},"modified":"2017-04-13T14:41:51","modified_gmt":"2017-04-13T18:41:51","slug":"digging-into-the-sql-plan-cache-finding-missing-indexes","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/digging-into-the-sql-plan-cache-finding-missing-indexes\/","title":{"rendered":"Digging into the SQL Plan Cache: Finding Missing Indexes"},"content":{"rendered":"<p>This is one of those topics I planned to blog about a long time ago, but never actually got around to it, and consequently I keep having to hit search engines to find the code I provided in a forums post when I need to use it again.\u00a0 A while back, I helped fellow SQL enthusiast <a href=\"https:\/\/facility9.com\/\">Jeremiah Peschka<\/a> (<a href=\"https:\/\/twitter.com\/peschkaj\">@peschkaj<\/a>) out via twitter with finding plans that included missing index information in them.\u00a0 Then a bit later I used the same code on a <a href=\"https:\/\/social.msdn.microsoft.com\/Forums\/en-US\/sqldatabaseengine\/thread\/71814032-cd8d-4802-80de-7fb2bee80f41\" target=\"_blank\">Forums Post<\/a> and then again with <a href=\"http:\/\/sqlfool.com\/\" target=\"_blank\">Michelle Ufford<\/a> (<a href=\"https:\/\/mobile.twitter.com\/sqlfool\" target=\"_blank\">@sqlfool<\/a>) who actually took the code and ran with it to create a very nice <a href=\"http:\/\/sqlfool.com\/2009\/03\/find-missing-indexes\/\" target=\"_blank\">stored procedure<\/a> that runs to capture plans with missing indexes into a table where they can be investigated at length later.\u00a0 As you probably already know, missing index information is stored in the DMV&#8217;s in SQL Server 2005 and 2008, but one thing that is missing from the DMV&#8217;s is the SQL Statement that triggered the missing index information to be added.\u00a0 This is available in the plan cache, if the query plan still exists in the cache.<\/p>\n<p>To be perfectly honest, the quick way to get to the root of the problem is to just query the procedure cache and then click on the XML in the results window which will open up the execution plan graphically, and if you have SSMS 2008, the missing index information will show up in green text as a part of the window, and a simple right click will produce the script required to create the missing index.\u00a0 Anybody can do that, so I don&#8217;t see the fun in playing there.\u00a0 What is really nice about the XML plans is that they are 1) schema bound to a published schema and 2) hold a vast amount of information that can give your deep insight into how your queries are actually working.<\/p>\n<p>The XML schema for the show plans are published on the following location:<\/p>\n<blockquote><p><a title=\"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan\/\" href=\"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan\/\">http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan\/<\/a><\/p><\/blockquote>\n<p>This allows the use of XML NAMESPACES for quick querying of information out of the XML document using XQUERY.\u00a0 One of the question asked in the comments on Michelle&#8217;s blog post was how to get the information in the same format as the missing index DMV&#8217;s.\u00a0 I thought this would be interesting to look at so I started playing with the XML document and looking at the schema to figure out how to go about doing it.\u00a0 What I came up with was the following code:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nWITH XMLNAMESPACES\r\n   (DEFAULT 'http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan')\r\n\r\nSELECT query_plan,\r\n       n.value('(@StatementText)&#x5B;1]', 'VARCHAR(4000)') AS sql_text,\r\n       n.value('(\/\/MissingIndexGroup\/@Impact)&#x5B;1]', 'FLOAT') AS impact,\r\n       DB_ID(REPLACE(REPLACE(n.value('(\/\/MissingIndex\/@Database)&#x5B;1]', 'VARCHAR(128)'),'&#x5B;',''),']','')) AS database_id,\r\n       OBJECT_ID(n.value('(\/\/MissingIndex\/@Database)&#x5B;1]', 'VARCHAR(128)') + '.' +\r\n           n.value('(\/\/MissingIndex\/@Schema)&#x5B;1]', 'VARCHAR(128)') + '.' +\r\n           n.value('(\/\/MissingIndex\/@Table)&#x5B;1]', 'VARCHAR(128)')) AS OBJECT_ID,\r\n       n.value('(\/\/MissingIndex\/@Database)&#x5B;1]', 'VARCHAR(128)') + '.' +\r\n           n.value('(\/\/MissingIndex\/@Schema)&#x5B;1]', 'VARCHAR(128)') + '.' +\r\n           n.value('(\/\/MissingIndex\/@Table)&#x5B;1]', 'VARCHAR(128)')\r\n       AS statement,\r\n       (   SELECT DISTINCT c.value('(@Name)&#x5B;1]', 'VARCHAR(128)') + ', '\r\n           FROM n.nodes('\/\/ColumnGroup') AS t(cg)\r\n           CROSS APPLY cg.nodes('Column') AS r(c)\r\n           WHERE cg.value('(@Usage)&#x5B;1]', 'VARCHAR(128)') = 'EQUALITY'\r\n           FOR  XML PATH('')\r\n       ) AS equality_columns,\r\n        (  SELECT DISTINCT c.value('(@Name)&#x5B;1]', 'VARCHAR(128)') + ', '\r\n           FROM n.nodes('\/\/ColumnGroup') AS t(cg)\r\n           CROSS APPLY cg.nodes('Column') AS r(c)\r\n           WHERE cg.value('(@Usage)&#x5B;1]', 'VARCHAR(128)') = 'INEQUALITY'\r\n           FOR  XML PATH('')\r\n       ) AS inequality_columns,\r\n       (   SELECT DISTINCT c.value('(@Name)&#x5B;1]', 'VARCHAR(128)') + ', '\r\n           FROM n.nodes('\/\/ColumnGroup') AS t(cg)\r\n           CROSS APPLY cg.nodes('Column') AS r(c)\r\n           WHERE cg.value('(@Usage)&#x5B;1]', 'VARCHAR(128)') = 'INCLUDE'\r\n           FOR  XML PATH('')\r\n       ) AS include_columns\r\nINTO #MissingIndexInfo\r\nFROM\r\n(\r\n   SELECT query_plan\r\n   FROM (\r\n           SELECT DISTINCT plan_handle\r\n           FROM sys.dm_exec_query_stats WITH(NOLOCK)\r\n         ) AS qs\r\n       OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp\r\n   WHERE tp.query_plan.exist('\/\/MissingIndex')=1\r\n) AS tab (query_plan)\r\nCROSS APPLY query_plan.nodes('\/\/StmtSimple') AS q(n)\r\nWHERE n.exist('QueryPlan\/MissingIndexes') = 1;\r\n\r\n-- Trim trailing comma from lists\r\nUPDATE #MissingIndexInfo\r\nSET equality_columns = LEFT(equality_columns,LEN(equality_columns)-1),\r\n   inequality_columns = LEFT(inequality_columns,LEN(inequality_columns)-1),\r\n   include_columns = LEFT(include_columns,LEN(include_columns)-1);\r\n\r\nSELECT *\r\nFROM #MissingIndexInfo;\r\n\r\nDROP TABLE #MissingIndexInfo;\r\n<\/pre>\n<p>This only begins to scratch the surface of the information available in the XML Showplan.\u00a0 I&#8217;ll be doing a few more posts that show information available based on code I&#8217;ve used to answer questions on the forums out of the plan cache.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This is one of those topics I planned to blog about a long time ago, but never actually got around to it, and consequently I keep having to hit search engines to find the code I provided in a forums post when I need to use it again.\u00a0 A while back, I helped fellow SQL [&hellip;]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,48,38,39],"tags":[],"class_list":["post-1748","post","type-post","status-publish","format-standard","hentry","category-plan-cache","category-sql-server","category-sql-server-2008","category-sql-server-2012"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.3 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Digging into the SQL Plan Cache: Finding Missing Indexes - Jonathan Kehayias<\/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\/jonathan\/digging-into-the-sql-plan-cache-finding-missing-indexes\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Digging into the SQL Plan Cache: Finding Missing Indexes - Jonathan Kehayias\" \/>\n<meta property=\"og:description\" content=\"This is one of those topics I planned to blog about a long time ago, but never actually got around to it, and consequently I keep having to hit search engines to find the code I provided in a forums post when I need to use it again.\u00a0 A while back, I helped fellow SQL [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/digging-into-the-sql-plan-cache-finding-missing-indexes\/\" \/>\n<meta property=\"og:site_name\" content=\"Jonathan Kehayias\" \/>\n<meta property=\"article:published_time\" content=\"2009-07-27T16:27:23+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T18:41:51+00:00\" \/>\n<meta name=\"author\" content=\"Jonathan Kehayias\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jonathan Kehayias\" \/>\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\":\"Article\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/digging-into-the-sql-plan-cache-finding-missing-indexes\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/digging-into-the-sql-plan-cache-finding-missing-indexes\\\/\"},\"author\":{\"name\":\"Jonathan Kehayias\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"headline\":\"Digging into the SQL Plan Cache: Finding Missing Indexes\",\"datePublished\":\"2009-07-27T16:27:23+00:00\",\"dateModified\":\"2017-04-13T18:41:51+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/digging-into-the-sql-plan-cache-finding-missing-indexes\\\/\"},\"wordCount\":748,\"commentCount\":8,\"articleSection\":[\"Plan Cache\",\"SQL Server\",\"SQL Server 2008\",\"SQL Server 2012\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/digging-into-the-sql-plan-cache-finding-missing-indexes\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/digging-into-the-sql-plan-cache-finding-missing-indexes\\\/\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/digging-into-the-sql-plan-cache-finding-missing-indexes\\\/\",\"name\":\"Digging into the SQL Plan Cache: Finding Missing Indexes - Jonathan Kehayias\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\"},\"datePublished\":\"2009-07-27T16:27:23+00:00\",\"dateModified\":\"2017-04-13T18:41:51+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/digging-into-the-sql-plan-cache-finding-missing-indexes\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/digging-into-the-sql-plan-cache-finding-missing-indexes\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/digging-into-the-sql-plan-cache-finding-missing-indexes\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Plan Cache\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/category\\\/plan-cache\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Digging into the SQL Plan Cache: Finding Missing Indexes\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\",\"name\":\"Jonathan Kehayias - The Rambling DBA\",\"description\":\"The Rambling DBA\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/?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\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\",\"name\":\"Jonathan Kehayias\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"caption\":\"Jonathan Kehayias\"},\"sameAs\":[\"http:\\\/\\\/3.209.169.194\\\/blogs\\\/jonathan\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Digging into the SQL Plan Cache: Finding Missing Indexes - Jonathan Kehayias","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\/jonathan\/digging-into-the-sql-plan-cache-finding-missing-indexes\/","og_locale":"en_US","og_type":"article","og_title":"Digging into the SQL Plan Cache: Finding Missing Indexes - Jonathan Kehayias","og_description":"This is one of those topics I planned to blog about a long time ago, but never actually got around to it, and consequently I keep having to hit search engines to find the code I provided in a forums post when I need to use it again.\u00a0 A while back, I helped fellow SQL [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/digging-into-the-sql-plan-cache-finding-missing-indexes\/","og_site_name":"Jonathan Kehayias","article_published_time":"2009-07-27T16:27:23+00:00","article_modified_time":"2017-04-13T18:41:51+00:00","author":"Jonathan Kehayias","twitter_misc":{"Written by":"Jonathan Kehayias","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/digging-into-the-sql-plan-cache-finding-missing-indexes\/#article","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/digging-into-the-sql-plan-cache-finding-missing-indexes\/"},"author":{"name":"Jonathan Kehayias","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"headline":"Digging into the SQL Plan Cache: Finding Missing Indexes","datePublished":"2009-07-27T16:27:23+00:00","dateModified":"2017-04-13T18:41:51+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/digging-into-the-sql-plan-cache-finding-missing-indexes\/"},"wordCount":748,"commentCount":8,"articleSection":["Plan Cache","SQL Server","SQL Server 2008","SQL Server 2012"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/digging-into-the-sql-plan-cache-finding-missing-indexes\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/digging-into-the-sql-plan-cache-finding-missing-indexes\/","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/digging-into-the-sql-plan-cache-finding-missing-indexes\/","name":"Digging into the SQL Plan Cache: Finding Missing Indexes - Jonathan Kehayias","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website"},"datePublished":"2009-07-27T16:27:23+00:00","dateModified":"2017-04-13T18:41:51+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/digging-into-the-sql-plan-cache-finding-missing-indexes\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/digging-into-the-sql-plan-cache-finding-missing-indexes\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/digging-into-the-sql-plan-cache-finding-missing-indexes\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/"},{"@type":"ListItem","position":2,"name":"Plan Cache","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/category\/plan-cache\/"},{"@type":"ListItem","position":3,"name":"Digging into the SQL Plan Cache: Finding Missing Indexes"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/","name":"Jonathan Kehayias - The Rambling DBA","description":"The Rambling DBA","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/?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\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c","name":"Jonathan Kehayias","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","caption":"Jonathan Kehayias"},"sameAs":["http:\/\/3.209.169.194\/blogs\/jonathan"]}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/1748","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/comments?post=1748"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/1748\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/media?parent=1748"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/categories?post=1748"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/tags?post=1748"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}