One Index vs Multiple Indexes on the table

[wg26]

Hi Everyone:

I have a table,which has been used in several queries. I have created one non clustered covering index,which covers all the columns retrieved for all the queries,besides the clustered index on the table...for each query, this nonclustered index is used. But for some queries, query optimizer suggests that the order of the columns in the index should be changed or number of columns should be reduced based on the query to increase performance...for others, there is no suggestion...I am wondering if I should keep one covering index on the table so that it will increase data insertion performance and save hard disk space or I should create multiple covering index for each query to increase data retrieve speed...Can anyone please give me some suggestions on which way is better and If multiple indexes on the table will really affect the performance? Thanks alot...

 

[topher0303]

Multiple indexes would be better. The more unused data you put in an index for a particular query will slow down your index seeks/scans. That is why it is telling you to cut down on the columns.

As for column reording, it sounds as if the seek is unable to be performed on all the columns in your queries criteria even though they exist in the index. For example, if your where clause filters on column A, C, D, E and your index is on column A, B, C, D, E, F, G, it is only doing a seek on column A. It cannot use column C, D, & E in the index seek because column B seperates them which is not in your queries criteria. Removing column B from the index or moving it to the end would cause the four columns in your queries criteria to be the first four columns in the index, thus, allow a seek to occur on all four columns rather than just the A column.

Also, covering indexes should be used in moderation. Too much of a good thing ends up being a bad thing in this situation.

Chris.

 

[sguslan]

I would create another index to help with the other queries. Make it simple with one or two columns no more. I think having up to 4 or 5 indexes is OK. It should not hamper write performance much ....
As for your current index make sure to reduce the number of columns to the mix that really help. Having more columns in the index may not help at all.

 

[nikhilparchure]

Well multiple index to an extent are good.
Experince I had is that i had about 6 indexed on a table

A specific Query was not giving me optimal performce inspite of having these indexex.

So i decided to add a new index ..To my surprise the query went for a table scan

The columns added in the new index had columns from other indexes , which confused the SQL server to identify the best index and hence it didn't take a single index and went for a table scan ...

Check this doesn't happen to u!!!
Regards
nikhil

 

[wg26]

Thank you, topher0303,sguslan and nikhilparchure for your very helpful input.

I added one more covering index on the table. So there are totally one clustered index and two nonclustered covering indexes. It works well. Thanks alot...