Explain why recreating a table fixed performance issue


  • I'm trying to find an explanation for why an action I took fixed a problem... The problem was that an update to a table was basically hanging - and in turn was causing database blocking so other processes were also hanging. It didn;t matter what the update was.. so for example:-

    update ViewHierarchy set Status = 1 where ID = 1

    which affects 1 row would cause the problem. I tracked down that a trigger on the table was basically taking a value from the updated row and using it to insert into a 2nd table. That 2nd table was empty and had only one column.

    I ended up dropping and recreating the 2nd table (remember... has one column and was empty). As soon as I did that the problems went away and the original update was practically instantaneous.

    I did a DBCC CHECKDB to see if there were any problems generally in the database - none whatsoever. So why would dropping and recreating that one table fix the issue? To say the table was corrupted sounds rather tenuous....

    26 กุมภาพันธ์ 2555 10:11