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 reading on the leaf page. If we use a range predicate in our SQL query or do not have a unique index, SQL Server will read forward or backward the leaf pages with the support of the doubly linked index chain. In this way, SQL Server reads only the pages that match the searchable argument, and therefore drastically reduces IO reads.
That is why SQL queries (SELECT, UPDATE and DELETE) normally will benefit from a supportive index.
With this understanding, consider the index consists of two columns, col1 and col2. If we are looking for a col2 in the index, SQL Server cannot use the index to seek for col2 because col1 is the leading column and therefore the index is sorted by col1 and not col2. SQL Server will scan the entire index or the table instead.
Another example where the order of the index columns determines if SQL Server can use an index is when there is a need to sort the data either by an explicit sort operator or because SQL Server decided to use an operator like Stream Aggregate or a Merge Join in the query plan. These operators requires sorted data. If the index columns are not in the correct order, it is not possible to use the index to sort, aggregate or join. Therefore, we need to consider the order of index columns when creating an index.Let us go into detail now by running a query on a heap without any non clustered index to get the missing index hint from SQL Server.First, we create a table based on AdventureWorks2017.Sales.SalesOrderDetail. They way we a creating the table ensures we do not create any index at the same time. 
USE AdventureWorks2017;
GO

-- Create a demo table
DROP TABLE IF EXISTS dbo.SalesOrderDetail;
GO

-- Create a heap table based on Sales.SalesOrderDetail
SELECT * INTO dbo.SalesOrderDetail FROM Sales.SalesOrderDetail;
GO

Now, let us run a simple query. The final result should be sorted by SalesOrderDetailID and SalesOrderID.

-- Sort operator
-- Memory grant : 23016
-- Costs : 7,39451
-- Table 'SalesOrderDetail'. Scan count 5, logical reads 1497
-- The Query Processor estimates that implementing the following index could
-- improve the query cost by 9.94838%.
SELECT *
FROM dbo.SalesOrderDetail
WHERE
	SalesOrderID > 43670
AND SalesOrderDetailID > 112
ORDER BY
	SalesOrderDetailID, SalesOrderID
GO

In the execution plan we can see that SQL server recommends to create an index because with the use of the index the overall costs can be reduced by 9.94838%. Currently we scan the entire table which costs 7.39451 and therefore SQL Server even decides to run the query with all possible cores (I did not changed the value 5 for the Cost Threshold for Parallelism). Additionally, an explicit sort operator is used which needs memory grant of approx. 23MB. This query will not scale and it will definitely benefit from a supportive index to avoid the sorting operator, memory grant, parallelism finally and reduce IO reads.

So let us create the index SQL Server recommends.

-- Index recommendation from missing index dmv
-- Create a non clustered index including all columns
CREATE NONCLUSTERED INDEX [wrong_order]
	ON dbo.SalesOrderDetail (
								SalesOrderID
							  , SalesOrderDetailID
							)
	INCLUDE (
				CarrierTrackingNumber
			  , OrderQty
			  , ProductID
			  , SpecialOfferID
			  , UnitPrice
			  , UnitPriceDiscount
			  , LineTotal
			  , rowguid
			  , ModifiedDate
			);
GO

We can discuss if it is good idea to include all columns in the index leaf pages or simply create a clustered index but let us concentrate on the order of the index columns. SQL Server wants us to build the index pages in the order SalesOrderID and SalesOrderDetailID.

After creating the index and running the query again the execution plan looks like this:

-- There is no improvement by using the suggested index from missing index dmv
-- Index colums are in the wrong order
-- Sort operator
-- Memory grant : 24960
-- Costs : 7,39309 
-- Table 'SalesOrderDetail'. Scan count 5, logical reads 1497
SELECT *
FROM
	dbo.SalesOrderDetail
WHERE
	SalesOrderID > 43670
	AND SalesOrderDetailID > 112
ORDER BY
	SalesOrderDetailID, SalesOrderID
GO