Online Conversion of a Heap table into a Clusterd table.

Question

Online Conversion of a Heap table into a Clusterd table.

ben.brugman

SSChampion

Points: 13369
More actions
June 15, 2012 at 7:30 am

#262409

Is there a way or what are the possibilities, to convert a Heap table into a Clustered table in an On-line situation.
Thanks for your time and attention.
(Maybe this question should have been asked in High availability, but maybe it's 'just' an Transact SQL question. Sorry if I placed this question in the wrong thread).
Ben Brugman

Viewing 9 posts - 1 through 8 (of 8 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

http://msdn.microsoft.com/en-us/library/ms188783%28v=sql.105%29.aspx

You might want to do this during off hours especially if the table is large because it might take a bit of time to create the index.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

If you're on Enterprise Edition and there are no LOB columns in the table, then you should be able to create the clustered index online with the ONLINE option of Create index

(shouldn't be in HA forum, that's for HA solutions like mirroring, clustering, etc)

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

ben.brugman SSChampion Points: 13369 More actions · Answer 3

Sean Lange (6/15/2012)
You might want to do this during off hours especially if the table is large because it might take a bit of time to create the index.

Thanks for your reply,

There are no off hours,

there are times when there is not a lot of activity on the system, but everything including the table which has to be converted from clustered to a heap has to be on line.

So the question is still the same.

Thanks,

Ben Brugman

ben.brugman SSChampion Points: 13369 More actions · Answer 4

GilaMonster (6/15/2012)
If you're on Enterprise Edition and there are no LOB columns in the table, then you should be able to create the clustered index online with the ONLINE option of Create index
(shouldn't be in HA forum, that's for HA solutions like mirroring, clustering, etc)

Thanks for your anwser,

Will try that on monday on a Developer edition.

This weekend I only have access to a standard edition.

(Does your solution work for heap tables with a identity column where you want to cluster on something else than the identity. Because that is what I could not get working. ).

As said I'll continue on monday.

Thanks, have a nice weekend,

(for me that begins in a few minutes):-)

Ben Brugman

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Shouldn't depend on anything other than whether there are LOB columns in the table or not.

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

ben.brugman SSChampion Points: 13369 More actions · Answer 6

Thank you all for the replies.

Now I can convert most tables ON-LINE. Great.

Do you have suggestions for the tables containing LOBS.

Or is there is way to do this on-line by creating an extra table transporting the information but keeping the information on-line at the same time ?

Set up. (Not complete).

Table_X (This is the target table to be clustered).

Create a Table_X2 which is the same as table_X but with the clustering in place.

Rename Table_X into Table_X1

Make a view which is called Table_X and makes a union of Table_X1 and Table_X2.

Transport the date from Table_X1 to Table_X2

When ready drop the X1 and the view X and rename X2 into X.

Now the tables are available during conversion, but DML is not possible, or is there a solution for that ?

Or can something be done with triggers, partitioning or .... .?

Ben Brugman

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

or Snapshot / read committed snapshot isolation.

Data modifications may still block

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

ben.brugman SSChampion Points: 13369 More actions · Answer 8

Thanks for your anwsers.

At the moment I am trying to convince my organisation that Clustering would solve a lot of the problems we have at the moment and would improve performance in general and would improve read performance a lot.

Offcourse the management is throwing all kind off objections. Things like write performance will go down, large concurrency installations performance will go down. This can not be done on-line. This will only improve a small part of the operation, we do not experience problems in our large deployments. This will not work on all deployments.

I do not have the capacity to resolve all the problems and all the objections thrown at me. But the knowledge how to solve a large part of the objections will help me in part to convince the management.

So if I can come up with enough anwsers, maybe the management will allocate resources to test the clustering solution.

If choosen at the beginning, I think the clustering would have given a better solution and a better performance and scalability, but changing now comes with some resistance.

(To be onest I have not tested the setup with the large scale deployments).

Thanks for your time and attention,

Ben Brugman