{"id":695,"date":"2010-05-01T16:41:00","date_gmt":"2010-05-01T16:41:00","guid":{"rendered":"\/blogs\/paul\/post\/Missing-index-DMVs-bug-that-could-cost-your-sanity.aspx"},"modified":"2013-01-01T19:04:29","modified_gmt":"2013-01-02T03:04:29","slug":"missing-index-dmvs-bug-that-could-cost-your-sanity","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/missing-index-dmvs-bug-that-could-cost-your-sanity\/","title":{"rendered":"Missing index DMVs bug that could cost your sanity…"},"content":{"rendered":"

\nHere's yet another reason to be very careful when using the missing index DMVs… <\/font>\n<\/p>\n

\nThere's a bug in the missing index DMVs that could end up causing you to knock your head against a brick wall and question your sanity. I know I did. <\/font>\n<\/p>\n

\nThe bug is this: the missing index code may recommend a nonclustered index that already exists. Over and over again. It might also recommend an index that won't actually help a query.<\/font>\n<\/p>\n

\nYes, I'm surprised by this too – as the missing index code is in the query optimizer too. However, it will continue to recommend you create the already-existing index – which is terribly annoying. <\/font>\n<\/p>\n

\nThis is a little-known bug (<\/font>Connect item #416197<\/font><\/a>) which is fixed in SQL11 but won't be fixed in earlier versions. <\/font>\n<\/p>\n

\nI experienced this on SQL Server 2008 SP1 this weekend and I wanted to blog about it so you don't spend ages trying to work out what's going on. <\/font>\n<\/p>\n

\nHere's a repro for you: <\/font>\n<\/p>\n

\n

\n\tCREATE TABLE t1 (
\n\t    c1 INT IDENTITY,
\n\t    c2 AS c1 * 2,
\n\t    c3 AS c1 + c1,
\n\t    c4 CHAR (3000) DEFAULT 'a');
\n\tGO
\n\tCREATE UNIQUE CLUSTERED INDEX t1_clus ON t1 (c1);
\n\tGO <\/font>\n\t<\/p>\n

\n\tSET NOCOUNT ON;
\n\tGO
\n\tINSERT INTO t1 DEFAULT VALUES;
\n\tGO 100000<\/font> <\/font>\n\t<\/p>\n<\/blockquote>\n

\nThis creates a table with a a bunch of rows, with each row pretty large so that the cost of scanning the table is expensive. <\/font>\n<\/p>\n

\nNow say I want to run a query: <\/font>\n<\/p>\n

\n

\n\tSELECT COUNT (*) FROM t1
\n\t    WHERE c2 BETWEEN 10 AND 1000
\n\t    AND c3 > 1000;
\n\tGO<\/font>  <\/font>\n\t<\/p>\n<\/blockquote>\n

\nIf I display the estimated execution plan… <\/font>\n<\/p>\n

\n \"\" <\/font>\n<\/p>\n

\n…it will tell me there's a missing index I should create: <\/font>\n<\/p>\n

\n \"\" <\/font>\n<\/p>\n

\nSo I go ahead and create the index and everything's cool: <\/font>\n<\/p>\n

\n

\n\tCREATE NONCLUSTERED INDEX [_missing_c2_c3] ON [dbo].[t1] ([c2],[c3]);
\n\tGO<\/font> <\/font>\n\t<\/p>\n<\/blockquote>\n

\nNow what if I want to do something more complicted? How about a cursor over the table? (Don't start on about not using cursors – they're everywhere in application code we see – this is just an easy example to engineer.) <\/font>\n<\/p>\n

\n

\n\tDECLARE testcursor CURSOR FOR
\n\t    SELECT c1 FROM t1
\n\t    WHERE
\n\t        c2 BETWEEN 10 AND 1000
\n\t        AND c3 > 1000; <\/font>\n\t<\/p>\n

\n\tDECLARE @var BIGINT; <\/font>\n\t<\/p>\n

\n\tOPEN testcursor; <\/font>\n\t<\/p>\n

\n\tFETCH NEXT FROM testcursor INTO @var; <\/font>\n\t<\/p>\n

\n\tWHILE (@@fetch_status <> -1)
\n\tBEGIN
\n\t    — empty body
\n\t    FETCH NEXT FROM testcursor INTO @var;
\n\tEND <\/font>\n\t<\/p>\n

\n\tCLOSE testcursor;
\n\tDEALLOCATE testcursor;<\/font> <\/font>\n\t<\/p>\n<\/blockquote>\n

\nIf I display the estimated execution plan again, it shows: <\/font>\n<\/p>\n

\n \"\" <\/font>\n<\/p>\n

\nHmm. That index is actually exactly the same as the one we created earlier (even though it's asking for c1 to be INCLUDE<\/font>d, it already is in the existing nonclustered index as c1 is the cluster key and is included automatically). However, just to prove I'm not doing anything dodgy, I'll create the index it wants: <\/font>\n<\/p>\n

\n

\n\tCREATE NONCLUSTERED INDEX [_missing_c2_c3_inc_c1] ON [dbo].[t1] ([c2],[c3]) INCLUDE ([c1]);
\n\tGO<\/font> <\/font>\n\t<\/p>\n<\/blockquote>\n

\nAnd nothing changes. You cannot get the missing index code to stop recommending the index. The index isn't being used for the *Key Lookup* in the query plan above  – but the missing index code thinks the index would be useful and suggests it. Not only would that index not actually help the Key Lookup, it already exists!<\/font>\n<\/p>\n

\nIf you use a query that aggregates the missing index DMV output (such as <\/font>Bart Duncan's excellent script<\/font><\/a>) and you have some very common queries on your system that are hitting this bug, you will find that the missing index DMV aggregation will be broken too.<\/font>\n<\/p>\n

\nBe careful out there! <\/font><\/p>\n","protected":false},"excerpt":{"rendered":"

Here's yet another reason to be very careful when using the missing index DMVs… There's a bug in the missing index DMVs that could end up causing you to knock your head against a brick wall and question your sanity. I know I did. The bug is this: the missing index code may recommend a nonclustered […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[47,66,73],"tags":[],"class_list":["post-695","post","type-post","status-publish","format-standard","hentry","category-indexes-from-every-angle","category-performance-tuning","category-query-tuning"],"yoast_head":"\nMissing index DMVs bug that could cost your sanity... - 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\/missing-index-dmvs-bug-that-could-cost-your-sanity\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Missing index DMVs bug that could cost your sanity... - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"Here's yet another reason to be very careful when using the missing index DMVs… There's a bug in the missing index DMVs that could end up causing you to knock your head against a brick wall and question your sanity. I know I did. The bug is this: the missing index code may recommend a nonclustered […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/missing-index-dmvs-bug-that-could-cost-your-sanity\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2010-05-01T16:41:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-02T03:04:29+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=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/missing-index-dmvs-bug-that-could-cost-your-sanity\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/missing-index-dmvs-bug-that-could-cost-your-sanity\/\",\"name\":\"Missing index DMVs bug that could cost your sanity... - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2010-05-01T16:41:00+00:00\",\"dateModified\":\"2013-01-02T03:04:29+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/missing-index-dmvs-bug-that-could-cost-your-sanity\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/missing-index-dmvs-bug-that-could-cost-your-sanity\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/missing-index-dmvs-bug-that-could-cost-your-sanity\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Missing index DMVs bug that could cost your sanity…\"}]},{\"@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":"Missing index DMVs bug that could cost your sanity... - 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\/missing-index-dmvs-bug-that-could-cost-your-sanity\/","og_locale":"en_US","og_type":"article","og_title":"Missing index DMVs bug that could cost your sanity... - Paul S. Randal","og_description":"Here's yet another reason to be very careful when using the missing index DMVs… There's a bug in the missing index DMVs that could end up causing you to knock your head against a brick wall and question your sanity. I know I did. The bug is this: the missing index code may recommend a nonclustered […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/missing-index-dmvs-bug-that-could-cost-your-sanity\/","og_site_name":"Paul S. Randal","article_published_time":"2010-05-01T16:41:00+00:00","article_modified_time":"2013-01-02T03:04:29+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/missing-index-dmvs-bug-that-could-cost-your-sanity\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/missing-index-dmvs-bug-that-could-cost-your-sanity\/","name":"Missing index DMVs bug that could cost your sanity... - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2010-05-01T16:41:00+00:00","dateModified":"2013-01-02T03:04:29+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/missing-index-dmvs-bug-that-could-cost-your-sanity\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/missing-index-dmvs-bug-that-could-cost-your-sanity\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/missing-index-dmvs-bug-that-could-cost-your-sanity\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Missing index DMVs bug that could cost your sanity…"}]},{"@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\/695","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=695"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/695\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=695"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=695"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=695"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}