Convert Non Clustered PKs to Clustered

  • My database that I have inherited suffers from many of the tables have PKs setup to have but they are all non clustered. The tables are not clustered at all. I tried to convert one PK to Clustered on a particular table which brought the system to its knees.

    I rolled back very quickly with very little damage done.

    My question is was this down to the fact that I had altered a unique index. Did this make Query Optimiser work differently and in turn by pass some of the indexes on my tables. The problem I encountered was blocking issues.

  • Changing a nonclustered index to a clustered index involved rebuilding the entire table and every single nonclustered index on that table (the data pages have to move, the nonclustered indexes have to be recreated)

    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
  • Just the person I wanted to answer this question.....

    Yes I forgot about that. PK is written into all NON Clustered index. Another point that makes chosing the right column as a PK.

    So changing from NON CLUST to CLUST will not affect Query Optimiser and my current NON Clustered indexes should be used in the same way.

    Are you saying that although my Clustered index was created successfully my Non Clustered indexes were still rebuilding in the back ground causing blocks.

    Carrying on changing my NON CLUSTERED PK to CLUSTERED PKs should not have any real negative effects on the system if it is done when there is enough time for all the indexes to rebuild before a request is made for the resource.

  • Definately time the index rebuilds for off peak hours to minimize impact to the users.

  • robert.nesta123 (1/9/2013)


    PK is written into all NON Clustered index.

    No it's not. The clustered index key is what is in all nonclustered indexes, not the primary key.

    So changing from NON CLUST to CLUST will not affect Query Optimiser and my current NON Clustered indexes should be used in the same way.

    Err, no I didn't say that.

    Are you saying that although my Clustered index was created successfully my Non Clustered indexes were still rebuilding in the back ground causing blocks.

    No. Everything, including the rebuild of the nonclustered indexes is part of the single operation

    While the index is being changed from nonclustered to clustered, the entire table is locked exclusively and hence any query that wants to use that table will be blocked until the operation completes.

    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
  • My problems occured after the index was built successfully. 2-3 minutes later, during the build all was ok. No blocking etc.

    The system is never completely quiet, but it was done in a quieter period.

    Changing NON CL PK to Clustered can have a negative impact?

    If so what is the best course of action for these heaps. I would insist all PKs are created on a table as clustered. But what is the best course of action to turn this db around. Testing this is not really an option. 1 single table can have between 30 - 100 dependant sprocs. Each sproc can have probably the same amount of parameters 10 - 30 maybe. NOT MY DESIGN. legacy of many years of bad design and administration.

    To test one table would take a long time, there are about 1000 tables to sort out.

    Real headache.

  • robert.nesta123 (1/9/2013)


    Changing NON CL PK to Clustered can have a negative impact?

    Oh yes, especially if the PK column is not a good one for a clustered index.

    If so what is the best course of action for these heaps.

    Create a clustered index on a column or set of columns that are a good choice for a clustered index. That may be the PK. It may not. It requires analysis, investigation and thorough testing

    Testing this is not really an option.

    Then you don't make any changes. If you're not going to test, leave the DB alone, you'll do the least harm that way.

    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
  • GilaMonster (1/9/2013)


    robert.nesta123 (1/9/2013)


    Changing NON CL PK to Clustered can have a negative impact?

    Oh yes, especially if the PK column is not a good one for a clustered index.

    Additionally, the PK index is now larger (potentially much larger) because it contains all columns. So if you have queries that only needed the PK key columns, then it now has to read a lot more data. This particular scenario can be fixed by adding a replacement nonclustered index on those columns ... though I would evaluate indexing as a whole, because there may be 1 or 2 additional columns you could add that would make it suitable for even more queries.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Robert Davis (1/9/2013)


    GilaMonster (1/9/2013)


    robert.nesta123 (1/9/2013)


    Changing NON CL PK to Clustered can have a negative impact?

    Oh yes, especially if the PK column is not a good one for a clustered index.

    Additionally, the PK index is now larger (potentially much larger) because it contains all columns.

    And so, potentially, are all your nonclustered indexes.

    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
  • Don't automatically assume the PK makes the best, or even a good, clustered index, in particular if the PK is an identity column.

    You need to review the existing index usage, missing index info (but don't blindly accept what it says!), and understand the most common query type(s) on that table before deciding on the correct clustered index column(s).

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Btw, it's always frustrated me that SQL Server can't seem to read only the non-leaf pages of a clustered index. If SQL needs only key column(s), why does it read all the leaf pages of the table as well?? That's an incredible waste of I/O.

    UDB/DB2 can do it, why not SQL Server?

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Additionally, the PK index is now larger (potentially much larger) because it contains all columns. So if you have queries that only needed the PK key columns, then it now has to read a lot more data. This particular scenario can be fixed by adding a replacement nonclustered index on those columns ... though I would evaluate indexing as a whole, because there may be 1 or 2 additional columns you could add that would make it suitable for even more queries.[/quote]

    let me just get this straight theni think ive got what i need to do.

    Column A PK

    If my query needs this column then create an additional non clustered column A index . Then add more covering indexes on the non-clustered index to satisfy more queries when possible.

    This will prevent sql using the large clustered pk, it will then use non clustered pk and satisfy multiple queries if possible.

    This solution will get rid of the heap and prevent using the larger pk for queries.

    Data willnow be ordered on disk get rid of fragmentation as thi will now be sorted by reindexing jobs

  • robert.nesta123 (1/9/2013)


    let me just get this straight theni think ive got what i need to do.

    Column A PK

    If my query needs this column then create an additional non clustered column A index . Then add more covering indexes on the non-clustered index to satisfy more queries when possible.

    This will prevent sql using the large clustered pk, it will then use non clustered pk and satisfy multiple queries if possible.

    It depends on the queries. Don't automatically make that change. You have to evaluated whether it is the right decision or not.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • ScottPletcher (1/9/2013)


    Don't automatically assume the PK makes the best, or even a good, clustered index, in particular if the PK is an identity column.

    Considering that the IDENTITY column is frequently the only column that meets the best conditions of "narrow, unique, and ever increasing", I'm not sure why you'd say such a thing. At best, "It Depends".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ScottPletcher (1/9/2013)


    If SQL needs only key column(s), why does it read all the leaf pages of the table as well?? That's an incredible waste of I/O.

    The only place that all the rows are stored is at the leaf level of an index. All that's at the non-leaf levels (any index, clustered or nonclustered) is the key columns for the first row on each page of the level below, hence there's no way to get at the key columns without reading the leaf level.

    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

Viewing 15 posts - 1 through 15 (of 17 total)

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