Memory Optimization Advisor producing errors I don't know how to fix

Olshansky, Asya 1 Reputation point
2021-03-24T09:33:18.863+00:00

I am trying to migrate part of my tables that are disk based to in memory ones. While doing this I have an error produced which I do not know how to fix nor can I find information on this error anywhere.
I am using SQL Server 2016.

I get the following errors that are produced:
1.) "Post migration row size exceeds the row size limit of memory-optimized tables...."
2.) "The following unsupported foreign key relationships are defined on this table..."

How do I fix this? Is there anyway to fix it when migrating?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,607 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.8K Reputation points MVP
    2021-03-24T23:04:59.373+00:00

    The first error seems obvious. It appears that the table you have is very wide. Maybe you need to split it up.

    For the second, but my guess is that you have CASCADE options which are not supported by In-memory OLTP.

    0 comments
  2. CathyJi-MSFT 21,071 Reputation points Microsoft Vendor
    2021-03-25T06:34:06.25+00:00

    Hi @Olshansky, Asya ,

    > "Post migration row size exceeds the row size limit of memory-optimized tables...."

    Prior to SQL Server 2016 (13.x) the in-row data size of a memory-optimized table couldn't be longer than 8,060 bytes. However, starting SQL Server 2016 (13.x) and in Azure SQL Database it is now possible to create a memory-optimized table with multiple large columns (e.g., multiple varbinary(8000) columns) and LOB columns (i.e., varbinary(max), varchar(max), and nvarchar(max)) and perform operations on them using natively compiled T-SQL modules and table types.

    Refer to MS document Table and Row Size in Memory-Optimized Tables to get more.

    >The following unsupported foreign key relationships are defined on this table..."

    For memory-optimized tables, FOREIGN KEY constraints are only supported for foreign keys referencing primary keys of other memory-optimized tables. Remove the constraint from the table definition if the foreign key references a unique constraint.

    Refer to MS document Memory-Optimized Tables.

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments