In Recovery...

(In this post I'm not going to name-and-shame, as I'm sure the problems will be fixed in time.)

I want to warn you about unthinkingly acting on advice from 3rd-party tools around dropping nonclustered indexes. One of my long-term clients recently bought and installed a new tool and has been asking me about recommendations from it concerning indexes I've added to their system.

Case 1: recommendation to drop an index because its key is a left-based subset of another index key, without considering INCLUDEd columns

The first index has two narrow key columns (A, B) and is scanned as part of a join. The second index has a key of (A), plus 3 wide varchar columns as included columns, and is seeked as part of a join. The tool considered the second index to be redundant and recommended dropping it to save on index maintenance costs. Even combining the two indexes would have a strongly detrimental performance impact on the first join, and the index maintenance has a negligible effect on the system performance.

In this case it turns out that the product does not consider INCLUDEd columns in the nonclustered indexes when making these recommendations – a cardinal sin in my opinion.

Case 2: recommendation to drop an index because it hasn't been used since the system last rebooted 10 days ago, without considering an entire business life-cycle

In this case the index in question is used to help a once-per-month process not result in a hash join with a huge worktable that spills to tempdb. The recommendation from the tool did not even suggest waiting for an entire business life-cycle before considering dropping the index – standard advice when considering information from sys.dm_db_index_usage_stats. I'd be interested to know whether index usage statistics are being persisted by the tool over system reboots so that recommendations are not given solely on post-reboot usage statistics.

Summary

In each of these cases I knew exactly why the index was required and the recommendation from the tool was incorrect. The client wasn't sure and so asked me.

You need to be careful out there – just because a tool says to drop an index, it doesn't mean that the developers of the heuristic algorithms the tool is using know what they're doing, as has been shown in this case.

Don't get me wrong – most tools are excellent and can save you time and money, but you need to be *extremely* careful any time dropping an index is recommended.

2 Responses to Beware of advice from 3rd-party tools around dropping indexes

Excellent advice Paul. It is hard for vendors to make tools that can satisfy everyone, but it makes the DBA job even harder when the recommendations themselves do not take into account the fact that business rules change from one shop to another.