none
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

ตอบทั้งหมด

  • Hi,

    When did you run DBCC CHECKDB? If it was just after you drop the second table, you may missed the corruption. Check the SQL Server error log + Windows event log for IO related errors.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    27 กุมภาพันธ์ 2555 13:15
  • Yes - it was after the drop/recreate... But is it really feasible that one table (that spends 99% of it's time being empty) could be corrupted - yet nothing else in the database is corrupted? It doesn;t sound very likely.

    And there are no errors in the SQL or Windows logs that might explain things... I'm thinking "magic pixie dust", but the customer will have a hard time believing me.

    27 กุมภาพันธ์ 2555 13:37
  • Are you sure that it was this update which was the head blocker. If any other thread was the head blocker, then when you dropped the table, obviously the query would have failed, which resolved the blocking and then when you created the table again and ran the update the blocking never occurred.

    Could that be a possibility?


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    27 กุมภาพันธ์ 2555 13:44
  • Hello,

    If you are sure that the trigger was to blame, dissect the code in it and see what the table is used for. Otherwise it's ahrd to diagnose and solve the issue after it's gone without any other information.

    -Sean

    27 กุมภาพันธ์ 2555 13:57
    ผู้ตอบ
  • Absolutely positive this was the head blocker.... everyone else queued up behind it every time the update ran and invoked the trigger. So long as the trigger was disabled, no problem....
    27 กุมภาพันธ์ 2555 14:01
  • The trigger was to blame. Disabling the trigger stopped the issue. Re-enabling it brought the issue back.

    Looking at the code, the statement causing the issue was basically this:-

            INSERT INTO dbo.TableName
           SELECT DISTINCT inserted.ViewID FROM inserted
           WHERE inserted.Status & 0x08000000 = 0
           AND NOT EXISTS (SELECT 1 FROM dbo.TableName vsmc 
           WHERE inserted.ViewID = vsmc.ViewID)

    (name if table changed to protect the innocent). If I commented this out of the trigger all was well. If I created a dummy table and changed the above to use that table - no problem. The table (TableName) was empty throughout everything. Then I dropped and recreated "TableName" and the problem went away.

    It seems very weird.

    27 กุมภาพันธ์ 2555 14:29
  • Just an assumption that, If there was an open transaction to that table (in the trigger) then it can cause all of these problems that you explained. It would not cause problems when the trigger is not used, when the table name is different and would go away when it is dropped and recreated.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    28 กุมภาพันธ์ 2555 9:16
  • I understand that... there was no open transaction on that table. And if there had been, and the code leading up to what the trigger was doing - then dropping and recreating the table would not change things.. the issue would just return on the next update.

    But like I say - no transaction on that table when the problem was happening.

    28 กุมภาพันธ์ 2555 9:25