THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Benjamin Nevarez

The Missing Indexes Feature

Since I will be speaking about the Query Optimizer at the coming PASS Summit, I have been preparing my presentation and at the same time blogging about it. This time I will describe the Missing Indexes feature, seen from the point of view of the Query Optimizer.


We know that it is the job of the Query Optimizer to find an efficient execution plan for a query. But we rarely see the Query Optimizer directly giving us indications about what it needs to produce a better execution plan. One of these cases is the Missing Indexes feature, which was introduced with SQL Server 2005.


The Query Optimizer defines what the best indexes for a query are, and if these indexes do not exist, it will make this information available in the XML plan and the sys.dm_db_missing_index DMVs. And of course, by showing this information the Query Optimizer is also warning you that it might not be selecting an efficient plan. This information shows which indexes may be helpful to improve the performance of your query. You can even use SQL Server 2008 Management Studio to display the CREATE INDEX commands needed to create these indexes, as shown later.


However, although this information about missing indexes is very helpful, this feature should not be used as a tuning tool and should not replace your own index analysis. Database administrators and developers should be aware of its limitations, as described on the Books Online entry ‘Limitations of the Missing Indexes Feature’.


So let us take a quick look to see how this feature works. Create a dbo.SalesOrderDetail table on the AdventureWorks database with the following command


select *

into dbo.SalesOrderDetail

from sales.SalesOrderDetail


Run this query and ask for a graphical or XML execution plan


select *

from dbo.SalesOrderDetail

where SalesOrderID = 43670


This query can benefit from an index on the SalesOrderID column but no missing indexes information is shown this time. One limitation of the Missing Indexes feature is that it does not work on a trivial plan optimization, like in this case. You can verify that this is a trivial plan by looking at the graphical plan properties (Optimization Level shows as TRIVIAL) or by looking at the XML plan (StatementOptmLevel="TRIVIAL).


You can avoid the trivial plan optimization by using more complex features. In our case we are just going to create a non related index



create index ix_ProductID on dbo.SalesOrderDetail(ProductID)


Note that the index created will not be used by our previous query but the query will no longer qualify for a trivial plan. Run the query again. This time the XML plan will contain something like this



   <MissingIndexGroup Impact="99.703">

      <MissingIndex Database="[AdventureWorks]" Schema="[dbo]" Table="[SalesOrderDetail]">

         <ColumnGroup Usage="EQUALITY">

            <Column Name="[SalesOrderID]" ColumnId="1" />






And if you look at the graphical plan (only SQL Server 2008 Management Studio) you will see a Missing Index warning and a CREATE INDEX command




You can right-click on the graphical plan and select Missing Index Details to see the CREATE INDEX command that can be used to create this index



Missing Index Details from SQLQuery1.sql

The Query Processor estimates that implementing the following index could improve the query cost by 99.703%.




USE [AdventureWorks]


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

ON [dbo].[SalesOrderDetail] ([SalesOrderID])





Create the recommended index after you provide a name to it. This time if you run the same query again and look at the execution plan you will see that an Index Seek operator is using the index you have just created and both the Missing Index warning and the MissingIndex element of the XML plan are gone.


Finally, remove the dbo.SalesOrderDetail table you have just created.


drop table dbo.SalesOrderDetail

Published Friday, September 25, 2009 12:19 AM by Ben Nevarez
New Comments to this post are disabled

About Ben Nevarez

Benjamin Nevarez is a SQL Server MVP and independent consultant based in Los Angeles, California who specializes in SQL Server query tuning and optimization. He is the author of "SQL Server 2014 Query Tuning & Optimization" and "Inside the SQL Server Query Optimizer" and co-author of "SQL Server 2012 Internals". With more than 20 years of experience in relational databases, Benjamin has also been a speaker at many SQL Server conferences, including the PASS Summit, SQL Server Connections and SQLBits. Benjamin’s blog can be found at and he can also be reached by e-mail at admin at benjaminnevarez dot com and on twitter at @BenjaminNevarez.
Privacy Statement