Rob Farley

I don't claim to know what's in the mind of the people who wrote the algorithm that suggests missing indexes. But here's my guess.

The best index for any query, any simple select query at all, will be a covering non-clustered index (like you suggested).

It's always a bit of an art and a science isn't it? A bit of a balancing act.

We can make every select statement we analyze superfast by implementing the right index.

But the risks are: Expensive updates, too much space. etc...

My guess is that the database engine people deliberately took a step back from recommending non-clustered covering indexes for everything and just stuck with the non-clustered part. Giving up included columns and accepting lookups.

But that's just my guess.

Kinda odd. When I run that query on Adventureworks2008 (SQL 2008 RTM x64) the missing index it lists is

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [Sales].[SalesOrderDetail] ([CarrierTrackingNumber])

INCLUDE ([OrderQty],[ProductID])

I would hope it's not a 32/64 issue! I'd be very worried if some of the core features (optimiser) behave differently on 32/64. Unfortunately I don't have a 32-bit server to test on.

Can you check the missing index DMV, see if the include columns are listed there? Also, maybe have a look in the exec plan's XML at the missing index section.

I'm curious as to whether it's the optimiser not noticing that the include columns would be useful, or management studio just displaying the missing index details wrong.

FWIW, most of the time I find the missing indexes to have too many include columns (up to the point of including the entire table), not too few.

What build of 2008 are you running there?

I disagree here.  I think the DTA goes way TOO far in INCLUDING columns to create covering indexes.  I have seen it absolutely destroy databases (and database performance) with bloated indexes.  I just cleaned out 60% of the databases a client had in their database because they ran DTA on a workload and accepted everything.  Massive bloat, blocks and locks all over the place.  The indexes I left caused just a small amount of read increases for select activity but made the DML stuff WAY faster.

Whether this index should include ProductID or not depends only on whether you decide its important for the query to complete in 3 reads vs 6 & that the overheads associated with having the extra column included are worth the minicsule saving in reads.

Index maintenance overheads are often described in terms of DML overhead (updates etc). Less widely recognised is the extra space index columns take up, which have daily implications for disk space management, backups, index maintenance & log shipping. These are real problems which should be taken into consideration when deciding to simply throw an extra column onto an index.

Adding extra columns onto indexes should be given similar consideration to throwing extra columns onto tables..

I think it is kind of funny that when run Rob's query and then look a the missing index stats the included_columns are not showing up when running the following query:

SELECT *  

FROM sys.dm_db_missing_index_groups AS g    

join sys.dm_db_missing_index_group_stats AS gs ON gs.group_handle = g.index_group_handle    

join sys.dm_db_missing_index_details d ON g.index_handle = d.index_handle;

It might be nice to know why the optimizer is not picking up these included columns.  

But now if you change Rob's query to be an inequality query like so:

select productid, orderqty  

from sales.salesorderdetail

where carriertrackingnumber > 'FB88-4B92-82';

Now the suggested missing index now has the appropriate INCLUDED columns.  

Oh, by the way all my testing was on a 32bit machine.

The missing index suggestion in SQL 2008 SSMS is like DTA command line util in SQL 2005.

Nice feature.

http://sqlkingz.blogspot.com