Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Convert Non Clustered PKs to Clustered Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2013 12:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 23, 2013 1:09 PM
Points: 9, 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.
Post #1404932
Posted Wednesday, January 9, 2013 12:04 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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

Post #1404936
Posted Wednesday, January 9, 2013 12:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 23, 2013 1:09 PM
Points: 9, 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.
Post #1404947
Posted Wednesday, January 9, 2013 12:42 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 1:58 PM
Points: 367, Visits: 615
Definately time the index rebuilds for off peak hours to minimize impact to the users.
Post #1404952
Posted Wednesday, January 9, 2013 12:50 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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

Post #1404957
Posted Wednesday, January 9, 2013 1:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 23, 2013 1:09 PM
Points: 9, 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.
Post #1404966
Posted Wednesday, January 9, 2013 1:20 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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

Post #1404973
Posted Wednesday, January 9, 2013 1:29 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:16 AM
Points: 1,618, Visits: 1,554
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 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1404980
Posted Wednesday, January 9, 2013 1:31 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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

Post #1404984
Posted Wednesday, January 9, 2013 2:36 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:26 PM
Points: 2,330, Visits: 3,509
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1405017
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse