September 26, 2010 at 11:10 am
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,
September 26, 2010 at 12:19 pm
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
Learn to play, play to learn !
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[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 27, 2010 at 5:43 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 27, 2010 at 1:11 pm
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.
September 27, 2010 at 1:29 pm
Hi Grant or(Anyone else) :),
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.
September 27, 2010 at 1:34 pm
digdave7 (9/27/2010)
Hi Grant or(Anyone else) :),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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 28, 2010 at 1:03 am
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
Learn to play, play to learn !
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[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 29, 2010 at 4:55 pm
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
September 29, 2010 at 5:26 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 30, 2010 at 11:40 am
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
Learn to play, play to learn !
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[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply