February 8, 2009 at 11:18 am
Hi all,
i was investigating missing indexes for 1 particular database and server where we have a little high CPU.
The problem i am dealing now is 2 indexes where recommended but both of them with the same leading column like bellow:
i did checked query plan(xml) and these 2 indexes point to 1 specific stored procedure with 2 different update statements that could benefit from these indexes.
Indexes recommended by DMV:
--CREATE INDEX missing_index_8_7 ON [warehouse].[dbo].[Sku] ([Supplier],[Date]) INCLUDE ([SkuID], [ShipTime])
-- ( 30000 user_seeks can benefit from this index). Also based on last_user_seek column it seems that this index could benefit more than the index bellow.
--CREATE INDEX missing_index_10_9 ON [warehouse].[dbo].[Sku] ([Supplier]) INCLUDE ([AvailabilityID], [ShipTime]) -- (900 user_seeks can benefit from this index)
So what i should do in this case? Is there any disadvantage to create 2 different indexes with the same leading column? It is redundant in this case?
Is there better options?
Thanks.
February 8, 2009 at 12:50 pm
They are not redundant since they have different total indexed columns and completely different included columns.
But, there is a disadvantage to having both. Remember that whenever you do any kind of write operation, all affected indexes need to get updated, so the more indexes are affected the more that write operation is slowed down. Also (though in many cases this is trivial) it will take up more hard drive space. And finally, (though this generally rears its head only in extreme cases) having a vast number of indexes can cause the optimizer to take longer to figure out the best execution as it sorts through them.
Whether or not these disadvantages are outweighed by the advantages are dependent on the situation. Is harddrive space an issue in your case? Do you do a lot of write operations? Are these common read operations? Which is more time sensitive, the write operations or the read operations?
Generally, it is better to err on the side of having too many indexes than not enough, but there is definitely a trade off to balance.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
February 8, 2009 at 1:22 pm
Great! Thank you for reply.
February 8, 2009 at 1:34 pm
timothyawiseman (2/8/2009)
They are not redundant since they have different total indexed columns and completely different included columns.
They are redundant because the index key columns of one is a left-based subset of the other, and order of include columns doesn't matter because they are only at the leaf level and hence are not sorted in any way (which the key columns are).
It's only when one is not a subset of the other, or the columns are in differing orders that they can't be merged. The following examples cannot be merged into one.
Col1, col2
Col1, col3
col1, col2, col3
col1, col3
col1, col2
col2, col3
One index is all that you need.
Key columns - ([Supplier],[Date])
include - ([AvailabilityID],[SkuID], [ShipTime])
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
February 8, 2009 at 5:41 pm
Thank you, so basically we are merging second index into the first correct?
How about the order of the INCLUDE? Does it matter in this case?
February 8, 2009 at 9:58 pm
I didn't read closely enough the columns listed to see that they could be easily merged. That would definitely be better in this case.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
February 9, 2009 at 10:09 am
trans54 (2/8/2009)
How about the order of the INCLUDE? Does it matter in this case?
As I said in the post above
and order of include columns doesn't matter because they are only at the leaf level and hence are not sorted in any way (which the key columns are).
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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply