SQLSERVER2014 Memory-Optimized tables

The memory engine (codenamed Hekaton) in SQL Server 2014 elevated the OLTP to a new height.

The storage engine is now integrated into the current database management system and uses advanced memory technology to support large-scale OLTP workloads.

Even so, to take advantage of this new feature, the database must contain "memory-optimized" filegroups and tables

That is, the configured filegroups and tables use Hekaton technology.

Fortunately, SQL Server 2014 makes this process very straightforward.

To illustrate how it works, let's create a database named Testhekaton , and add a memory-optimized filegroup to this database

test Environment : Microsoft Azure Continental Virtual machine

4 cores, 7G memory, WINDOWS2012R2

SQLSERVER2014 Enterprise Edition

Experiment

First experiment: Simple use of memory tables

Step 1: Create a database and a memory_optimized_data filegroup

Use master; Gocreate DATABASE Testhekaton; Goalter DATABASE testhekatonadd FILEGROUP hekatonfg CONTAINS memory_optimized_data; GO

Note the Add FILEGROUP statement in the ALTER DATABASE statement contains the name of the filegroup (hekatonfg) and the keyword CONTAINS memory_optimized_data

It instructs SQL Server to create the file group types that are necessary to support the memory OLTP engine.

Note: There can only be one memory_optimized_data filegroup per database!!

To confirm that this filegroup has been created, you can access the filegroups interface of the database properties in SSMs, as shown in.

Step 2:

Adding a data file to a filegroup can be implemented by the ALTER DATABASE statement.

Add a new data file to the HEKATONFG filegroup:

ALTER DATABASE testhekatonadd FILE (   NAME = ' hekatonfile ',   FILENAME = ' C:\Program Files\Microsoft SQL Server\ MSSQL12. Mssqlserver\mssql\data\hekatonfile ') to FILEGROUP [HEKATONFG]; GO

Note: In the Add File statement, we only provide a friendly name for the file path and filename.

Also, in the to FILEGROUP statement, specify a name for the new filegroup.

You can then go to the files interface of the database properties to view the file you just added.

Step 3:

After you have set up the required filegroups and files for your database, you can create your own memory-optimized tables.

When a table is defined, its " persistence"is specified.

A memory-optimized table can be either persistent or non-persistent .

(1) For a persistent table, the data is stored in memory and is also stored in a memory-optimized filegroup.

(2) For a non-persistent table, the data is stored only in memory , so if the system crashes or restarts, the data is lost.

The default is the persistent table in SQL Server 2014, so let's take a closer look.

When defining a persistent memory-optimized table, you must also define a primary key that is based on a nonclustered hash index.

In a hash index, the data is accessed through a memory hash instead of a fixed-size page.

A hash index is the only type of index supported in a memory-optimized table.

In addition to defining the primary key in the table definition, you must also configure the table to be memory-optimized, as shown in the CREATE TABLE statement:

Use Testhekaton; Gocreate TABLE Reseller    (      [Resellerid] INT not NULL                       PRIMARY KEY nonclustered HASH with (Bucket_count = 1024), C3/>[resellername] NVARCHAR () not NULL,      [Resellertype] NVARCHAR (NO) null) with    (memory_optimized = ON, DUR ability = schema_and_data); INSERT into  resellervalues  (1, ' A Bike Store ', ' Value Added Reseller ');

The resellerid field definition contains a primary key that is defined as a nonclustered hash.

Note that you must include a with statement to specify the setting for Bucket_count, which indicates the number of buckets that should be created in the hash index.

(Each bucket is a slot that can be used to hold a set of key-value pairs.) )

Microsoft suggests buckets should be as many as one or two times the number of unique index keys that you expect the table to contain.

This table definition ends with a second with statement.

Here you specify that the Memory_optimized option is on and the durability option is schema_and_data, which is for persistent tables.

Then insert a record in the table so that you can test it.

The data is already inserted into the table

This is the whole process of creating a memory-optimized table, and everything else is going to be behind the scenes.

However, keep in mind that SQL Server 2014 has many restrictions on these tables. For example, they do not support foreign key or constraint checking (feel like the memory storage engine of MySQL),

They also do not support identity fields or DML triggers. Most importantly, memory exhaustion can cause write activity to stop.

Step 4:

On the other hand, memory-optimized tables support natively compiled stored procedures, as long as those stored procedures only reference memory-optimized tables.

In this case, the stored procedure can be converted to native code, which executes faster and requires less memory than a typical stored procedure.

In addition to referencing only memory-optimized tables, a local compiled stored procedure must be schema-bound and run within a specific execution content.

In addition, each local compilation stored procedure must consist entirely of an atomic block.

The Create PROCEDURE statement below defines a locally compiled stored procedure that retrieves data from the reseller table created in the previous example

CREATE PROCEDURE Getresellertype (@id INT)    with Native_compilation,         SCHEMABINDING,         EXECUTE as Owneras    BEGIN  ATOMIC with (TRANSACTION isolation level = SNAPSHOT, LANGUAGE = ' us_english ')        SELECT  resellername,                Resellertype        from    dbo. Reseller        WHERE   resellerid = @id    END; GO

After the parameters have been defined, a with statement is included to specify the Native_compilation option.

Note: This statement also contains the SCHEMABINDING option and the EXECUTE AS option, as well as specifying the owner as the execution environment.

The WITH statement is responsible for implementing the three requirements of a locally compiled stored procedure.

To resolve atomic block requirements, you can specify atomic after the BEGIN keyword, followed by another with statement that contains the transaction isolation level and language.

for transactions that access memory-optimized tables, you can use Snapshot,repeatableread or serializable as the isolation level .

Also, you must use one of the available language or language aliases for this language.

This is all you need to include when defining a stored procedure. Once created, you can test it by executing an EXECUTE statement, as shown in the following example:

EXEC Getresellertype 1;

This statement returns the name and type of the reseller, in this case Abike store and value Added Reseller, respectively.

First experiment: Data query speed comparison of memory tables

Comparison of clustered index tables and memory-optimized tables

Build a Table statement

Use Testhekaton; go--Memory-Optimized table CREATE TABLE Testmemory1    (      [ID] FLOAT not NULL                       PRIMARY KEY nonclustered HASH with (Bucket_count = 102 4),      [Name] NVARCHAR (+) not NULL     ) with (memory_optimized = on, durability = Schema_and_data);

Use Testhekaton; go--Clustered Index Table CREATE table Testmemory2    (      [ID] FLOAT NOT null PRIMARY KEY,      [Name] NVARCHAR (a) NOT null     )

---------------------------------------------------------------

Insert Performance Comparison

Memory-Optimized tables

Set STATISTICS IO on set STATISTICS time ONINSERT to Testmemory1 ([Id],[name])  SELECT [id], [name] from Sysobjectsset STATISTICS IO OFFSET STATISTICS time OFF

Table ' Sysschobjs '. Scan count 1, logical reads, physical reads 0, Read-ahead reads 0, LOB logical reads 0, LOB physical reads 0, LOB read- Ahead reads 0. SQL Server execution times:   CPU time = 0 ms,  elapsed time = Ms. (s) affected) SQL Server execution times:   CPU time = 0 ms,  elapsed time = 0 ms. SQL Server Execution times:   CPU time = 0 ms,  elapsed time = 0 Ms.

Clustered Index Table

Set STATISTICS IO on set STATISTICS time ONINSERT to Testmemory2 ([Id],[name])  SELECT [id], [name] from Sysobjectsset STATISTICS IO OFFSET STATISTICS time OFF

Table ' Testmemory2 '. Scan count 0, logical reads 183, physical reads 0, Read-ahead reads 0, LOB logical reads 0, LOB physical reads 0, LOB read -ahead reads 0.Table ' Sysschobjs '. Scan count 1, logical reads, physical reads 0, Read-ahead reads 0, LOB logical reads 0, LOB physical reads 0, LOB read- Ahead reads 0. SQL Server execution times:   CPU time = 0 ms,  elapsed time = Ten Ms. (s) affected) SQL Server execution times:   CPU time = 0 ms,  elapsed time = 0 Ms.

-------------------------------------------------------------------------------

Query Performance Comparison

Memory-Optimized tables

Set STATISTICS io on set STATISTICS time ONSELECT * from  testmemory1  ORDER by [ID] descset STATISTICS IO ONSET STA Tistics time on

SQL Server parse and compile time:    CPU time = 0 ms, Elapsed time = 1 ms. SQL Server Execution times:   CPU time = 0  MS,  Elapsed time = 0 ms. SQL Server Execution times:   CPU time = 0 ms,  elapsed time = 0 Ms. (s) affected) SQL Server execution times:   CPU time = 0 ms,  elapsed time = 0 ms. SQL Server Execution times:   CPU time = 0 ms ,  Elapsed time = 0 Ms.

Clustered Index Table

Set STATISTICS io on set STATISTICS time ONSELECT * from  testmemory2  ORDER by [ID] descset STATISTICS IO ONSET ST Atistics time on

(Affected row (s)) Table ' Testmemory2 '. Scan count 1, logical reads 2, physical reads 0, Read-ahead reads 0, LOB logical reads 0, LOB physical reads 0, LOB Read-a Head reads 0. SQL Server execution times:   CPU time = 0 ms,  elapsed time = 0 ms. SQL Server Execution times:   CPU time = 0 MS,  Elapsed time = 0 Ms.

can see memory-optimized table read/write Data (insert, select) do not see IO Read and Write

Supplemental testing:

We first delete the data that we just inserted, the memory-optimized table does not support TRUNCATE TABLE, only with the delete from table

can only delete

Insert Test

Memory-Optimized tables

Clustered Index Table

-------------------------------------------------------------------------------------------------

Query test

Memory-Optimized tables

Clustered Index Table

Let's take a look at the transaction log

Checkpointgoselect Context, Operation,allocunitnamefrom sys.fn_dblog (null, NULL)

Context	Operation	Allocunitname
Lcx_null	Lop_hk	Null
Lcx_null	lop_hk_chained	Null
Lcx_null	Lop_hk	Null
Lcx_null	lop_hk_chained	Null
Lcx_null	Lop_hk_checkpoint	Null
Lcx_null	Lop_hk	Null
Lcx_null	Lop_begin_xact	Null
Lcx_null	Lop_fs_downlevel_op	Null
Lcx_null	Lop_begin_xact	Null
Lcx_clustered	Lop_insert_rows	Sys.filestream_tombstone_2073058421.FSTSClusIdx
Lcx_index_leaf	Lop_insert_rows	Sys.filestream_tombstone_2073058421.FSTSNCIdx
Lcx_null	Lop_commit_xact	Null
Lcx_mark_as_ghost	Lop_delete_rows	Sys.filestream_tombstone_2073058421.FSTSClusIdx
Lcx_mark_as_ghost	Lop_delete_rows	Sys.filestream_tombstone_2073058421.FSTSNCIdx
Lcx_null	Lop_hk	Null
Lcx_null	Lop_fs_downlevel_op	Null
Lcx_heap	Lop_insert_rows	Sys.xtp_storage
Lcx_index_leaf	Lop_insert_rows	Sys.xtp_storage. Uq__xtp_stor__3213e83ea8737d06
Lcx_clustered	Lop_expunge_rows	Sys.filestream_tombstone_2073058421.FSTSClusIdx
Lcx_clustered	Lop_expunge_rows	Sys.filestream_tombstone_2073058421.FSTSClusIdx
Lcx_pfs	Lop_set_bits	Sys.filestream_tombstone_2073058421.FSTSClusIdx
Lcx_index_leaf	Lop_expunge_rows	Sys.filestream_tombstone_2073058421.FSTSNCIdx
Lcx_index_leaf	Lop_expunge_rows	Sys.filestream_tombstone_2073058421.FSTSNCIdx
Lcx_null	Lop_commit_xact	Null
Lcx_null	Lop_begin_xact	Null
Lcx_null	Lop_fs_downlevel_op	Null
Lcx_null	Lop_begin_xact	Null
Lcx_clustered	Lop_insert_rows	Sys.filestream_tombstone_2073058421.FSTSClusIdx
Lcx_index_leaf	Lop_insert_rows	Sys.filestream_tombstone_2073058421.FSTSNCIdx
Lcx_null	Lop_commit_xact	Null
Lcx_mark_as_ghost	Lop_delete_rows	Sys.filestream_tombstone_2073058421.FSTSClusIdx
Lcx_pfs	Lop_set_bits	Sys.filestream_tombstone_2073058421.FSTSClusIdx
Lcx_mark_as_ghost	Lop_delete_rows	Sys.filestream_tombstone_2073058421.FSTSNCIdx
Lcx_null	Lop_fs_downlevel_op	Null
Lcx_heap	Lop_insert_rows	Sys.xtp_storage
Lcx_index_leaf	Lop_insert_rows	Sys.xtp_storage. Uq__xtp_stor__3213e83ea8737d06
Lcx_null	Lop_commit_xact	Null
Lcx_null	Lop_hk	Null
Lcx_clustered	Lop_expunge_rows	Sys.filestream_tombstone_2073058421.FSTSClusIdx
Lcx_index_leaf	Lop_expunge_rows	Sys.filestream_tombstone_2073058421.FSTSNCIdx
Lcx_pfs	Lop_set_bits	Sys.filestream_tombstone_2073058421.FSTSClusIdx
Lcx_pfs	Lop_set_bits	Sys.filestream_tombstone_2073058421.FSTSNCIdx
Lcx_clustered	Lop_count_delta	Sys.sysallocunits.clust
Lcx_clustered	Lop_count_delta	Sys.sysrowsets.clust
Lcx_clustered	Lop_count_delta	Sys.sysrscols.clst
Lcx_clustered	Lop_count_delta	Sys.sysrscols.clst
Lcx_clustered	Lop_count_delta	Sys.sysrscols.clst
Lcx_clustered	Lop_count_delta	Sys.sysrscols.clst
Lcx_clustered	Lop_count_delta	Sys.sysrscols.clst
Lcx_clustered	Lop_count_delta	Sys.sysrscols.clst
Lcx_clustered	Lop_count_delta	Sys.sysrscols.clst
Lcx_clustered	Lop_count_delta	Sys.sysrscols.clst
Lcx_clustered	Lop_count_delta	Sys.sysrscols.clst
Lcx_clustered	Lop_count_delta	Sys.sysrscols.clst
Lcx_clustered	Lop_count_delta	Sys.sysallocunits.clust
Lcx_clustered	Lop_count_delta	Sys.sysrowsets.clust
Lcx_clustered	Lop_count_delta	Sys.sysallocunits.clust
Lcx_clustered	Lop_count_delta	Sys.sysrowsets.clust
Lcx_clustered	Lop_count_delta	Sys.sysrscols.clst
Lcx_clustered	Lop_count_delta	Sys.sysrscols.clst
Lcx_clustered	Lop_count_delta	Sys.sysrscols.clst
Lcx_clustered	Lop_count_delta	Sys.sysallocunits.clust
Lcx_clustered	Lop_count_delta	Sys.sysrowsets.clust
Lcx_null	Lop_begin_ckpt	Null
Lcx_file_header	Lop_modify_streamfile_hdr	Null
Lcx_boot_page_ckpt	Lop_xact_ckpt	Null
Lcx_null	Lop_end_ckpt	Null
Lcx_null	Lop_hk	Null
Lcx_null	Lop_hk	Null
Lcx_null	Lop_hk	Null
Lcx_null	lop_hk_chained	Null
Lcx_null	Lop_hk	Null
Lcx_null	Lop_hk	Null
Lcx_null	Lop_begin_xact	Null
Lcx_null	Lop_fs_downlevel_op	Null
Lcx_null	Lop_begin_xact	Null
Lcx_clustered	Lop_insert_rows	Sys.filestream_tombstone_2073058421.FSTSClusIdx
Lcx_index_leaf	Lop_insert_rows	Sys.filestream_tombstone_2073058421.FSTSNCIdx
Lcx_null	Lop_commit_xact	Null
Lcx_mark_as_ghost	Lop_delete_rows	Sys.filestream_tombstone_2073058421.FSTSClusIdx
Lcx_pfs	Lop_set_bits	Sys.filestream_tombstone_2073058421.FSTSClusIdx
Lcx_mark_as_ghost	Lop_delete_rows	Sys.filestream_tombstone_2073058421.FSTSNCIdx
Lcx_pfs	Lop_set_bits	Sys.filestream_tombstone_2073058421.FSTSNCIdx
Lcx_null	lop_hk_chained	Null
Lcx_null	lop_hk_chained	Null
Lcx_null	Lop_hk_checkpoint	Null
Lcx_null	Lop_fs_downlevel_op	Null
Lcx_heap	Lop_insert_rows	Sys.xtp_storage
Lcx_index_leaf	Lop_insert_rows	Sys.xtp_storage. Uq__xtp_stor__3213e83ea8737d06
Lcx_null	Lop_commit_xact	Null
Lcx_null	Lop_begin_xact	Null
Lcx_null	Lop_fs_downlevel_op	Null
Lcx_null	Lop_begin_xact	Null
Lcx_clustered	Lop_insert_rows	Sys.filestream_tombstone_2073058421.FSTSClusIdx
Lcx_index_leaf	Lop_insert_rows	Sys.filestream_tombstone_2073058421.FSTSNCIdx
Lcx_null	Lop_commit_xact	Null
Lcx_mark_as_ghost	Lop_delete_rows	Sys.filestream_tombstone_2073058421.FSTSClusIdx
Lcx_mark_as_ghost	Lop_delete_rows	Sys.filestream_tombstone_2073058421.FSTSNCIdx
Lcx_null	Lop_fs_downlevel_op	Null
Lcx_heap	Lop_insert_rows	Sys.xtp_storage
Lcx_index_leaf	Lop_insert_rows	Sys.xtp_storage. Uq__xtp_stor__3213e83ea8737d06
Lcx_null	Lop_commit_xact	Null
Lcx_null	Lop_hk	Null
Lcx_clustered	Lop_expunge_rows	Sys.filestream_tombstone_2073058421.FSTSClusIdx
Lcx_clustered	Lop_expunge_rows	Sys.filestream_tombstone_2073058421.FSTSClusIdx
Lcx_index_leaf	Lop_expunge_rows	Sys.filestream_tombstone_2073058421.FSTSNCIdx
Lcx_index_leaf	Lop_expunge_rows	Sys.filestream_tombstone_2073058421.FSTSNCIdx
Lcx_pfs	Lop_set_bits	Sys.filestream_tombstone_2073058421.FSTSClusIdx
Lcx_pfs	Lop_set_bits	Sys.filestream_tombstone_2073058421.FSTSNCIdx
Lcx_pfs	Lop_modify_header	Unknown Alloc Unit

Summarize

Memory-Optimized tables also write transaction logs, and when read and write operations find memory-optimized tables with no I/O times, it should be that the data is already in memory

For more details, refer to:

SQL Server 2014 new features--memory database

SQL Server 2014 new features: Partitioned indexes and memory-optimized tables

MSDN: Memory-Optimized tables

SQLSERVER2014 Memory-Optimized tables