sqlskills-logo-2015-white.png

Missing index DMVs bug that could cost your sanity…

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 index that already exists. Over and over again. It might also recommend an index that won't actually help a query.

Yes, 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.

This is a little-known bug (Connect item #416197) which is fixed in SQL11 but won't be fixed in earlier versions.

I 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.

Here's a repro for you:

CREATE TABLE t1 (
    c1 INT IDENTITY,
    c2 AS c1 * 2,
    c3 AS c1 + c1,
    c4 CHAR (3000) DEFAULT 'a');
GO
CREATE UNIQUE CLUSTERED INDEX t1_clus ON t1 (c1);
GO

SET NOCOUNT ON;
GO
INSERT INTO t1 DEFAULT VALUES;
GO 100000

This creates a table with a a bunch of rows, with each row pretty large so that the cost of scanning the table is expensive.

Now say I want to run a query:

SELECT COUNT (*) FROM t1
    WHERE c2 BETWEEN 10 AND 1000
    AND c3 > 1000;
GO 

If I display the estimated execution plan…

 

…it will tell me there's a missing index I should create:

 

So I go ahead and create the index and everything's cool:

CREATE NONCLUSTERED INDEX [_missing_c2_c3] ON [dbo].[t1] ([c2],[c3]);
GO

Now 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.)

DECLARE testcursor CURSOR FOR
    SELECT c1 FROM t1
    WHERE
        c2 BETWEEN 10 AND 1000
        AND c3 > 1000;

DECLARE @var BIGINT;

OPEN testcursor;

FETCH NEXT FROM testcursor INTO @var;

WHILE (@@fetch_status <> -1)
BEGIN
    — empty body
    FETCH NEXT FROM testcursor INTO @var;
END

CLOSE testcursor;
DEALLOCATE testcursor;

If I display the estimated execution plan again, it shows:

 

Hmm. That index is actually exactly the same as the one we created earlier (even though it's asking for c1 to be INCLUDEd, 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:

CREATE NONCLUSTERED INDEX [_missing_c2_c3_inc_c1] ON [dbo].[t1] ([c2],[c3]) INCLUDE ([c1]);
GO

And 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!

If you use a query that aggregates the missing index DMV output (such as Bart Duncan's excellent script) 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.

Be careful out there!

22 thoughts on “Missing index DMVs bug that could cost your sanity…”

  1. This bug got me scratching my head just last week. Too bad this renders the DMV kind of useless — one has to filter if hint is bogus or relevant.

    Reply

  2. >>>(Don’t start on about not using cursors – they’re everywhere in application code we see…

    Heh… that’s part of why I have a job. I love apps like that. ;-)

    Reply

  3. I don’t think this bug "renders the DMV kind of useless". You should always look at the existing indexes on the table, and look at the stored procedures and other queries that use the table(if possible), before deciding whether to add a new index that the DMV recommends. The DMV has always been overly eager to recommend indexes, and the column order it recommends is not always correct.

    This DMV is just one tool that helps you decide on your index selection. Never just blindly do everything it asks for.

    Reply

  4. Agreed Glenn – the DMVs are useful but you have to be very careful what you do with them. If you hit this bug, it can be *really* confusing and time-wasting if the idnex it’s suggesting already exists – I can’t imagine how many people out there have come up against this.

    Reply

  5. I’m so glad someone finally explained this. I had a half dozen of these constantly showing in the DMV at my previous job. I thought maybe it was an issue with needing a different sort order but changing the index never solved the problem.

    Reply

  6. well, I guess I had better remove my automatic nightly tuning script that blindly creates all "missing" indices.

    Seriously, I’m glad you pointed this out. I always wondered what was going on and never realized it might be a bug. We always analyze the output and at times have wondered why it would suggest such indices. However we never actually created them because… well.. an accceptable index already existed. Knowing that the cluster "is in" every non-cluster I couldn’t understand why it wanted to Include it as well (although it did make me study what an INCLUDED column was).

    We live by the "most narrow" index rule. We’ve learned this DMV will sometimes recommend slighly wider indices that try and cover one specific query (for instance c1,c2,c3), however we know there are queries that also search on (c1,c2,c4). Even if an index on (c1,c2) exists, the DMV would still suggest creating two more (c1,c2,c3) and (c1,c2,c4). I just figured it was being silly and trying to show a covering index for all possible situations.

    We’ve used it to help provide insight and recommendations – just to make sure we aren’t missing something horribly unexpected ("wow – somebody has a query using c5? better go look").

    An analysis of the code and stats io are used to make the final call.

    -Mike

    Reply

  7. I’ve just run through your process step for step on my SQL 2008 VM, and after creating the missing index and running th cursor code I don’t get a recommendation for the missing index with the INCLUDE. The cursor code uses the index as expected without issues. It may be worth checking if this only occurs under certain conditions, like when the server has multiple CPUs/ or MAXDOP != 1.

    A good DBA doesn’t rely on the DMVs any way, they are a tool, and like any tool you need to use it with wisdom.
    Cheers

    Reply

  8. Indeed. And yes, query plans change. The point is that the missing index DMVs have a bug in, which apparently many people have been tripping over without realizing.

    Reply

  10. Hello Sir,

    As a follow up question, when the missing index bug hits, the execution plan would show a table scan. Is that really what’s going on during query processing? Will it not use the index in reality or will it?

    Thank you very much.

    Reply

  12. I’ve Experienced this same thing ins SQL Server 2016 SP1 Enterprise Edition. The only way I got it to go away in my test lab was to force the query to use the index by “FROM table WITH (INDEX(index_name)).. Weirdness!

    Reply

  13. So we have an EXISTING index which is apparently MISSING?
    Is it possible that the index (although present) is not being used by queries?

    Reply

  14. I’ve noticed a few of these missing indexes that exist, and put them on the “ignore” pile.
    But then I thought – what if for some reason the index is just not being noticed?
    I dropped the existing index and recreated it
    Now my script no longer detects a missing index

    Reply

  15. Just found completely opposite case – the index is actually used (USER_SEEKS is increasing), but anyway suggested to be created by DMV. Suggested index is completely equal to existing one. After index recreation the suggestion row disappeared from DMV, but reappeared again after execution of first request that used it.
    Estimated AVG_USER_IMPACT is about 15% according to DMV.
    I’m sure it’s not a column order error – there’s only one corresponding statement in a code with 2 equalities and some “include” fields in it.

    Reply

  16. An oddity, just one of many to add to the wonderful wacky world of SQL!
    I’ve found that dropping and creating the index in question makes the problem go away
    I haven’t noticed if the ignored index subsequently gets ignored again

    Reply

  17. Further joy – dropping and creating the index makes the problem go away – but only for a while! It clears the statistics for the table, but if you wait a while it will come back

    Reply

  18. We’re using 2019 version, and this issue still persists…the SQLSrver keeps suggesting Missing index…its Frustating

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course