The equality_columns and inequality_columns on sys.dm_db_missing_index_details offer useful information on sql missing indexes.

Equality_columns displays columns  using equality predicates , e.g  “Select * from employee where id = 2”

Inequality_columns displays columns using inequality predicates e.g “Select * from employee where id > 2”

 How can this information be used to improve SQL Server performance?

1)  Treat the sys.dm_db_missing_index_details  as advice , rather than making the change automatically.

2)  Evaluate the advice – and make decisions after testing and within the overall SQL Server performance

3)  Index order is important.  Consider a telephone book – what is more useful , searching by first name or last name? The answer is last name.

A similar concept is relevant to SELECT statements and the use of indexes. What will the SELECT statement search first?

It is important to understand the SQL statements used – otherwise you could end up creating more of a problem

The advice from Microsoft is to follow these guidelines

a)          List the equality columns first (leftmost in the column list).

b)           List the inequality columns after the equality columns (to the right of equality columns listed).

c)           List the include columns in the INCLUDE clause of the CREATE INDEX statement.

d)           To determine an effective order for the equality columns, order them based on their selectivity; that is, list the most selective columns first.

 
Using the advice on the second row , we’d create a Non-Clustered index  as :

USE [MYDB]

GO

CREATE NONCLUSTERED INDEX [NC_TEST_IDX] ON [dbo].[MYDB]

(

      [TYPE] ASC,

      [FROM_CURR] ASC,

      [TO_C] ASC,

      [FROM_MONTH] ASC,

      [TO_M] ASC

)

INCLUDE ( [ROJ]) WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

 The equality_columns and inequality_columns on sys.dm_db_missing_index_details are very useful when analysed along with the sql queries  used. How are those queries using the indexes?

Make sure you’ve updated your sql server statistics , SQL Server Optimizer and statistics work well togetherto ensure the tests are accurate, otherwise you may receive  incorrect results.

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