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…

 missing1 Missing index DMVs bug that could cost your sanity...

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

 missing2 Missing index DMVs bug that could cost your sanity...

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:

 missing3 Missing index DMVs bug that could cost your sanity...

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!

Related Posts