Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Location
    Leics, England
    Posts
    5

    Angry Unanswered: Strange problem INSERTing data

    Bit of a head scratcher this one (well for me it is)....

    I have an insert which will works for over 99% of the time. However for 2 particular inserts it fails, on a Timeout. The only change from other lines is that one field - which is half of a clustered index - is different (this field differs for all lines).

    There are no Triggers, or Constraints (other than not Null) on this field, there are other indexes though.

    On this basis I figured that the clustered Index has a problem and have tried to ReIndex it - using DBCC DBREINDEX through query analyzer. After 72hours this is still running! There are in excess of 13million (!!!) rows in this table so I expected a lengthy process but this seems somewhat excessive.

    We can't remove any of the rows in the table - it's store audit info so is required (we maintain a rolling 3 months worth of data). The whole DB is flakey and a replacement is being worked upon.... but we have to keep this ropey system afloat for another couple of months.

    Any help/advice greatly appreciated... I'm in danger of scratching through my cranium at the mo!

  2. #2
    Join Date
    Nov 2002
    Posts
    9

    Re: Strange problem INSERTing data

    A clustered index is not stored like other indices. Instead the records are physically placed in the order which the index is told to order the records. When you are performing the update on the clustered field it is causing the entire table to be reordered. Basically, SQL Server makes a copy of your table, places your 13 million rows in it in the new order, and then overwrites your existing table with this copy. The DBREINDEX is doing the same thing.

    To get around this, you will have to do one of the following:

    1. Do not use a clustered index on this field.

    2. Do not update any fields that are part of the clustered index.

    3. Break your data up into more managable chunks across multiple tables, then use a partitioned view to access the data.

    4. Archive your older, seldom referenced data to another table or database. If necessary, use a partitioned view for reporting and what not.

    Richard

    Originally posted by Kuthula
    Bit of a head scratcher this one (well for me it is)....

    I have an insert which will works for over 99% of the time. However for 2 particular inserts it fails, on a Timeout. The only change from other lines is that one field - which is half of a clustered index - is different (this field differs for all lines).

    There are no Triggers, or Constraints (other than not Null) on this field, there are other indexes though.

    On this basis I figured that the clustered Index has a problem and have tried to ReIndex it - using DBCC DBREINDEX through query analyzer. After 72hours this is still running! There are in excess of 13million (!!!) rows in this table so I expected a lengthy process but this seems somewhat excessive.

    We can't remove any of the rows in the table - it's store audit info so is required (we maintain a rolling 3 months worth of data). The whole DB is flakey and a replacement is being worked upon.... but we have to keep this ropey system afloat for another couple of months.

    Any help/advice greatly appreciated... I'm in danger of scratching through my cranium at the mo!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •