November 17, 2010 at 9:04 am
I recently accidentally started a discussion at work about DTA. It had suggested an index on ColA, ColB, ColC, INCLUDE (ColD, ColE), when there was already an existing index on ColA, ColB, ColC, INCLUDE (ColD, ColE, ColF). So they were indexing on the same columns but with one fewer included column.
Can anyone think of a scenario where having both indexes would be an advantage? The only ideas I've had are very tenuous:
1) if ColF is a very wide column, one index will be smaller than the other.
2) if the two indexes are on different drives, and the query pulls one set of data from one index and one from the other, data can be read quicker.
P.S. those aren't real column names. And neither above scenario applies in my case.
November 17, 2010 at 9:55 am
Virtually none. There are some specialised cases where having two near identical indexes is advantageous, but it' mostly about the width of the index and it's not common.
This is one of the reasons I really don't like DTA.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply