Index column order in Index Missing Feature

25 January,2015 by Jack Vamvas

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

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Index column order in Index Missing Feature


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer