I have table with 17 Indexes if i add 1 more index for search in two columns insert , update and delete queries will be slow.I dont want them to slow...what to do
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
closed as not a real question by Paul White♦, Jack Douglas♦ Feb 16 '13 at 8:41
It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.
Indexes are always a tradeoff of read performance vs write performance. In the end you have to determine which indexes to keep and which ones to discard. This is a tradeoff which has to be guided by your specific needs and there is no single right answer here necessarily.
In general, the first thing you need to do is go through and find out which indexes you can throw out. MS SQL has a decent planner and I would be surprised if all indexes are required for good performance. Once you throw out the indexes you don't need then add new indexes you do.
It is possible though highly unlikely that in fact you do need all these indexes. However to get 17 indexes on a given table, I think you need to be trying to justify so many indexes in the first place....
The answer, as Chris said, is to figure out why you have that many indexes, and which are duplicates or are unnecessary. BrentOzar.com has a couple of videos as to how to clean up indexes.
My first suggestion is to find the unused ones. This video does a good job of explaining how to do this. I run a slightly tweaked version of the code used there:
I would modify that code and add a where clause to narrow it down to the table you're working on. Flat out remove any indexes that have a reads_per_write of 0, they're not used.
After removing (if any) of the unused indexes, I would examine the ones that have a reads_per_write of 1 or less. These are indexes that are read less than or equal to how much theiy're written to. Figure out why these were added, and if those query could be modified to match an already existing highly used index.
After that comes combining and simplifying indexes. That topic's a bit detailed and complicated for an answer here. The best move forward is to watch the index videos on BrentOzar.com.