question

liton avatar image
liton asked

are too many indexes good for performance?

We have a database where we recreate many tables each day. It takes long time for it to run. Most of them are bulk insert and them some update to that tables by joining with some other table. Theirs is one table which takes majority of the time to load. It has 60 fields but the person who created has 40 nonclustered index on 40 columns. I read that indexes decreases performance during update and insert but not too familiar with indexes. I’m a new guy and not sure whether I should start removing the indexes. My question is, does it make sense to have 40 columns with nonclustered index when I have only 60 columns in the whole table? If I’m doing bulk insert and update then does it make sense to have indexes in the script? Thanks.
index
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
I do not know it would prove good in your case, but you can definitely try these tasks in an order - disable (Do not drop) all non-clustered indexes for this specific table - Run the batch - Rebuild all the non-clustered indexes for this table (Since all the meta-data of the indexes is intact hence, no major change) This method may affect for good in ways like the fragementation level of the indexes would be very low, you would have the statistics with full scan etc. But only if the update time remains with in the limits or the execution time decreases.
0 Likes 0 ·

1 Answer

·
Tim avatar image
Tim answered
Indexes help make searching for data quicker. Wether you need 40 non clustered indexes on your table depends on several factors. Are the indexes all used, would covering indexes be better, are an single non clustered indexes covered by another covering index, etc. I have run into situations much like yours where I have tables that have nearly each column with a non clustered index. In my cases I have been able to evaluate that many are not used and could drop them, others were used but the overall query was not efficent and subsequent covering indexes were applied and additonal non clustered dropped. I am not saying that would be best in your case but I would be willing to put money on it. The big question I have is "Is end user experience ok with viewing the data", have you looked at the high usage execution plans to see if the queries are optimized doing index seeks verses scans and bookmark lookups? As for the impact of the indexes with a bulk insert, yes indeed the 40 indexes are impacting the insert. The data is having to update all 40 indexes so that 100k import is 4000k inserts. The question though is does this impact the customer? If it is a batch process that runs off hours, who are you tuning it for? Is this a database that you can tune the indexes on, or is this a third party vendor app that you have to be careful with. It is a candadate for you to dig into and analyze further. You mentioned the 40 non clustered, is there at least a unique clustered index?
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

liton avatar image liton commented ·
The end user is happy with viewing the data. They are using a reporting tool (tableau) to access the database. It runs during the off hours. Client access the database after the update. I want to reduce the update time so client can access the database early but don’t want to impact client’s performance. There is no clustered index.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Unless you have a good reason not to (a tested, documented, validated reason), every table should have a clustered index. Look to the access patterns in the queries against the table to identify the most common,unique, access path. That's usually your best candidate for a clustered index.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.