question

OlshanskyAsya-1201 avatar image
0 Votes"
OlshanskyAsya-1201 asked OlshanskyAsya-1201 commented

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

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-general
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @OlshanskyAsya-1201,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it is not, please let us know . By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·

No it didn't help, I looked at this pages - it's not related to my problem, it's some general explanation.
Also I can't split table since it's being used in JAVA code, so it will break functionality.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @OlshanskyAsya-1201,

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.