Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: Help! Indexing LARGE table

    OK, I imported 680 million records into an unindexed table. That went well.

    Then, I went into Enterprise Manager and added a two column non-unique clustered index to that table to speed access.

    It's been running for ~36 hours and I have no idea when it will complete. I have deadlines that I'm going to miss and am very nervous; what can I do?

    SQL Server 2000 Enterprise Edition (8.00.818 - sp3 + hotfixes)
    Dual 3Ghz Xeon (two physical CPUs each have HyperThreading enabled)
    Windows 2000 SP4
    4GB RAM (although I just noticed the 3GB OS switch wasn't on)
    SCSI boot drive
    tempdb, data, and transaction log are on a FibreChannel RAID SAN

    Help! Thanks in advance!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by RogerWilco
    OK, I imported 680 million records into an unindexed table. That went well.

    Well what? Into the night?

    Then, I went into Enterprise Manager and added a two column non-unique clustered index to that table to speed access.
    Yeah, you'd want to do that with almost a billion records (hey let's not split hairs)

    It's been running for ~36 hours and I have no idea when it will complete. I have deadlines that I'm going to miss and am very nervous; what can I do?
    Pray?

    SQL Server 2000 Enterprise Edition (8.00.818 - sp3 + hotfixes)
    Dual 3Ghz Xeon (two physical CPUs each have HyperThreading enabled)
    Windows 2000 SP4
    4GB RAM (although I just noticed the 3GB OS switch wasn't on)
    SCSI boot drive
    tempdb, data, and transaction log are on a FibreChannel RAID SAN

    Nice box...do you have to buy it dinner?


    Help! Thanks in advance!
    Help with what? You defenitley DONT want to kill it...it'll take twice as long to rollback.

    Seems you get a lot of these. I would definetly start padding my estimates...

    OR, I would think about performance before....

    Without knowing you're data, I would still say that this is a MAJOR partition canidate, into many file groups on many different devices....

    But that's just me

    OH

    MOO

    Good Luck
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I agree with Brett that killing the job will be bad. A clustered index looks to order the rows of the table on the disk itself. If SQL Server decides that these rows are in exactly the revers order of what they should be, then you are going to see an awful lot of data move around. Your transaction log is probably going nuts, too.
    As for speeding it up, there isn't a lot you can do while the process is in flight. If this import is a normal thing, then you can consider partitioning the table on some unique key, and cutting it down to seven 100 million row tables. Also, for non-clustered indexes, you can get a boost from the SORT_IN_TEMPDB option. Take a look in BOL for that.

  4. #4
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by Brett Kaiser
    Without knowing you're data, I would still say that this is a MAJOR partition canidate, into many file groups on many different devices....
    Yes, horizontal partitioning will be a necessary move. My data isn't primarily organized by date so that wouldn't make an ideal partition criteria. I haven't done this before and am nervous about doing it correctly; mistakes at the planning phase will be hard to correct.

    Your reply is a mix of helpful advice and frustrated sarcasm. I am thankful for the helpful advice and probably deserve the sarcasm.

  5. #5
    Join Date
    Oct 2003
    Posts
    268
    ok, it just finished rolling back. The data file was on a 150GB SAN partition and ran out of space. It was previously like 73GB so I'm surprised that happened. any way, at least I have the database back...

    thanks guys

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by RogerWilco
    Your reply is a mix of helpful advice and frustrated sarcasm. I am thankful for the helpful advice and probably deserve the sarcasm.
    Well I was trying to get you to laugh..not to make fun of you

    Why not post the DDL of the table...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Got me to laugh, anyway. Does that count as a near miss?

    Roger: At this point, maybe you can try the index as a non-clustered index. Let us know what happens.

  8. #8
    Join Date
    Jul 2004
    Posts
    52
    Is there any chance the import file is already sorted? If so you can create the clustered index on the empty table and use the ORDERED argument with BCP to prevent SQL from resorting the data.

  9. #9
    Join Date
    Jul 2004
    Posts
    52
    Quote Originally Posted by Brett Kaiser
    Well I was trying to get you to laugh..not to make fun of you
    Brett's gone off to Flordia for vacation. We can make fun of him while he is gone.

Posting Permissions

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