Ok, maybe I’m being a little harsh, but I just feel like it should be better.
Let me show you the niceness of the way that missing indexes are handled in SQL Server 2008.
Using AdventureWorks (not AdventureWorks2008) on a SQL Server 2008 install, if I show the Execution Plan from this simple query, I get a nice suggestion. My query…
select productid, orderqty
where carriertrackingnumber = ‘FB88-4B92-82’;
…could be improved through better indexing. It uses 1240 reads to get this data, which seems awful. The system shows me that it could be improved, and suggests an index.
It’s there, in green. It says:
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [Sales].[SalesOrderDetail] ([CarrierTrackingNumber])
(that’s right, no semi-colon on the end, but I’m fine about that)
What I’m not fine with is the fact that this index isn’t actually so ideal. If I create it (supplying a name, of course), we see it’s being used, and it should be clear that a better index ought to be better.
This query uses 42 reads to get the required information, which is significantly better than 1740, but still not brilliant. In fact, 42 is about 2.4% of 1740, so it’s hardly the 99.6512% improvement that Management Studio suggested would be seen.
My preference would be to consider that Key Lookup as well. it’s taking 92% of this improved query. We can avoid the Key Lookup by creating an index which INCLUDEs the columns we’re interested in. Like this:
CREATE NONCLUSTERED INDEX [MyNewIndex2] ON [Sales].[SalesOrderDetail] ([CarrierTrackingNumber]) INCLUDE (ProductID, OrderQty);
If I create this index, we see that the execution plan becomes just the Index Seek (on my new index), and the number of reads drops to just 3. Yes 3. That’s 0.17% of the original query, and only 7.1% of the reads of the improved query! 99.83% of the original reads have been eliminated – much more like the figures promised by my Missing Index suggestion, except it got it wrong.
I like the idea of detecting Missing Indexes, and I love the fact that it suggests these in Execution Plan viewer… I just want it to be slightly better by considering INCLUDEd columns.
I’ve suggested this be improved on the Connect site at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=375024