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

  • 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.

     

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

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

  • 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)
    Intro to Tally Tables and Functions

  • 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.

Viewing 4 posts - 1 through 4 (of 4 total)

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