Introduction to memory-optimized tables
Memory-optimized tables are tables created using CREATE TABLE (Transact-SQL).
By default, memory-optimized tables are fully persistent. Like transactions on (traditional) disk-based tables, transactions on memory-optimized tables are fully atomic, consistent, isolated, and persistent (ACID). Memory-optimized tables and natively compiled stored procedures support only a subset of Transact-SQL features.
As of SQL Server 2016 and in Azure SQL Database, there are no restrictions on in-memory OLTP-specific collation or code pages.
The primary memory of a memory-optimized table is the primary memory. Read rows from the table from memory and write those rows to memory. Another copy of the table data is maintained on disk, but only for persistence purposes. For more information about persistent tables, see Create and manage storage of objects for memory optimization. Data in memory-optimized tables is read from disk only during database recovery (for example, after a server reboot).
For greater performance gains, in-memory OLTP supports persistent tables with transactional persistent latency. Delayed persistent transactions are saved to disk shortly after the transaction is committed and control is returned to the client. In exchange for improved performance, committed transactions that are not saved to disk are lost in the event of a server crash or failover.
In addition to the default persistent memory-optimized tables, SQL Server supports unrecorded non-persistent memory-optimized tables, and their data is not persisted on disk. This means that transactions on these tables do not require any disk IO, but if a server failure or failover occurs, the data is lost.
In-memory OLTP integrates with SQL Server to provide a seamless experience across all aspects such as development, deployment, manageability, and supportability. A database can contain both in-memory objects and disk-based objects.
The rows in the memory-optimized table are versioned. This means that each row in the table can have multiple versions. All row versions are maintained in the same table data structure. Row versioning is used to enable concurrent reads and writes to the same row. For more information about concurrent reads and writes to the same row, see Things in the Memory Optimization Table.
Approximate steps and limitations of use
Steps:
(1) Create a database and MEMORY_OPTIMIZED_DATA file group (alter database add filegroup, each database can only have one MEMORY_OPTIMIZED_DATA file group!!) ）
(2) Add a folder to the file group to store the data files (please do not create this folder in advance, use alter database add file() to filegroup)
(3) Create persistent/non-persistent memory-optimized tables (SQL Server 2014 is persisted by default), and you must also define a primary key based on a non-aggregated hash index. Hashed indexes are the only supported index type in memory-optimized tables.
(4) Use locally compiled stored procedures: Memory-optimized tables support locally compiled stored procedures, as long as those stored procedures only reference memory-optimized tables.
Limit:
(1) The memory-optimized table must be placed under the database with the CONTAINS MEMORY_OPTIMIZED_DATA filegroup.
(2) The memory-optimized table must define a primary key based on a non-aggregated hash index, and the table must be definedForeign key or constraint checking is not supported
(3) The memory optimization table must have enough memory, otherwise operations such as inserting updates will fail.
(4) SQL Server 2014 has many limitations on these tables. For example, they don't support foreign keys or constraint checking (which feels similar to MySQL's memory storage engine), theyIDENTITY fields or DML triggers are also not supported。
Use the tutorial
The database versions are as follows:
Microsoft SQL Server 2022 (RTM-CU15-GDR) (KB5046059) - 16.0.4150.1 (X64)
Sep 25 2024 17:34:41
Copyright (C) 2022 Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Linux (Ubuntu 22.04.4 LTS) <X64>
First, create a database and file group with the following command:
If you do not create a memory-optimized group, an error will be reported when you create a memory-optimized table:Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container.
Create two memory-optimized tables to test, one persistent and one non-persistent, with the following command:
Memory optimization table has two options (DURABILITY = SCHEMA_AND_DATA), (DURABILITY = SCHEMA_ONLY)
Durability table: DURABILITY = SCHEMA_AND_DATA
Non-persistent table: DURABILITY = SCHEMA_ONLY
Tests are inserted separately into the memory optimization table100,000 articles(actually 100002 items) data, the command is as follows:
Persistence takes 28 seconds to turn on and 1 second without persistence。
Reference:
The hyperlink login is visible.
The hyperlink login is visible.
The hyperlink login is visible.
The hyperlink login is visible.
The hyperlink login is visible. |
Posted on 2025-5-10 21:28:18
|
|
|
|
Landlord|
Posted on 2025-5-10 21:36:40
|
