March 7, 2014 at 2:12 am
Hi
Can we migrate GUID TO BIGINT at non peak hour ?
I am having a table with around .5 billion records.Starting from first the GUID has been used. Using GUID and having .5 billion of data and fetching of record is slow, so we have a option of having BIGINT with identity column.
Present GUID with non clusterindex and plan to have Bigint with Clusterindex.
Some 20 tables with forigenkey relationship to main table.
Each 20 tables will be having some 5 million records each.
Plan to migrate GUID to BIGINT
Remove the fk
Update the main table with bigint with identity
Update the Child table(s) with bigint with identity (fk relationship)
Add the again adding fk.
Can you peoples suggest on below case.
Will this process is ok to go or is there any better option to go.
Or can we continue will the GUID, since the data is huge.
Thanks!
March 7, 2014 at 2:15 am
Have you tested the procedure out on your dev/test server? Can it be done in the time you have?
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
March 7, 2014 at 2:27 am
Yet to test process, before that getting into the process,ensuring that the process which i have followed is ok or need to modify.
Next thing is can we hold GUID itself or can we move to BIGINT.
Will there be improvement in the workflow
March 7, 2014 at 2:28 am
Maybe, maybe not, very hard to tell without knowing a lot about your app.
You need to test this process out in dev, first see if it's even possible in the time you have available, whether it will fill the log, grow the DB file, etc.
Why are you making this change?
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
March 7, 2014 at 2:41 am
As i said since the data is growing large and large having uniqueidentifier and querying the data might takes time.
Inorder to reduce the time in future to insert the data and fetch the data.
March 7, 2014 at 3:01 am
yuvipoy (3/7/2014)
As i said since the data is growing large and large having uniqueidentifier and querying the data might takes time.Inorder to reduce the time in future to insert the data and fetch the data.
This is a huge change to be considering on mights and maybes.
You need to test and see whether or not your plan will have any performance effects at all, otherwise it may be a complete waste of time. Set up a test environment, have a copy of the table with the GUID, a copy of the table with the bigint and run some benchmarks.
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
March 7, 2014 at 3:36 am
Right there with Gail. This is something I'd test the heck out of this before I got anywhere near production. It's far too big a change to do it any other way.
Your general approach seems ok. I'd leave the GUID column in place until all the migrations were complete. I'd also leave the indexes intact on the GUID column through the process. I'd probably drop it later, but that should be tested too. Maybe you need to do this in stages in order to meet the down time window. Stage one, get the identity column in place. Stage two modify primary keys and foreign keys. Stage three, drop the GUID column. Again, testing it out is the answer.
"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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply