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


Clustered Index - UniqueIdentifiers


Clustered Index - UniqueIdentifiers

Author
Message
digdave7
digdave7
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 298
I want to change from UniqueIdentifier to VarChar and then update the existing Guid to a Numeric(technically varchar), but the long term plan is to replace all the GUID's with Incremental Numerics to stop the page locks when updating.

If we change the column type From UniqueIdentifer to VarChar will that force an index refresh..? If so is there a way I can force the index to not update for the change it columns..?

We have millions of recrods, and are a 24/7 shop so waiting for that to complete could be a huge problem.

Please let me know, ive tried to search and found nothing.
thanks,
ALZDBA
ALZDBA
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20039 Visits: 8978
The index will have to be dropped and recreated because of the change of datatype.

Rule number 1: tell the system what you know.
So don't use a (var)char datatype if you know it is intended for numbers only !

Prefer Integer or bigint over number(x,y)because they will cause nice small index keys.

Prepare your action, so you'll cause the least io overhead.
If you need to drop indexes, drop your clustering index as last, because that one will cause your whole data to be shifted into a heap, and all remaining indexes to be rebuild so they use the new RIDs to point to the data pages.
Always create your clustered index as first index, because all non-clustering indexes will refer to the data using the clustering index (uniquified if needed) key(s).

First run your full conversion on a test system, so you can do a bit of benchmarking to plan your timings and strategy.
e.g. convert on a shadow database and the renaming that one and load the last delta.

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70792 Visits: 32902
Replacing the GUID with an integer, especially on the cluster, is a good idea, but I would not go through that interim step of moving it to varchar first. You're taking a 16 bit key and turning it into a 36 bit key. That's not going to help your performance, in any way. A better approach would be to add a new column, as suggested above, int or bigint, and then migrate the keys. This will result in less down time than trying to change the data type and it won't hurt performance unlike the varchar route.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
digdave7
digdave7
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 298
Thanks everyone. The only reason I suggested changing to Varchar was my hope the value would evaluate the same and not force a re-order of the index, but it sounds like it wont.

I am adding a BigInt, Identity column to each table in question, and as time goes on we will back populate the ID with the GUID. It sounds like there is no middle ground here, and we will have to bite the bullet for some tables by dropping the indexes and recreating them.

To load these tables we use MSMQ, and can turn off the queue's while some of this is going on. Since this is on 2008, any advice on doing these changes "Offline"...? We will be dropping the clustered index, and moving it to the BigInt column to avoid the page locks.
digdave7
digdave7
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 298
Hi Grant or(Anyone else) Smile,

Thanks for the reply; you're comments helped. I did want to ask (if you dont mind) for more feedback on the "migrate the keys" suggestion..? The end goal here is to shift the clustered index from the GUID to the BigInt column, if we can avoid dropping the indexes and migrate them, as you say that would be ideal. Is there a change index option rather then a drop and recreate..?

I didnt design the schema and picked up on this problem early on, but the research and valdiation took some time. Because of the qty of records I am looking at all options to balance this change with downtime.

For example:
TableID "Objects"
Index: PK_Objects on Object_ID (clustered, unique, primary key located on PRIMARY)
---I want to change it to TableID BigInt as the cluster, obviously there are other tables in question.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70792 Visits: 32902
digdave7 (9/27/2010)
Hi Grant or(Anyone else) Smile,

Thanks for the reply; you're comments helped. I did want to ask (if you dont mind) for more feedback on the "migrate the keys" suggestion..? The end goal here is to shift the clustered index from the GUID to the BigInt column, if we can avoid dropping the indexes and migrate them, as you say that would be ideal. Is there a change index option rather then a drop and recreate..?

I didnt design the schema and picked up on this problem early on, but the research and valdiation took some time. Because of the qty of records I am looking at all options to balance this change with downtime.

For example:
TableID "Objects"
Index: PK_Objects on Object_ID (clustered, unique, primary key located on PRIMARY)
---I want to change it to TableID BigInt as the cluster, obviously there are other tables in question.




No, to change the keys you'll have to drop & recreate them, but that should be relatively painless (just not pain free). But you'd have to do that no matter what.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
ALZDBA
ALZDBA
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20039 Visits: 8978
digdave7 (9/27/2010)
...The end goal here is to shift the clustered index from the GUID to the BigInt column, if we can avoid dropping the indexes and migrate them, as you say that would be ideal. ...


For the volume you are talking about, I would actually request down time !
As state earlier, a drop clustering index, will cause all data pages to be shifted to a heap !
This will also cause all non-clustering indexes to be rebuilt (which you'll want to avoid because of the next step)
Next the creation of the new clustering index will cause all data to be moved to the data pages of your new clustering index and all existing indexes to be rebuilt once again.
So I would drop all NCI before I modify the clustering index and re-create them afterward.

Also keep in mind you may have to swap recovery models for your database to cause as less logging overhead as possible.

Play it safe and start with a full backup !

edited: I mixed up two threads ... it was the other one talking aboud a huge number of rows ...
So re-evaluate the relevance of my reply, with regards to data volume.
Sorry for the mixup Blush


Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
AmolNaik
AmolNaik
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 1234
Create copy of the table that is identically same with the correct structure. Copy data from the existing table to this new table. Switch the table names.

After that you might have to do comparison between old and new tables and apply the updates you might have lost during the switch.

Give a try to this approach!

Good luck!

Thanks,

Amol Naik
Evil Kraig F
Evil Kraig F
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13431 Visits: 7660
AmolNaik (9/29/2010)
Create copy of the table that is identically same with the correct structure. Copy data from the existing table to this new table. Switch the table names.

After that you might have to do comparison between old and new tables and apply the updates you might have lost during the switch.

Give a try to this approach!

Good luck!

Thanks,


I'd agree with this. If you're stuck at 5 nines support and can't get a LARGE window for downtime (think day(s)) depending on the box, the size of the data pages, and the SAN speed, amongst other things. This is a solution, but be prepared to do a lot of prep work to make it work.

You want the system queueing data changes to an audit table from when the copy is first made, so you need to create a full set of audit triggers. And an audit table. Once that's in place, you work on the copy, do what you need to. You then find yourself a nice 10-20 minute window, and apply all audited changes. Then you run the sp_ for changing object names for the two tables, and let 'er rip. Keep your old copy of the table for a day or two in case anything goes wrong, or somehow something missed an audit.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
ALZDBA
ALZDBA
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20039 Visits: 8978
If you're going for the choice to copy the table to a staging table scenario, keep in mind to re-link all objects that are related to or have been related with your original table (PK/FK !), triggers, extra indexes, ...

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
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