SQL Server - Memory quota error during migration to in-memory table
We are currently migrating to in-memory tables on SQL Server 2019 Standard Edition. The disk based table is 55GB data + 54Gb of indexes (71M records). RAM is 900 GB. But during data migration (INSERT statement) we get an error message:
Msg 41823, Level 16, State 109, Line 150 Could not perform the operation because the database has reached its quota for in-memory tables. This error may be transient. Please retry the operation.
The in-memory file is “unlimited”, so it looks strange since SQL Server 2019 should not have any size restrictions for in-memory tables.
Why do you think in-memory data size in a single mem-opt table is unlimited on standard edition?
From Memory Limits in SQL Server 2016 SP1 (all of which still applies according to 2019 docs):
Each user database on the instance can have an additional 32GB allocated to memory-optimized tables, over and above the buffer pool limit.
So, you can do what you want, I suppose, but you'll have to spread it across multiple databases. You won't be able to store more than 32GB in a single mem-opt table or even in multiple mem-opt tables in a single database.
Cropped and probably inappropriately-scaled screenshot from the 2019 docs:
- Postgres selecting from table row
- To find the next work day
- SQL Server Left Join With 'Or' Operator
- How to exclusivley select a record from different tables
- Add user to table and use users id while adding data to second table while user registration
- Selecting the earliest row where a specified date value falls between a date range (two columns) when multiple rows may match
- SQL AVG returning an int
- Using two tables. What am I doing wrong here?
- ORDER BY column, with specific notnull value LAST
- How to get the first available number in a range
- In GENERATE_SPREADSHEET SQL on IBM i there are problems if a $ character is present in qualified names
- How to return default value from SQL query
- how to sort a column which contains Semantic Version in Postgres
- Proper Case in Athena SQL
- SQL VBA Query not picking up letter characters, only fully numeric fields
- Joining two tables with a joint result in Postgresql
- Can I Query or Do I Need a Table Function?
- How to link 3 SQL tables and pass 0 value if the value is NULL
- Searching all tables in database containing specific column
- Convert VARCHAR to SMALLDATETIME as part of UPDATE statement
- WordPress - Optimize the Meta Query for 3 meta keys at a time
- How can I find the highest ancestor in a tree?
- Firebird lock and select unlocked fields
- How to pass parameters in a native query JPA
- SQL Server : query columns to JSON object with group by
- SQL Server While loop for last day of month
- Can anyone please explain this solution of hacker rank Binary Tree Nodes?
- generate DDL script from SQL Server database
- How to return name, given an ID Number from same dataset?
- Get the names of all the tables and the views in a single SQL query for SQL Server?