SQL Server 2014 memory-optimized tables

Memory-Optimized tables are a new feature of SQL Server 2014, which can put tables in memory, which significantly improves DML performance.
For memory-optimized tables, refer to the two Heroes article: SQL Server 2014 new features Quest (1)-Memory Database try SQLSERVER2014 memory-Optimized table

It is also easy to create a memory-optimized table with the following tests:

To add a memory-optimized database filegroup:
[SQL] View plain copy on code to see a snippet derived from my Code slice
Use [master]
GO
--Add a memory-optimized database filegroup in the current database (only 1 filegroups per database)
ALTER DATABASE [Demo] ADD FILEGROUP [fg_memorytable] CONTAINS Memory_optimized_data
GO

--Create a new file to add to the filegroup
ALTER DATABASE [Demo]
ADD FILE
(
NAME = ' demo_memorytable ',
FILENAME = ' F:\VMWareSystem\database\Demo '
)
To FILEGROUP [fg_memorytable];
GO
The database file is added here, specifying a path. The path file is as follows:
(Forget about it.) ）

To create a memory-optimized table:
[SQL] View plain copy on code to see a snippet derived from my Code slice
use [Demo];
GO
--Memory-optimized tables
CREATE TABLE [memorytable]
(
[Guid] uniqueidentifier not NULL
CONSTRAINT ix_memorytable PRIMARY KEY nonclustered
HASH with (Bucket_count = 1024),
[Name] NVARCHAR () not NULL,
[Value] INT NULL
)
With (memory_optimized = on, durability = schema_and_data);/* in memory and disk */
GO
--Ordinary table
CREATE TABLE [clustertable]
(
[Guid] uniqueidentifier not NULL
CONSTRAINT ix_clustertable PRIMARY KEY nonclustered,
[Name] NVARCHAR () not NULL,
[Value] INT NULL
)
GO

Creating a memory-optimized table is currently only possible with scripting. With statement to specify the setting of the 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 recommends that the number of buckets be one to twice times the number of unique columns in the table.
memory_optimized = on: To enable memory-optimized tables;
Durability = Schema_and_data: Indicates that the data is stored in memory and in filegroups.
(durability = schema_only means data is stored only in memory)

Test IO situation:
[SQL] View plain copy on code to see a snippet derived from my Code slice
--Insert 10,000 rows of data
SET NOCOUNT on
INSERT into [memorytable]
SELECT NEWID (), ' Hello.kk ' +convert (VARCHAR), ABS (CHECKSUM (NEWID ()))%1000), ABS (CHECKSUM (NEWID ()))%1000
GO 10000
SET NOCOUNT OFF

INSERT into [clustertable] SELECT * FROM [memorytable]
GO

--View IO status
SET STATISTICS IO on
SELECT * FROM [memorytable] with (SNAPSHOT)
SELECT * FROM [clustertable]
SET STATISTICS IO OFF
GO

(10000 rows affected)
(1 rows affected)

(10000 rows affected)
Table ' clustertable '. Scan count 1, logical read 68 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
(1 rows affected)

IO results, the memory table [memorytable] is not tracked to Io, and the physical table [clustertable] can be seen to be read.
It is also not visible here whether the data is in memory and is now tested again. Is the data not in memory?
Now test: Create a new memory-optimized table, note durability = schema_only, and the data remains in memory only.
[SQL] View plain copy on code to see a snippet derived from my Code slice
--Create another test table
CREATE TABLE [Memorytest]
(
[Guid] uniqueidentifier not NULL CONSTRAINT ix_memorytest PRIMARY KEY nonclustered,
[Name] NVARCHAR () not NULL,
[Value] INT NULL
)
With (memory_optimized = on, durability = schema_only);/* Only in memory */
GO

--Insert 10 rows of data
INSERT into [memorytest] SELECT NEWID (), ' Hello.kk ', 100
GO 10

--View, have data!
SELECT * FROM [memorytest]


--Note: Restart the SQL Server service now!


--Look again, there is no data in the table!!
SELECT * FROM [memorytest]

--drop TABLE [Memorytest]

The data query is not available after restarting the SQL Server service! Because the data is only kept in memory, any loss of the SQL Server service will result in the data being lost. That means the data is in memory! Executing DBCC dropcleanbuffers has no effect on memory-optimized table data.

Memory-Optimized tables Many limitations:
The memory table does not support modifying bucket_count,truncate TABLE,DML triggers, where the identity initial value or increment is not 1.
Alter OPERATION (constraint check, foreign key, add or remove index, add or remove columns, change column properties) is not supported in the memory table
The following scenarios are not supported in memory-optimized tables:
[SQL] View plain copy on code to see a snippet derived from my Code slice
TRUNCATE TABLE [memorytable]
GO

CREATE TRIGGER tr_memorytable
On [memorytable] after INSERT
As
BEGIN
INSERT into [memorytable] ([Guid],[name],[value])
SELECT [Guid],[name],[value] from inserted
END
GO

CREATE TABLE [Memoryteattab]
(
[ID] INT IDENTITY (2,1) not NULL PRIMARY KEY nonclustered
)
With (memory_optimized = on);
GO

ALTER TABLE [memorytable] ADD CONSTRAINT ck_value CHECK ([value] between 0 and 1000)
GO
Alter TABLE [memorytable] Alter COLUMN [value] SMALLINT NULL
GO
ALTER TABLE [memorytable] ADD [value2] SMALLINT NULL
GO
CREATE nonclustered INDEX ix_memorytable_name on [memorytable] ([NAME])
GO
ALTER TABLE [memorytable]
ADD CONSTRAINT fk_memorytable_clustertable FOREIGN KEY ([Guid])
REFERENCES [Clustertable] ([Guid]) on UPDATE CASCADE on DELETE CASCADE
GO

Msg 10794, Level 16, State 92, line 113th
This statement "TRUNCATE table" is not supported by memory-optimized tables.
Msg 10794, Level 16, State 77, procedure tr_memorytable, line 116th
This operation "CREATE TRIGGER" is not supported by memory-optimized tables.
Msg 12339, Level 16, State 21, line 125th
Memory-Optimized tables do not support seeding and increment values using values other than 1.
Msg 10794, Level 16, State 14, line 132th
This operation "ALTER table" is not supported by memory-optimized tables.
Msg 10794, Level 16, State 14, line 134th
This operation "ALTER table" is not supported by memory-optimized tables.
Msg 10794, Level 16, State 14, line 136th
This operation "ALTER table" is not supported by memory-optimized tables.
Msg 10794, Level 16, State 12, line 138th
This operation "CREATE INDEX" is not supported by memory-optimized tables.
Msg 10794, Level 16, State 14, line 140th
This operation "ALTER table" is not supported by memory-optimized tables.

There is also an important concept for using memory-Optimized tables: Native compilation
Native compilation can improve the speed of accessing data and the efficiency of executing queries. Memory-Optimized tables will be recompiled during the server restart. To speed up database recovery, natively compiled stored procedures are not recompiled during server restarts, but are compiled on first execution. If the compilation fails or is interrupted, some of the generated files will not be deleted. These files are intentionally reserved for support purposes and are deleted when the database is deleted.
[SQL] View plain copy on code to see a snippet derived from my Code slice
--View compiled DLLs for memory-optimized tables
Select Description,name from Sys.dm_os_loaded_modules
Where name like '%xtp_t_ ' + cast (db_id () as varchar ()) + ' _ ' + cast (object_id (' dbo '). Memorytable ') as varchar + '. dll '
Go

--More DLLs
SELECT Description,name from Sys.dm_os_loaded_modules
where description = ' XTP Native DLL '

Description Name
-------------------- ----------------------
XTP Native DLL D:\Program Files\Microsoft SQL Server\mssql12. Mssqlserver\mssql\data\xtp\5\xtp_t_5_949578421.dll

Natively compiled stored procedures: The internal parameters or settings of a stored procedure are necessary! Compiled at creation time, and interpreted stored procedures are compiled on first execution.
(more reference: natively compiled stored procedures)
[SQL] View plain copy on code to view the snippets derived from my Code slice
CREATE PROCEDURE DBO. NATIVE_SP
with Native_compilation, SCHEMABINDING, EXECUTE as OWNER
as
BEGIN ATOMIC
with (TRANSACTION Isol ATION level=snapshot,language=n ' Simplified Chinese '
SELECT TOP 5 [guid],[name],[value]
from DBO. Memorytable ORDER by NEWID ()
END
GO

EXEC DBO. NATIVE_SP
GO

--View stored procedure compiled DLL
Select name, description from Sys.dm_os_loaded_modules
where name is like '%xtp_p_ ' + cast (db_id () as varchar) + ' _ ' + cast (object_id (' dbo '). NATIVE_SP ') as varchar + '. dll '
Go

The parameters are described as follows:
Native_compilation: Indicates local compilation
SCHEMABINDING: A natively compiled stored procedure must be bound to the schema of its referenced object
EXECUTE AS OWNER: The natively compiled stored procedure does not support EXECUTE as CALLER, which is the default execution context. Therefore, the execution context needs to be specified. The option execute as OWNER, execute Asuser, and execute as self is supported.
BEGIN ATOMIC: Natively compiled stored procedure bodies must consist of exactly one atomic block. Atomic blocks ensure atomic execution of stored procedures. If the procedure is used in the context of an active transaction, it starts a new transaction, which is committed at the end of the atomic block. (For more information: atomic blocks)
TRANSACTION Isolation Level: Transaction isolation Levels SNAPSHOT, RepeatableRead, and SERIALIZABLE must be set.
LANGUAGE: The language of the stored procedure must be set to one of the available languages or language aliases.
[SQL] View plain copy on code to see a snippet derived from my Code slice
--View the DLL compiled by the stored procedure
Select name, description from Sys.dm_os_loaded_modules
Where name like '%xtp_p_ ' + cast (db_id () as varchar ()) + ' _ ' + cast (object_id (' dbo '). NATIVE_SP ') as varchar + '. dll '
Go

An error occurred while changing the stored procedure:
Msg 10794, Level 16, State 25, procedure native_sp, line 168th
This operation "ALTER PROCEDURE" is not supported by natively compiled stored procedures.

The transaction isolation level is READ COMMITTED error:
Msg 10794, Level 16, State 81, procedure native_sp, line 171th
This transaction isolation level "READ COMMITTED" is not supported by natively compiled stored procedures.

Memory-Optimized table transaction ISOLATION level:
Isolation level for transaction support for accessing memory-optimized tables: Snapshot,repeatable read,serializable,read COMMITTED.
Memory-Optimized tables do not use locks. You can use a higher isolation level (such as repeatable READ and SERIALIZABLE) to declare the required guarantees.
Locking hints are not supported. Instead, declare the required guarantees through the transaction isolation level.
(NOLOCK is supported because SQL Server does not use locks for memory-optimized tables.) Note that unlike disk-based tables, NOLOCK does not imply READ uncommitted behavior for memory-optimized tables. ）

For autocommit transactions, the isolation level of READ COMMITTED is implicitly mapped to the SNAPSHOT of the memory-optimized table. READ COMMITTED is not supported for explicit or implicit user transactions.
Therefore, if the TRANSACTION isolation level session setting is set to READ COMMITTED, you do not need to specify isolation levels through table hints when accessing memory-optimized tables.

The following error is used in this way!
[SQL] View plain copy on code to see a snippet derived from my Code slice
--Execution Error!
SET TRANSACTION Isolation Level READ COMMITTED
BEGIN TRAN
SELECT TOP 5 [Guid],[name],[value] from DBO. Memorytable ORDER by NEWID ()
ROLLBACK TRAN
GO

Msg 41368, Level 16, State 0, line 198th
Use the READ COMMITTED isolation level to access memory-optimized tables only for autocommit transactions. It does not apply to explicit or implicit transactions. Use table hints, such as with (SNAPSHOT), to provide a supported isolation level for memory-optimized tables.

Execute the method correctly! The following 3 kinds!
[SQL] View plain copy on code to see a snippet derived from my Code slice
--Execute the method correctly! The following 3 kinds!
SET TRANSACTION Isolation Level READ COMMITTED
SELECT TOP 5 [Guid],[name],[value] from DBO. Memorytable ORDER by NEWID ()
GO


BEGIN TRAN
SELECT TOP 5 [Guid],[name],[value] from DBO. Memorytable with (SNAPSHOT) ORDER by NEWID ()
ROLLBACK TRAN
GO


ALTER DATABASE Demo SET memory_optimized_elevate_to_snapshot on
BEGIN TRAN
SELECT TOP 5 [Guid],[name],[value] from DBO. Memorytable ORDER by NEWID ()
ROLLBACK TRAN
GO

Transaction Isolation test:

Open Session 1 Execute the following statement to make the update clogged:
[SQL] View plain copy on code to see a snippet derived from my Code slice
--Session 1
SET TRANSACTION Isolation Level READ COMMITTED
BEGIN TRAN
UPDATE [Memorytable]with (SNAPSHOT) SET value = 9999 WHERE guid= ' 2d153c8a-498d-4619-a58f-491cead2a031 '
WAITFOR DELAY ' 00:00:20 '
ROLLBACK TRAN

Open Session 2, query or update the same statement:
[SQL] View plain copy on code to see a snippet derived from my Code slice
--Session 2
--Repeatable Read, read a snapshot before [session 1]
SET TRANSACTION Isolation Level READ COMMITTED
SELECT * FROM [Memorytable]with (SNAPSHOT) WHERE guid= ' 2d153c8a-498d-4619-a58f-491cead2a031 '

--The change is an error
UPDATE [Memorytable]with (SNAPSHOT) SET Name = ' KK ' WHERE guid= ' 2d153c8a-498d-4619-a58f-491cead2a031 '
/*
Msg 41302, Level 16, State 110, line 3rd
The current transaction attempts to update records that have been updated since the transaction started. The transaction has been aborted.
Statement has been terminated.
*/

Find the query is not blocked, it is equivalent to repeatable READ!

There are some error scenarios related to transactions that access memory-optimized tables:
41302. The current transaction attempts to update records that have been updated since the transaction started. (A Write/write conflict occurs if two transactions attempt to update the same row)
41305. The current transaction could not be committed because of a repeatable read validation failure.
41325. The current transaction could not be committed because a serialization read validation failed.
41301. The previous transaction on which the current transaction is dependent has been terminated and the current transactions cannot be resubmitted.
[SQL] View plain copy on code to see a snippet derived from my Code slice
--memory-optimized tables allow for optimistic concurrency control with higher isolation levels repeatable READ and SERIALIZABLE.
--Change the transaction isolation level to repeatable READ or serializable, and then you must execute the statement under Snapshot Isolation (with (SNAPSHOT)).
SET TRANSACTION Isolation Level READ COMMITTED
SET TRANSACTION Isolation Level repeatable READ
SET TRANSACTION Isolation Level SERIALIZABLE

Other Notes:
Memory-Optimized tables do not support cross-database transactions. Each transaction accessing a memory-optimized table cannot access multiple databases (except read/write access to tempdb and read-only access to the system's primary database).
Memory-Optimized tables do not support distributed transactions. A distributed transaction beginning with begin distributed TRANSACTION cannot access memory-optimized tables.
Memory-Optimized tables do not support locking. Memory-Optimized tables do not support explicit locks implemented through lock hints such as TABLOCK, XLOCK, Rowlock.

UPDATE STATISTICS:
By default, statistics for memory-optimized tables are not updated. For disk-based tables, sp_updatestats (Transact-SQL) only updates UPDATE STATISTICS if the table has been modified since the last sp_updatestats (Transact-SQL). For memory-optimized tables, sp_updatestats (Transact-SQL) always generates updated statistics.
[SQL] View plain copy on code to see a snippet derived from my Code slice
--Update a single memory-optimized table (MySchema. Mytable) Statistical information:
UPDATE STATISTICS [DBO]. [Memorytable] With FULLSCAN, NoRecompute


--Update statistics for all memory-optimized tables in the current database:
DECLARE @sql NVARCHAR (MAX) = N "

SELECT @sql + = n ' UPDATE STATISTICS ' + QuoteName (schema_name (schema_id)) + n '. ' + QuoteName (name) + n ' with FULLSCAN, NORE COMPUTE '
From Sys.tables WHERE is_memory_optimized=1

EXEC sp_executesql @sql

--memory-optimized table statistics Last updated:
Select t.object_id, T.name, sp.last_updated as ' stats_last_updated '
From Sys.tables t
Inner JOIN sys.stats s on t.object_id=s.object_id
Cross Apply Sys.dm_db_stats_properties (t.object_id, s.stats_id) SP
where t.is_memory_optimized=1

SQL Server 2014 memory-optimized tables