We recently has a massive performance problem on our system, which we narrowed down to a problem with I/O on the LUN. IOPS increased twenty-fold during the period when the problem was occuring. The LUN in question was the one that tempdb uses. This is all according to our network/server admins (I have no network/SAN/Server expertise).
Everyone on the team was investigating the problem, until one team member found an expensive query in activity monitor that look suspicious. He inspected the table and found and index that was a duplicate. Actually, it was technically an overlap, since it had one additional included column over and above from the original index.
The overlapping index had been in production for about three weeks with no problems.
The index was dropped in production, and the performance problems stopped almost immediately after We were not all in sync monitoring and trying fixes (never mind that this isn't a good practice). So it's possible it was a coincidence, but no other changes were made in the same timeframe, and we're 99% sure this index was somehow the cause.
I realize a superfluous index can cause performance problems, but I have never heard of a case where the problem would be that pronounced. Adding the index back also caused no problems (I know--also not a good practice in production).
Any idea of what the root cause of the problem could have been? The duplication? A corrupted index (if such a think exists)?
Any online sources as references would be deeply appreciated as well.