Missing Indexes Table

Question

Missing Indexes Table

arrjay

Hall of Fame

Points: 3455
More actions
September 12, 2014 at 8:02 am

#310930

Hi All - I am using the following table to produce details on missing indexes; dm_db_missing_index_details. I would like more information on the following columns, equality_columns, inequality_columns and included_columns. I would like to know what this information means and weather I should use it. Furthermore, how I should use it. Thanks.
--------------------------------------------
Laughing in the face of contention...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398911 More actions · Answer 1

Yeah, you pretty much have to use it. Equality and Inequality columns are the columns being used within the queries in the WHERE, JOIN and HAVING clauses. They would make the key fields for your indexes. The Include columns are additional columns that can be stored at the leaf level of the index using the INCLUDE syntax for index creation. They help to make the index into a covering index. A covering index is an index that contains all the columns necessary to satisfy a given query.

Please note, you can't trust the missing index information. Take them as suggestions. Do not simply apply every index suggested. Test them to ensure they help, and don't hurt, your workload.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

arrjay Hall of Fame Points: 3455 More actions · Answer 2

Thanks for the information Grant! I'm much clearer now on what needs to be done.

--------------------------------------------

Laughing in the face of contention...

ScottPletcher SSC Guru Points: 101130 More actions · Answer 3

You can't consider missing index info in isolation. At the very least, you also need to look at index usage stats. Both of those are the minimum needed to make reasonable index modifications (creates / alters / drops).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".