Index combining question

  • 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 1 week, 5 days ago by  Tac11.
  • 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?

     

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • This was removed by the editor as SPAM

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

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