Drop Clustered Index when there's no other index's taking a long time.


  • Hi All,

    I'm testing the time it takes to Drop Clustered Index when there's no other index's and see that this operation still takes quite a while. The table I'm working with has 500M records and no other index's.

    I understand that if there were other Index's they would essentially need to be rebuilt as they could be referencing the Clustered index, but why does this operation take so long when I do't have any other index's. In my case it to look 30 minutes to drop the Clustered Index. 


    Monday, August 19, 2013 1:50 PM

All replies

  • Are there any other tables in your database which have a foreign key that references the table you are deleting the clustered index from?

    Monday, August 19, 2013 2:08 PM
  • Hi Warrick,

    This link might have some explanation to your question.

    Are you dropping index or rebuilding it?

    Edit:Considering that you have large record set it seems to normal to me.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, August 19, 2013 2:11 PM
  • No, there are no foreign keys the ref. it. 
    Monday, August 19, 2013 2:33 PM
  • I'm dropping the Clustered Index.

    eg. DROP INDEX [IndexName] ON [dbo].[DbName] WITH ( ONLINE = OFF )

    I don't see anything in the article you referenced that explains why this take s so long. Most of the article explain what happens to Non-Clustered index's when the clustered index is dropped. Since I don't have any non-clustered index's I don't see how that explains it.

    Monday, August 19, 2013 2:46 PM
  • Then the time taken is purely a function of the table size.  As the link in Shanky's post illustrates when you drop the clustered index, SQL Server rebuilds the table into a heap.  The process of rebuilding the table into a heap causes SQL Server to add a RID to the table and that's what takes so much time.

    In other words SQL Server basically makes a copy of the entire table when you drop the clustered index.

    Monday, August 19, 2013 2:49 PM
  • In other words SQL Server basically makes a copy of the entire table when you drop the clustered index.

    NO - that's not true. If a clustered index will be dropped and a table will be drawn back to a HEAP no data from the clustered index will be moved to other allocations! If the clustered index turned into a HEAP only the non clustered indexes will be rebuild because - as you have mentioned - the Clustered Key will be changed to a RID (Row Locator ID).

    You can check it with a simple example:

    -- Creation of a relation without determination of any fill factor
    IF OBJECT_ID('dbo.ServerSettings', 'U') IS NOT NULL
    	DROP TABLE dbo.ServerSettings;
    CREATE TABLE dbo.ServerSettings
    	Id	int	NOT NULL	IDENTITY (1, 1),
    	col1	char(189)	NOT NULL	DEFAULT ('just stuff')
    -- Clustered Index with Key on ID and
    -- non clustered index on co1l
    CREATE CLUSTERED INDEX ServerSettings_Id ON dbo.ServerSettings (Id);
    CREATE INDEX ServerSettings_Col1 ON dbo.ServerSettings(Col1);

    Now I fill the relation with 400 records (=400 pages) by the following code. After the fillling I list the allocated pages / slots for each index!

    -- Filling the table with a few records
    GO 400
    -- what slots do the records allocate
    SELECT sys.fn_PhysLocFormatter(%%physloc%%), * FROM dbo.ServerSettings;
    SELECT sys.fn_PhysLocFormatter(%%physloc%%), * FROM dbo.ServerSettings WITH (INDEX (ServerSettings_Col1));

    The following pic will show in the first list the allocation by the clustered key and the second list is the allocation by the non clustered index

    Fine - now I drop the clustered index - I put it into a named transaction to check the transaction log AFTER the drop operation:

    DROP INDEX ServerSettings_Id ON dbo.ServerSettings;
    SELECT Operation, Context, AllocUnitId, AllocUnitName, [Lock Information]
    FROM sys.fn_dblog(NULL, NULL)
    WHERE [Transaction ID] IN 
    (SELECT [Transaction ID] FROM sys.fn_dblog(NULL, NULL) WHERE [Transaction Name] = 'DropIndex')

    If you check the result for the transaction log you'll never find any "new" allocation for the data of the clustered index but for the non clustered. Furthermore you can see from the context that most of the transactional operations are based on allocation units for the storage of the metadata.

    Next we show the allocated pages for the HEAP and the non clustered index again (after the turn to a HEAP):

    -- How many pages do we have (1 PAGE!)
    SELECT sys.fn_PhysLocFormatter(%%physloc%%), * FROM dbo.ServerSettings;
    SELECT sys.fn_PhysLocFormatter(%%physloc%%), * FROM dbo.ServerSettings WITH (INDEX (ServerSettings_Col1));

    As you can see by comparing both pics you see that the allocated pages and slots are always the same as after the turn from a clustered index to a HEAP but the location of the pages for the nonclustered index have moved - as expected.

    I assume that it will take so long because of two reasons:

    - huge amount of meta data operations OR
    - blocking by other user access

    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Monday, August 19, 2013 4:05 PM
  • nice one

    Thanks, Andrew

    Monday, August 19, 2013 4:12 PM
  • I assume that it will take so long because of two reasons:

    - huge amount of meta data operations OR
    - blocking by other user access

    These are not the only reasons dropping a clustered index takes time.  I just verified it on one of my test servers using a test table which I just created.  The server is isolated and only used by myself.

    Whatever the precise reason might be, SQL Server takes time to drop a clustered index.  My test table contained 32,870,400 rows and is stored in 865,020 pages (about 38 rows per page).

    Dropping the clustered index on the table consistently takes 24 seconds.  If I increase the number of records the time required to drop the clustered index increases.

    Obviously one cannot extrapolate out my results to 500 million and come up with an estimate for the original poster since there are a multitude of factors influencing performance (disk subsystem, CPUs, memory, row size, etc.)  But stating the only two possible reasons that dropping a clustered indexes takes time is limited to metadata operations or blocking is incorrect.

    Monday, August 19, 2013 5:47 PM
  • Wow, great response. Thanks, I learned a new thought process there.

    Is it possible that the

    reason for this is that there’s a hidden column called uniquifier that need to be removed when you drop a no-unique clustered index?

    As per this article:

    Non-unique clustered index: Every record in a clustered index HAS to be unique, otherwise there would be no way to deterministically navigate to a particular record using the index b-tree. In a non-unique clustered index, SQL Server has to add a special column, called the uniquifier, to each record, so that if multiple records have the same cluster key values, the uniquifier column will be the tie-breaker. This uniquifier column is added as part of the cluster key, and so it is also present in all non-clustered index records as part of the logical RID.

    Monday, August 19, 2013 6:02 PM
  • SQL Server does appear to be doing some type of in-place operation when dropping a clustered index as stated.  I increased the number of records in my test table on my test server and as expected the amount of time required to drop the clustered increased from 24 seconds to 48 seconds which makes sense because I doubled my data from 32 million records to 64 million records.

    I also captured the bytes read and written from fn_virtualfilestats before and after dropping the clustered index.  The results are interesting.


    • Bytes read - 64660357120
    • Bytes written - 73509519360


    • Bytes read - 78886748160
    • Bytes written - 73511772160 (73509740544 prior to doing a checkpoint)

    The size of my table now is 13.515 GB so SQL Server appears to be scanning the entire table when it drops the clustered index.  What SQL Server isn't doing is writing the entire table back out.  SQL Server only writes a small amount of data to accomplish the drop so I was wrong on that point.

    Obviously blocking could be a culprit but since you mentioned you are testing this I assumed you aren't doing this on a server/database being used by others.

    Monday, August 19, 2013 7:23 PM
  • Don't forget that the B-Tree structure will also need to be removed, so part of that time is in cleaning up all of the pages, changing root pages in system catalogs, IAM chains, etc.


    Sean Gallardy | Blog | Twitter

    Monday, August 19, 2013 8:13 PM
  • Hi all,

    i made it a WIKI article which describes in depth the internals when a clustered index will be dropped. Any comments are appreciated :)

    Unfortunately I cannot add pics to the article and asked Ed for assistance. I hope he can do that for me :)

    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Tuesday, August 20, 2013 5:49 PM