Indexes are fantastic. They make queries fly! Even a badly designed index can sometimes be better than no index. But any reasonable index review and index tuning strategy should consider the downsides of indexes
Storage costs for indexes. Indexes can use large amounts (and costly) hard disk space. On the whole, it’s mainly OK – as the benefits outweigh the costs.
Indexes are objects for storage in buffer space. Prefetching techniques exploit the possibility of improving performance by prefetching indexes and reducing IO. Depending on the range of queries and the buffer space size , there is a possible negative performance impact for certain range of queries
Placement of indexes on (dedicated) disk should be considered. There are many benefits including distributing IO over different channels, minimising the impact of maintenance
Costs for index updates . When a new row is created or updated and indexes are in-place , the index is modified – adding to the query cost.
Index usage may be to costly in a scenario with many constant updates on database objects. Some examples is sensor data and weather tracking which reports on constantly moving objects
Index key updates can trigger a reorganization
Locking overhead and lock conflicts Indexes are potential performance hot spots. This will depend on cardinality and INSERT patterns such as sequential
Write operations and reorganizations may more easily cause lock conflicts and deadlocks.
These are some issues to consider when designing an index strategy. As well as the initial design to support the queries , a regular index review can yield good results for minimal effort. For example, creating a report on index usage is straightforward and can assist application owners in index management