Index combining question

Question

Index combining question

Tac11

SSCertifiable

Points: 6980
More actions
June 9, 2021 at 8:00 pm

#3894055
I ran a report and execution plan suggested a 'missing index' with 4 index key columns and 4 included columns. I created the index and ran the report again it gave me 'missing index' suggestion again with now 2 key columns and 2 included columns which were part of the missing index of the first time.
I added those again and it ran even BETTER with no 'missing index' suggestion.
My question is since 2nd index columns are already in the 1st index how can I make only one index instead of 2? another word how to combine? Thanks
Example: First missing index suggestion:
Create NonClustered index 11111 (col1, col2, col3, col4) on mytable inculde (col5, col6, col7, col8)
Second index suggestion
Create NonClustered index 22222 (col1, col4) on mytable inculde (col7, col8)
- This topic was modified 3 years, 5 months ago by Tac11.
June 22, 2021 at 5:53 am

This was removed by the editor as SPAM

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

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

ratbak Ten Centuries Points: 1358 More actions · Answer 1

What queries use those indexes? Do they (always) use the same columns as predicates (where clauses, joins, order bys)?

Any queries that use the index must use the first index key as a predicate. If a query doesn't use that first index key, it won't use the index... or perhaps will use it very badly.

The second recommended index doesn't include col1 or col2. Are there multiple statements (or subqueries or CTEs) that use the same table in multiple ways?

ScottPletcher SSC Guru Points: 101095 More actions · Answer 2

Would have to see the query's use of col1 thru col8: in WHERE, JOINs, GROUP BY and even the SELECT itself.

SQL Server's missing index feature doesn't properly "understand" the importance of key sequence, thus it may not suggest the best first key for the index. And, for best performance, the first key is the most important.

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".