Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2005
    Posts
    44

    Red face Unanswered: Performance Creating clustered index

    Hi Guys,

    I have a SQL 2000 sp3a server on Windows 2000 sp4. Running dual proc server with hyper threading enabled, 3gb memory attached to a HP EVA 5000 SAN.

    One of the tables is 67gb and contains 140,000,000 rows. Recently someone dropped the clustered indexe so i`m trying to put it back (i've dropped the non clustered indexes as no point leaving them there whilst clustered builds).

    The problem i am having is the rebuild is taking forever!! It ran for 23 hours before someone rebooted the server (!). The database is currently recovering from the reboot but i need to work out what is causing the appalling performance so i can get the index rebuilt. There are no reported hardware problems.....

    There are multiple file groups involved and i found i was getting an extent allocation rate of 1.5 extents a second and same for deallocation.

    Any advice on how to trouble shoot this?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are there any other existing indexes on the table?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by ACALVETT
    dropped the non clustered indexes as no point leaving them there whilst clustered builds

    Dude, drink your coffee


    Recently someone dropped the clustered index
    Why? And did they get fired?

    One of the tables is 67gb and contains 140,000,000 rows.
    That's a lot of data...what's it contain? 104 millions rows, does not seem to equal 67 gig though...anyone else think there's a disparity here?

    Is the database 67?

    Are people actively banging away at 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.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Brett Kaiser
    Dude, drink your coffee
    I think I've been drinking the Kool-aid...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jun 2005
    Posts
    44
    Hiya

    Blindman - There are no other indexes as i removed them since they would get rebuilt anyway when the clustered is created so decided to avoid the additional overhead.

    Brett - Unfortunately it was a new guy with little experience of SQL, rather than speak to his colleagues when working on a problem he took it upon himself to deal with it and here we are!

    140 mill rows, SQL is definately saying 67gb for the table. DB size is 80gb. The database logs our users internet activity.

    There is a connection from the logging process which is trying to access the table and obviously be blocked.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Logging internet activity...why isn't this a heap table in the first place...he may have done you a service...

    I also assume that you report on the activity....so that's why you need the indexes....Sounds like you have many....

    I would split he table in 2....1 for current logging, and one for reporting, and have the reporting....

    I would have a nightly process to archive the data, with a bcp in native format, and load the archive/reporting table with a bcp...

    That eliminated down time and your blocking.

    It also frees up to do maintenance.

    What period of time do you report on?

    Since you're hosed, I'd implement this strategy now.
    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
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Concur, agree, affirm.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jun 2005
    Posts
    44
    Cheers Brett,

    Yep spot on, period of time for reporting is up to 6 months.

    I agree with everything you say but its the classic third party product scenario where we can not change anything if we want support etc etc.

    The 23 hrs + is my real problem though it should not be taking this long to build the index. Ohh well, time to put the data into a new table and go from there (once the database has recovered, its been 4 hrs so far).

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    If tempdb is on separate disks from the filegroup containing your data, you can try to create the index with SORT_IN_TEMPDB. I had to check if it was valid for clustered indexes, and it looks like it is. Here is the bit from BOL:
    Quote Originally Posted by BOL
    SORT_IN_TEMPDB

    Specifies that the intermediate sort results used to build the index will be stored in the tempdb database. This option may reduce the time needed to create an index if tempdb is on a different set of disks than the user database, but it increases the amount of disk space used during the index build.
    It may not be much, and it may blow your tempdb up in size, but it is certainly worth a shot on a test server.

  10. #10
    Join Date
    Jun 2005
    Posts
    44
    Cheers, i`ll give it a go.....

  11. #11
    Join Date
    Nov 2009
    Posts
    1

    Question which type of index is suitable ?

    Suppose there are 2 tables.

    First table is customer table having a primary Cust_id

    2nd table is order table having Cust_id as reference key.

    There is no primary key in 2nd table (order)

    Please suggest me that which type of index is suitable on 2nd table (order) & why ?

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Pankaj, please post a complete question in a new thread.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by pankaj_medatwal View Post
    Suppose there are 2 tables.

    First table is customer table having a primary Cust_id

    2nd table is order table having Cust_id as reference key.

    There is no primary key in 2nd table (order)

    Please suggest me that which type of index is suitable on 2nd table (order) & why ?

    POost question in a new thread...and if you have a foreign key, then I think you need a primary key...but I could be wrong...but NOT having a PK in table2 would be just wrong in the first place

    ALSO, in the last 4 yrears...I found that SQL Server performs BETTER with a clustered Unique Index (or PK) on a heap table

    That kinda blew my mind

    DB2 doesn't work that way
    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.

Posting Permissions

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