Migration of 2008 db to 2016 db causing varchar(max) issues

Question

Post reply

Migration of 2008 db to 2016 db causing varchar(max) issues

komal145

SSCrazy Eights

Points: 9915
More actions
August 4, 2022 at 5:14 pm

#4072580

hi,
I am trying to migrate databases and we have huge tables like 30million records , and migration is done using azure devops. So, during DMA (Data migration assistance) changes being applied to tables , as we changed a column i.e column A dataytype of varchar(max) to column nvarchar(max) taking for hours on that table.
As Azure devops try to create a new table with new column datatype and try to move data to that new table in background.
so, what is the fastest way to apply changes on such huge table?
Thanks and appreciate for any suggestion given.
August 12, 2022 at 6:03 am

This was removed by the editor as SPAM
August 27, 2022 at 9:28 am

This was removed by the editor as SPAM

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

I've not looked at the code behind the scenes but I suspect it copies one row at a time using 1 Insert/Values statement per row and that would take almost as long if you weren't making the change from VARCHAR(MAX) to NVARCHAR(MAX).

Also remember that not only are you doubling the size, but any rows that happen to fit "in row" in the cluster index are also doubling in size, creating more "Trapped Short Rows" in the process and that wastes a huge amount of space and makes "normal" scans take sometimes 10s (or worse) of times longer.

If most of the rows have such LOBs, consider forcing them out of row and assigning a single space as the default to prevent "updates" from NULL to something causing massive page splits because of the change from null to a 16 byte pointer.

And, again, I'd wouldn't trust the migration tool to be your friend performance wise. Consider writing your own transfer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

another question is why are you converting to NVARCHAR - is your software now going to be used by users that require data that does not fit on a VARCHAR? e.g. Unicode data.

If not then leaving the columns as VARCHAR is likely the best option for you.

EdVassie SSC Guru Points: 60472 More actions · Answer 4

Fredrico's question is important. Why are you making this change. If it is needed why are you doing it as part of the DB migration.

You are doing a data format change that is not mandatory for the SQL migration you are doing. You need a business case for making this change, and you should action the change outside of the migration process. If it is needed the data format change can be completed either before or after the SQL migration.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Lynn8968 Newbie Points: 8 More actions · Answer 5

Thank goodness i have found someone else experiencing the same problem.