Index column order in Index Missing Feature

When troubleshooting poor performing SQL queries by analysing Execution Plans, there’s an Index Missing feature. The Index Missing feature appears at the top of the Execution plan results.  Although tempting , avoid implementing the suggestions without completing some analysis of the workload queries.

I’ve noticed a Index Missing feature behaviour , which may lead to a non-optimal index solution. When the Index DDL appears, it doesn’t present a solution where the sort order is optimal.  It’s only through the DBA analysing the workload queries which leads to a sort order decision.

Look at this example:

SELECT col_1, col_2,col_3
FROM tbl_1
WHERE  col_2 < 10 AND col_3 < 10
ORDER BY col_3, col_2

The index missing feature will suggest a sort order of col_2 , col_3, whereas the optimal sort order may be more optimal by sort order of col_3, col_2. 

The key point is to analyse the work queries against the suggestions made.  There  may also be other queries either as stored procedures or as ad-hoc queries in applications which may utilise the new queries. You’ll need to assess how often these queries are executed and measure the impact if the query does not match the index.

Read More on How to Tune a SQL Query

 How to tune every query - SQL Server DBA

How to troubleshoot a slow running query in SQL Server - SQL ...

SQL Server – Find high impact queries with sys.dm_exec_query_stats