3

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.

Improve this question
5
  • 4
    So did you narrow it down to a specific query that was responsible for the increased IO? Maybe it just had a bad plan (parameter sniffing) and dropping the index caused the plan to be recompiled. A (more or less) duplicate index on its own shouldn't cause this type of issue.
    – Martin Smith
    Feb 10, 2012 at 23:36
  • Yes, the team member who found the problem found it via a suspicious query. Unfortunately, we did not have any means of reproducing the problem once the index was dropped and re-added. Parameter sniffing is an interesting theory! Can that be detected from looking at the logs?
    – Phil Sandler
    Feb 10, 2012 at 23:49
  • 1
    Not really. You would have needed to capture the execution plan. You could try and get the parameter sniffing issue to occur though by recompiling the suspect query with highly selective parameters and seeing if & how the plan changes.
    – Martin Smith
    Feb 10, 2012 at 23:56
  • Thanks Martin. Could you provide a link as to how to do that? Put it as an answer in case it's right? :)
    – Phil Sandler
    Feb 11, 2012 at 0:00
  • 1
    For what its worth, my first thought on reading the question was as per @MartinSmith's comment, bad execution plan. Probably of little value to try and reproduce the issue now but certainly communicate methods of capturing execution plans among your team in case anything similar occurs in the future.
    – Mark Storey-Smith
    Feb 11, 2012 at 0:30

1 Answer 1

Reset to default
2

Without the execution plans, it's impossible to say, but I've seen a similar scenario pop up. Had a client once whose database was teetering on the edge of being able to fit in memory. Someone added an index on a large table, queries started using it (which kept big chunks of it in memory.) Things all used to fit in memory perfectly, but they hit a tipping point where they started to hammer the disk much more frequently. We caught it via the sudden spike in disk access and a sudden drop in Page Life Expectancy.

Ideally, you should capture these Perfmon counters continuously on database servers, and alert when they drop dangerously. That'd help correlate things so you'd know if the PLE drop also happened, and that'd indicate you might be running out of memory to cache things.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.