In the last post
you saw that the missing index feature does not consider trivial plans.
In this blog I will explain another limitation of the missing index feature – it does not specify an order for columns to be used in an index.
SQL Server can only benefit from a row store index if the key columns are in the correct order. The order of the columns of an index determines whether SQL Server chooses an index scan or seek. Scanning an index simply means that the storage engine of SQL Server will read all index pages and filter the desired records afterwards (residual predicate).
The costs of the query execution is proportional to the number of pages SQL Server must read. An index seek on the other hand will navigate through the B-Tree of the index. SQL Server begins reading the appropriate index root page. From there it is directed directly to the page of the intermediate level and finally stops