In-memory: In-memory database

In the last six months after 2016, as the project needed to support fast data updates and high concurrent workloads for multiple users, I tried water SQL Server 2016 for in-memory OLTP, creating a memory database to implement the project's load requirements, and now the project is nearing the end, the system is running stably, writing a blog, Record the experience of using a memory database.

SQL Server 2016 in-memory OLTP, in layman's terms, is a memory database that is implemented using memory-optimized tables (memory-optimized table, or MOT), and the MOT resides in memory and is accessed using the Hekaton memory Database engine. When the MOT is queried, only the data rows are read from memory, and disk IO Consumption is not generated, and when the MOT is updated, the updates to the data are written directly into memory. Memory-Optimized tables maintain a copy of the data on disk that is used only for persisted data and not for data read and write operations.

In the in-memory database, not all of the data needs to be stored in memory, some data can still be stored on disk, the hard disk table (disk-based table, or DBT) is the traditional table storage structure, each page is 8KB, when querying and updating DBT, generating disk IO operation , the data is read from disk to memory, or data updates are written asynchronously to disk.

The memory database stores the data originally stored on disk in memory and uses the high-speed access advantage of memory to realize fast query and update of data, but the memory database is not only the change of storage space, but the Hekaton memory database access engine implements the local compiling module (natively Compiled), cross-transaction (Cross-container Transaction) and query interop:

• Local Compilation Module : If the code module only accesses the MOT, then the module can be defined as a local compilation module, SQL Server directly compiles the TSQL script into machine code; SQL Server 2016 supports natively compiled patterns: stored procedures (SP), Trigger (Trigger), scalar-valued functions (scalar function), or inline multi-statement functions (inline multi-statement function). Compared to the explanatory (interpreted) TSQL module, machine code directly uses memory addresses for higher performance.
• cross-transaction : In an explanatory TSQL module, a transaction can access both the hard disk table and the memory-optimized table; in fact, SQL Server creates two transactions, one transaction accesses the hard disk table, one transaction accesses the memory-optimized table, and in the DMV, identified by using transaction_id and xtp_transaction_id, respectively.
• Query Interop : An explanatory TSQL script can access memory-optimized tables and hard disk tables, and the local compilation module can only access memory-optimized tables.

The memory data is consolidated into the SQL Server relational engine, and the client application does not even feel any change when using the in-memory database, nor does the Dal interface need to make any modifications. Because of the existence of query interop, any explanatory TSQL script can transparently access the MOT, but performance is not as high as the natively compiled TSQL script. When using a distributed transaction to access the MOT, the appropriate transaction isolation level must be set, the read Committed is recommended, and if a mssqlserver_41333 error occurs, a cross-transaction isolation error is generated (cross_container_ Isolation_failure), because the isolation level of the current transaction is too high.

One, create a memory database

The data for the memory-optimized table must be stored in the file group containing memory_optimized_data, which can have multiple file, each file is actually a folder, and a DB can only create one containing Memory_ Optimized_data's file Group.

Step1, create a database, the number of data file created is best and the number of CPU cores consistent, stored on different physical disks;

View Code

Step2, create a filegroup that contains memory-optimized data for the database, add "File" to the filegroup, which is actually a directory, for storing memory-optimized data files, mainly checkpoint files, Memory-optimized tables for restoring persistence.

--Add File Group from memory-optimized dataalter database [test_memborydb]add filegroup fg_memoryoptimizeddatacontains M Emory_optimized_data;alter Database [Test_memborydb]add file (name=test_memborydbdirectory,filename= ' D:\Program Files\Microsoft SQL Server\test_memborydbdirectory ') to FILEGROUP Fg_memoryoptimizeddata;

Filegroup properties: CONTAINS memory_optimized_data clause, which specifies that the file group is used to store memory-optimized table data, and that each database can specify only one file group that stores memory-optimized data, where the file Group creates multiple directory, distributed on different physical disk, speeding up memory-optimized table data restore speed.

Second, create a memory-optimized table

Memory-optimized tables are used to store user data, persist storage, store data in memory, maintain a copy of the data on disk, specify persisted storage memory-optimized tables with the option durability= Schema_and_data, or store them in memory only. specified by the option durability= schema_only. On a memory-optimized table, you can create nonclustered index or nonclustered hash index, creating at least one index per memory-optimized table.

--create Memory optimized tablecreate table [dbo]. [Products] (    [ProductID] [bigint] NOT NULL,    [Name] [varchar] (+) not NULL,    [Price] Decimal (10,2) is not null,    [Unit] varchar (+) not NULL,    [Description] [varchar] (max) null,    constraint [Pk__products_productid] primary key Nonclustered hash ([ProductID]) with (bucket_count=2000000)    , index idx_products_price  nonclustered ([price] DESC)    , index idx_products_unit nonclustered hash (Unit) with (bucket_count=40000)) with (Memory_optimized=on, durability= schema_and_data) Go

1, memory optimization: memory_optimized

[memory_optimized = {on | OFF}]

The default value is off, which specifies that the table created is a hard disk table; The SET option memory_optimized is on, specifying that the table created is a memory-optimized table;

2, Durability: Durability

Durability = {Schema_only | Schema_and_data}

The default value is Schema_and_data, which specifies that the memory-optimized table created is persisted, which means that data updates are persisted to disk, and after a SQL Server restart, the data of the memory-optimized table can be restored to the copy stored on disk. Option schema_only specifies that the memory-optimized table created is non-persisted, which means that the table SCHEMA is persisted to disk, but any data updates are not persisted to disk, and the memory-optimized table data is lost after SQL Server restarts.

3, hash index and range index

Memory-Optimized tables support hash index, property bucket_count specifies the number of buckets created for the hash index, the average hash bucket is 1-2 times the number of data rows, if the number of buckets cannot be estimated, Please create a range index (nonclustered index), the index structure is bw-tree.

A hash index consists of an array and multiple data row chains, each of which is called a hash bucket, and the hash index key is mapped to the hash bucket by a built-in hash function, for example, if the hash index key is (col1,col2 ), the data row is mapped to the specified hash bucket based on the hash Value returned by Hashfunction (Col1,col2), and if multiple keys are mapped to the same hash bucket, the keys form a chain. For example: the data table structure is (name,city), the Name field is created, the hash Index,hash value of the same data row is linked into a one-way chain.

Third, create natively Compiled SP

The locally compiled SP is compiled into machine code when it is created, and the entire SP is executed atomically, which means that all operations in the entire SP are an atomic operation, either successful or failed, in the SP.

CREATE PROCEDURE dbo.usp_getproduct    @ProductID bigint not Nullwith native_compilation, schemabinding, execute AS Ownerasbegin Atomic with (transaction isolation level = snapshot, language = N ' us_english ')  select  [productid]< C3/>,[name]      , [Price]      , [Unit]      

1, in the local compilation SP, can specify the Nullability property for the parameter, variable, and the default value is NULL

Not NULL property: Cannot specify a null value for a parameter or variable.

• In the natively compiled SP, specify a NOT NULL property for the parameter, and you cannot specify a null value for the parameter;
• In the natively compiled SP, define the NOT NULL property for the variable, and the variable must be initialized at declare;

2, the locally compiled SP must contain two options: SCHEMABINDING and ATOMIC Block

• SCHEMABINDING: Memory-Optimized tables for binding references
• ATOMIC block: All statements in an atomic block run in a single transaction, all statements succeed when the transaction succeeds, and all statements are rolled back when the transaction fails. The Atomic bloc guarantees that the SP executes atomically, and if the SP is called in the context of other transactions, the SP begins a new transaction.
  • Atomic blocks guarantee Atomic execution of the stored procedure. If The procedure is invoked outside the context of an active transaction, it'll start a new transaction, which commits a t the end of the atomic block.

You must set two options to use the atomic block:

• TRANSACTION Isolation Level: Specifies the isolation levels of atomic block open transactions, typically specifying the snapshot isolation level;
• LANGUAGE: Specifies the language of the SP context;

3, the difference between an interpreted SP and a locally compiled SP

The real difference between the explanatory SP's ability to access the hard disk table (disk-based table) and the memory-optimized table (memory-optimized table) is that the explanatory (interpreted) SP was compiled the first time, and the local compilation (natively Compiled) SP is compiled at the time of creation and is compiled directly into machine code, which binds to the memory address.

4, Delayed persistence

In the local compilation SP, the option to set the Atoic block is: delayed_durability = ON, which enables the SP to update the memory-optimized table to asynchronously write the transaction log mode, delaying persisting to disk, which means that If the memory-optimized table maintains a copy of the disk-based, the data is modified in memory and is not immediately updated to the disk-based copy, which has the potential to lose data, but can reduce disk IO and improve the performance of data updates.

Four, using memory-optimized table variables and temporary tables

Traditional table variables and temporary tables, all using tempdb to store temporary data, and tempdb is not a memory database, using disk to store temporary tables and table variable data, resulting in disk IO and contention, SQL server provides memory-optimized table variables, storing temporary data in memory, For more information, please refer to my blog: "in-memory: Creating temporary tables and table variables in memory."

Five, using JSON in the in-memory database

Since the use of JSON, my first feeling is: The database could not have JSON, whether it is the database to pass the value of the front end, or the front end of the data to the database, the use of JSON is much more convenient, compared to the use of Xml,json more simple, more information, please refer to my blog: Querying and updating JSON data using TSQL

Six, transaction processing of memory database

A cross-transaction is a transaction in which an explanatory TSQL statement accesses a memory-optimized table (memory-optimized table, or MOT) and a hard disk table (disk-based table, or DBT). In a cross-transaction, the operation to access the MOT and the operation to access the DBT have their own independent transaction sequence number, as in the case of a large cross transaction, there are two separate sub-transactions, respectively, to access the MOT and DBT; in Sys.dm_db_xtp_transactions ( Transact-SQL), transactions that access DBT use the transaction_id identity, and the transaction sequence that accesses the MOT uses the xtp_transaction_id identity. For more information, please refer to my blog: "In-memory: Transaction processing for memory-optimized tables"

Reference Documentation:

SQLSERVER2014 Memory Database Features Introduction

In-memory OLTP (in-memory optimization)

Introduction to Memory-optimized Tables

Natively Compiled Stored Procedures

Memory-optimized Tables

Try SQLSERVER2014 memory-Optimized tables

SQL Server 2014 memory-optimized tables

SQL Server 2014 Memory-Optimized tables (1) Implement memory-optimized tables

In-memory: In-memory database