SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convert Non Clustered PKs to Clustered


Convert Non Clustered PKs to Clustered

Author
Message
robert.nesta123
robert.nesta123
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 19
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221047 Visits: 46279
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


robert.nesta123
robert.nesta123
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 19
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.
jerry-621596
jerry-621596
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1301 Visits: 649
Definately time the index rebuilds for off peak hours to minimize impact to the users.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221047 Visits: 46279
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


robert.nesta123
robert.nesta123
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 19
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221047 Visits: 46279
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


Robert Davis
Robert Davis
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6524 Visits: 1632
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
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
GilaMonster
GilaMonster
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221047 Visits: 46279
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


ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19365 Visits: 7410
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search