Indexes - The downside

26 February,2014 by Jack Vamvas

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

Read More

 

Top 5 SQL Server DMV for Index Analysis

Find all DMV and DMF

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Indexes - The downside


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer