Issues to be aware of when using SQL Server 2014 memory databases

This article shared the experience of using the Microsoft Memory database from a product design and architecture perspective, and I hope you will be able to better design your architecture by understanding these new objects and concepts after reading this article.

A memory database that refers to the direct manipulation of database data in memory. Compared to storage on disk, memory data read and write speed is much higher, so it can improve the performance of the application. Microsoft's SQL Server 2014 was officially released on April 1, 2014, and the SQL 20,141 main features are memory databases.

Next, I'll focus on what you need to be aware of when using SQL Server 2014 memory databases.

About the Memory database

The SQL Server 2014 Memory database introduces a new structure for traditional tables and stored procedures: Memory optimized table (memory tuning tables) and native stored procedure (local compilation stored procedures).

By default, the memory optimized table is completely persistent (that is, durable memory optimized table), such as transactions on a traditional disk-based table, and fully persistent transactions are supported by atomic, consistent, isolated, and persistent (ACID) Of The difference is that the primary storage of the entire table of the memory tuning table is in memory, that is, to read rows from memory and update these row data into memory. It's not like a traditional disk-based table mounts a database according to a database Database page. The data in the Memory tuning table also maintains another copy on the disk, but only for the purpose of persistence. Data in the memory-optimized table is again loaded from disk during database recovery. Create a persistent memory tuning table method as follows:

CREATE TABLE durabletbl
(accountno     INT not	    NULL  PRIMARY KEY nonclustered HASH with (Bucket_count = 28713 )
, CustName      VARCHAR not  null
, Gender        CHAR not NULL/   * M or F/*,
CustGroup     VARCHAR (4)   NOT NULL   /* which customer group he/she belongs to/*
, Addr          VARCHAR (m)  null/No address       suppl IED is acceptable * * *
, phone         VARCHAR (a)  NULL       */Phone number/* with
(memory_ Optimized=on, Durability=schema_and_data)

In addition to the default Persistent memory tuning table, non-durable memory optimized table (non-persisted memory tuning table) is supported, logs of these tables are not logged and their data is not saved on disk. This means that transactions on these tables do not require any disk IO, but the data cannot be recovered if the server crashes or fails over. Create a Non-persistent memory tuning table method as follows:

CREATE TABLE nondurabletbl
(accountno     INT not	    NULL  PRIMARY KEY nonclustered HASH with (Bucket_count = 28713)
, CustName      VARCHAR () not  null
, Gender        CHAR not         null/   */F
//, CustGroup     VARCHAR (4) Not   NULL/   * which customer group he/she belongs
to/*, ADDR          VARCHAR (50) C18/>null       /* No address supplied is acceptable
/*, phone         VARCHAR (a)  NULL/       * Phone number */< c24/>)
with (Memory_optimized=on, durability=schema_only)

Native compiled stored procedure (local compilation stored procedures) is for traditional stored procedures, and is generated after native compilation of stored procedures, since native compilation refers to the process of converting a programming construct to native code, which consists of a processor instruction. No further compilation or interpretation is required. Compared with traditional TSQL, native compilation can improve the speed of accessing data and the efficiency of executing queries. Therefore, the stored procedures compiled by the native can improve the efficiency of query and business logic processing in the stored procedure. Create methods local compilation stored procedure methods are as follows:

CREATE PROCEDURE dbo.usp_insertnondurabletbl
@AccountNo int,
@CustName nvarchar (),
@Gender	char ( 1),
@CustGroup	varchar (4),
@Addr	varchar (m),
@Phone	varchar (
a) with Native_ Compilation, schemabinding, EXECUTE as "
ATOMIC with"
(TRANSACTION isolation level = SNAPSHOT, Langu Age = N ' 中文版 ')      
BEGIN
	INSERT into [dbo].[ DURABLETBL]
           ([Accountno], [
           CustName], [
           Gender]
           , [CustGroup],
           [Addr]
           , [Phone])
     VALUES (@AccountNo
           , @CustName
           , @Gender 
           , @CustGroup 
           , @Addr
           , @Phone)
end
End
Go

The memory database can contain both the memory tuning table and the local compilation stored procedures, as well as disk-based tables and traditional stored procedures, and the schemas for data storage and access between objects are as follows: