SQL Server Performance

Dropping a clustered index

Discussion in 'Performance Tuning for DBAs' started by asentell, Sep 17, 2007.

  1. asentell New Member

    If I have a large table (130 GB, 330+ million records) on which there is only one index and it is clustered, should dropping that clustered index be a "fast" operation or would SQL have to do a lot of work behind the scenes that could take some time (hours)?
    Thanks,
    Aaron
  2. FrankKalis Moderator

    Are you on SQL Server 2005? If so, check this out: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2344c76a-c01e-40ab-9350-f2434d484402.htm
    Sounds like this will be a lengthy operation.
  3. asentell New Member

    Thanks. I'm on SQL Server 2000 Enterprise Edition, but I'm betting this is also true for it. I'd like to still hear from others if they have experience with this or anything to add to it.
    Here's an excerpt from the SQL 2005 BOL entry referred to by FrankKalis:
    "In the logical phase, the existing allocation units used by the table or index are marked for deallocation and locked until the transaction commits. With a clustered index that is dropped, the data rows are copied and then moved to new allocation units created to the store either a rebuilt clustered index, or a heap. (In the case of an index rebuild, the data rows are sorted also.) When there is a rollback, only this logical phase needs to be rolled back. "
  4. ndinakar Member

    [quote user="asentell"]If I have a large table (130 GB, 330+ million records) on which there is only one index and it is clustered, should dropping that clustered index be a "fast" operation or would SQL have to do a lot of work behind the scenes that could take some time (hours)?[/quote]
    I would think so..What is it that you are trying to do eventually?
  5. ndinakar Member

    Also remember it can have side-effects such as query plans getting screwed up for not finding the index...
  6. asentell New Member

    We have two databases: Online and Archive. Online is 6 weeks worth of data, and Archive is a full history of data. Once each week we migrate data from Online to Archive using a DTS package. One unfortunate characteristic of these databases is that GUID columns are everywhere, and there are clustered indexes created on many of them. My idea was to drop the clustered indexes in the Archive database before running the migration job, thereby speeding up inserts, and rebuild the clustered indexes afterwards. This would also allow me to compress the indexes (fillfactor = 100) and eliminate the need for a separate reindex job for these tables at some other time.
    With an upcoming upgrade to SQL 2005 I hope to utilize partitioning and SSIS to improve this process even more.
  7. FrankKalis Moderator

    What is the Archive database else used for? Would anyone care about a long-running reindexing job anyway?
  8. Luis Martin Moderator

    Back to the original question.
    Will take a long time to drop a cluster index in that table.
    Of course, its also depend on your hardware.

Share This Page