SQL Server 2014 Data Memory optimization table detailed

Unlike the disk-based table, the memory tuning table resides in memory and is implemented using the Hekaton memory Database engine. Reads a row of data from memory at the time of the query, and writes the update of the data directly into memory when it is updated. The Memory tuning table maintains a copy of the disk to be used for persisting the dataset.

memory-optimized tables reside in Memory. Rows in the table are read from and written to memory. The entire table resides in memory. A second copy of the table data is maintained on disk and but only for durability purposes.

One, create a database

The Memory tuning table must be stored in a file group containing memory-optimized-data, one db can contain only one, and the file group may have more than one file (file is actually a folder).

Use master
Go

--create Database
Create DATABASE Testmemorydb

--ADD Filegroup
ALTER DATABASE Testmemorydb
Add Filegroup Fg_testmemorydb
Contains memory_optimized_data;

--add File directory go filegroup
ALTER DATABASE Testmemorydb
Add File
(
Name= ' Testmemorydbdirectory ',
Filename= ' D:\MSSQLServerData\MSSQL12. Mssqlserver\mssql\data\testmemorydbdirectory '
)
to filegroup fg_testmemorydb;
The CONTAINS memory_optimized_data clause specifies the filegroup that is used to store the memory tuning table data.

Specifies that's filegroup stores memory_optimized data in the file system. Only one memory_optimized_data filegroup are allowed per database.

Second, create the memory tuning table

In the Memory tuning table of SQL Server 2014, you can create only nonclustered index or nonclustered hash index, and create at least one index per memory tuning table and create up to 8 index. In the Memory tuning table, index must be created in the CREATE TABLE statement, the index cannot be created using the CREATE INDEX command, and the index cannot be deleted using the DROP INDEX command.

You are must specify column and table indexes as part of the CREATE table statement. CREATE Index and DROP INDEX are not supported for memory-optimized tables.

--create Memory Optimized table
CREATE TABLE [dbo]. [Products]
(
[ProductID] [bigint] Not NULL,
[Name] [varchar] () not NULL,
[Price] Decimal (10,2) is not NULL,
[Unit] varchar COLLATE latin1_general_100_bin2 is not NULL,
[Description] [varchar] (1000) 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,durability Persistence

Durability = {Schema_only | Schema_and_data}

The value of Schema_and_data indicates that's the table is durable, meaning this changes are persisted on disk and survive R  Estart or failover. Schema_and_data is the default value.

The value of Schema_only indicates that's the table is non-durable. The table schema is persisted but any data updates are not persisted upon a restart or failover of the database. Durability=schema_only is only allowed with Memory_optimized=on.

2,memory_optimized Memory Tuning Properties

[memory_optimized = {on | OFF}]

The value on indicates this table is memory optimized. The default value off indicates this table is disk-based.

3,hash Index

Hash indexes are supported only on memory-optimized tables.

Bucket_count indicates the number of buckets that should is created in the hash index.

Limits for creating memory tuning tables

Restriction1: BLOB data type is not supported, size of each row cannot exceed 8060B (a buffer size)

The row size limit of 8060 bytes for memory optimized tables has been. Please simplify the table definition.

Restriction2:index key is not NULL

Nullable columns in the index key are not supported with indexes on memory tables.

Restriction3: Creates index on the character column, then character columns must use BIN2 collation.

Indexes on character columns ' do not ' use ' a *_bin2 collation are not supported with Indexes on memory optimized tables.

Restriction4: Column with data type (VAR) char whose code page must be 1252

The data types char (n) and varchar (n) using a collation that has a code page other than 1252 are not supported with memory Optimized tables.

Third, create the natively compiled SP

The local compilation SP compiles at creation time, and the entire SP executes atomically, which means that all operations in the entire SP are atomic operations, either successful or unsuccessful, in the SP.

--create SP
CREATE PROCEDURE Dbo.usp_getproduct
@ProductID bigint NOT NULL
With Native_compilation, schemabinding, EXECUTE as OWNER
As
BEGIN ATOMIC with (TRANSACTION isolation level = SNAPSHOT, LANGUAGE = N ' us_english ')

SELECT [ProductID]
, [Name]
, [Price]
, [unit]
, [Description]
from [dbo]. [Products]
where productid= @ProductID

End
Go
1, the Nullability property exists in the parameter definition of the local compilation SP, and null cannot be specified for the parameter if the parameter specifies not NULL.

Not nullconstraints on parameters of and variables in natively compiled stored. You are cannot assign null values to parameters or variables declared as NOT NULL.

CREATE PROCEDURE Dbo.myproc (@myVarchar varchar () NOT NULL) ...

DECLARE @myVarchar varchar () NOT NULL = "Hello"; --(Must initialize to a value.)

SET @myVarchar = null; --(compiles, but fails during run time.)

2, the locally compiled SP must contain two options: Schemabinding and ATOMIC block

Schemabinding:a natively compiled stored procedure must is bound to the schema of the objects it references.

ATOMIC block:all statements in ATOMIC blocks, which are required with natively compiled stored procedures, always run as P Art of a single transaction-either the actions of the atomic blocks as a whole are committed, or they are all rolled , in the case of a failure.

The natively compiled stored procedure body must consist to exactly one atomic block. Atomic blocks guarantee Atomic execution stored of the procedure. If The procedure is invoked outside the context of a active transaction, it'll start a new transaction, which commits a t the end of the atomic block.

Atomic blocks in natively compiled stored procedures have two options:

TRANSACTION isolation level. Transaction isolation levels for memory-optimized Tables for supported isolation.

LANGUAGE. The language for the stored procedure must is set to one of the available languages or language aliases.

3, the difference between an interpreted SP and a local compilation SP is that the interpreted SP compiles on the first execution, while the natively compiled SP is compiled at creation time.

One difference between interpreted (disk-based) stored procedures and natively compiled stored procedures is this inter Preted stored procedure is compiled at a/execution, whereas a natively compiled-stored procedure is compiled when it I S created. With natively compiled stored procedures, many error conditions can is detected at create time and'll cause creation of The natively compiled stored procedure to fail (such as arithmetic overflow, type conversion, and some Divide-by-zero itions). With interpreted stored procedures, which these error conditions typically does not cause a failure when the stored procedure is C reated, but all executions'll fail.

4, Delayed persistence

In the local compilation sp, set delayed_durability = ON, and the SP update operation on the Memory tuning table will delay persistent to disk. This means that if the memory tuning table maintains a copy of the disk-based, the data rows are modified in memory and are 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.

Appendix: Create syntax for natively compiled SP

--Syntax for SQL Server natively Compiled Stored procedures 
 
CREATE {PROC | PROCEDURE} [schema_name.] procedure_name 
    [{@parameter data_type} [NULL | Not NULL] [= default] [out | OUTPUT] [READONLY]] [,... n] 
  with Native_compilation, schemabinding [, EXECUTE as clause]  r> as 
{ 
  BEGIN ATOMIC with (set_option [,... n])  
Sql_statement [;] [... n] 
 [end] 
} 
 [;]  
 
<set_option>::= 
    LANGUAGE =  [N] ' LANGUAGE '  
  | TRANSACTION Isolation level =  {SNAPSHOT | Repeatable READ | SERIALIZABLE} 
  | [Datefirst = number] 
  | [DateFormat = Format] 
  | [delayed_durability = {off | on}]