Missing Indexes. Equality and Inequality on sys.dm_db_missing_index_details

01 July,2011 by Jack Vamvas

The equality_columns and inequality_columns on sys.dm_db_missing_index_details offer useful information on sql missing indexes when  Troubleshooting a slow running query 

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. SQL Server performance tuning is about Asking the right question

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.

Equality columns 

 

 
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.

Read More

Top 5 SQL Server DMV for Index Analysis - SQL Server DBA

Performance Tuning - Save hundreds of hours - SQL Server DBA

Top 5 habits of Performance Tuning Experts - SQL Server DBA


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 Missing Indexes. Equality and Inequality on sys.dm_db_missing_index_details


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