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)

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

  • 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

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

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

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

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