Sys.dm_db_missing_index_details and Advisor

Question

I run this query

SELECT * FROM [dbo].[Bets] 
WHERE [BetAmount] =1000

and after that i check

SELECT * FROM sys.dm_db_missing_index_details

the result from the last one is

then i used the Tuning Advisor and and get the following result it advises me to create the following index-

SET ANSI_PADDING ON

CREATE NONCLUSTERED INDEX [_dta_index_Bets_5_277576027__K4_1_2_3_5_6] ON [dbo].[Bets]
(
    [BetAmount] ASC
)
INCLUDE (   [BetID],
    [PlayerID],
    [GameID],
    [BetDate],
    [BetResults]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

My question is should the advisor and the sys.dm generate the same result?

Answer 1

Personally I would take anything that either the DMV or Tuning Advisor 'advises' with a pinch of salt. Index Tuning is an art-form that neither of these two tools have got quite right yet. You're much better off seeing what indexes you have so far and adding/tweaking them to target a wider range of queries than simply adding indexes to target one query.

Anyway...I digress...

So the following query:

SELECT * FROM [dbo].[Bets] 
WHERE [BetAmount] = 1000;

....gives the suggested Index:

CREATE NONCLUSTERED INDEX IX_Bets_BetAmount
ON dbo.Bets(BetAmount);
GO

Based on that one query (and that one query alone), SQL Server has determined that any benefit that would be gained by including any columns in the index is negligible because the number of rows returned is likely to be few and a Key Lookup on the Clustered Index is almost as efficient as an Index Seek would have been.

The statistics on the existing table could determine that a query on the bet amount of 1000 would return a very low number of rows.

However, you could quite easily run a different query that returns more rows (say an amount of 10):

SELECT * FROM [dbo].[Bets] 
WHERE [BetAmount] = 10;

...SQL Server may now see the benefits of including the extra columns in the index.

CREATE NONCLUSTERED INDEX IX_Bets_BetAmount 
ON [dbo].[Bets]([BetAmount] ASC)
INCLUDE ([BetID], [PlayerID], [GameID], [BetDate], [BetResults]);
GO

The difference between the DMV and the Tuning Advisor is that the Tuning Advisor takes an overall look at the activity on your database, as opposed to a one off query.