June 9, 2021 at 8:00 pm
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)
June 9, 2021 at 8:56 pm
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?
June 9, 2021 at 9:31 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
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