Post reply

Index creation-Blocking and Disk space

  • June 9, 2011 at 11:52 pm

    #252650

    Hi All,

    I am creating non clustered index on a table size @650GB. When i check for the process, sometimes it gives blocking by the same process id which is used for creating the index. I t persist for some time and goes. My question is why the same process is blocking itself. Also looking for suggetion how to manage the index creation in such scenario with optimum time on such large size table.

    Thanks in advance!!

  • June 10, 2011 at 1:42 am

    #1336817

    vinod.saraswat (6/9/2011)

    My question is why the same process is blocking itself.

    This can happen when the server has more than one processor and the process is running parallelly on many processors.

  • June 10, 2011 at 1:47 am

    #1336820

    vinod.saraswat (6/9/2011)

    Also looking for suggetion how to manage the index creation in such scenario with optimum time on such large size table.

    One suggestion is , create nonclustered indexes on separate file group which is on serarate disk.

  • June 10, 2011 at 3:37 am

    #1336851

    Thanks Suresh.:-)

    I have one more thing to discuss, yesterday i created clusetered index on the same table and it took very long time to create the index. Is there any effective way to create clustered index with optimum time say 1 hr or max 2hours on such large table.

  • June 10, 2011 at 3:57 am

    #1336860

    Creating a clustered index recreates the entire table, and rebuild all nonclustered indexes. If creating a cluster ensure that all nonclustered indexes are dropped first.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 10, 2011 at 4:09 am

    #1336867

    @Gail : I agree your point but i am looking for way to handle the scenario in future, if i face the same situation mentioned in the post i.e. creating clustered index on very large size table.

  • June 10, 2011 at 4:24 am

    #1336873

    Drop all nonclustered indexes first. Other than that there is little you do, creating a clustered index requires recreating the entire table. It will be slow on large tables.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 10, 2011 at 5:42 am

    #1336901

    OKay,

    Thanks for the reply

  • June 10, 2011 at 10:32 am

    #1337118

    Also try to prevent DML from happening on the table while building the clustered index.

    Nothing slows down index creation more than inserts, updates and deletes.

    -------------------------------------------------
    Will C,
    MCITP 2008 Database Admin, Developer

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply