[SQL Server]SQL2014和SQL2016 In-Memory OLTP比較

2017-04-15

Memory table將所有資料都存放到記憶體，內部針對每一筆資料建立multiple versions避免blocking情況

(multi-version optimistic concurrency control多版本樂觀並行控制)，同時也沒有任何的locks和latchs

下面兩張查詢處理可以看出Disk-base table和memory optimized table差異

SQL Server Disk-base Table of Query processing

Source: Microsoft

SQL Server Memory optimized tables of Query processing

Source: Microsoft

Memory optimized table主要特點

資料讀取和寫入在記憶體中，因為整個資料表都存放在記憶體。
使用multi-version optimistic concurrency control所以不會發生blocking
資料持久性可依個別資料表應用選擇持久或非持久(如果啟用資料持久性，那麼會在硬碟中保留第二個副本)。
復原資料庫期間，必須從disk讀取資料才能載入記憶體。
可以和Disk-base table互相存取操作。

Note:如果要全力追求效能，那麼必須要承擔memory optimized table的資料遺失風險(啟用延遲交易)，使用完全交易則不用擔心資料遺失風險。

Natively-compiled Store Procedure

Natively-compiled Store Procedure預先編譯成可存取memory optimized table的機器碼，

可以使用有效率的方式執行SP中的查詢和商業邏輯處理，主要特點如下

建立時會建立一顆DLL(native code)，實際上執行會使用該DLL的entry point
改善並優化compile 所需時間
只能存取memory optimized table

In-Memory OLTP使用案例

高輸送量且低延遲的交易處理:為個別交易提供一致的低延遲，以支援大量交易，最小化I/O並消除資源競爭，而這也是最核心案例。
擷取大量資料: ETL作業效率，參考[SQL SERVER]傳統資料表和In-Memory資料表 ETL 效能比較。
取代tempdb物件:相較於傳統資料表變數和 #temp 資料表，記憶體最佳化資料表變數和非持久性資料表通常會減少 CPU，並完全移除記錄檔 IO。參考[SQL SERVER]善用 In-Memory 資料表變數提高效能
cache and session:SQL維護session state(ex:Asp.net application) 和cache非常便利且高效率(ex:即時報表分析)。

SQL2014和SQL2016限制比較

特點	SQL Server 2014	SQL Server 2016
Memory optimized table記憶體限制	256 GB	2TB
定序支援	必須使用*_bin2	支援所有定序
Alter memory optimized table	No(drop and create)	Yes
Alter native store procedure	No	Yes
Parallel plan 運算子	No	Yes
Transparent Data Encryption (TDE)	No	Yes
Multiple Active Result Sets (MARS)	No	Yes
Large Objects (LOBs): Varchar(max)、nvarchar(max)、varbinary(max)	No	Yes
離線 Checkpoint 執行緒	1	Multiple Threads
Natively-compiled, scalar user-defined functions	No	Yes
Indexes on NULLable columns	No	Yes
DML triggers	No	Yes
Natively store procedure支援下面語法 LEFT and RIGHT OUTER JOIN SELECT DISTINCT OR and NOT operators Subqueries Nested stored procedure calls UNION and UNION ALL 所有內建數學function	No	Yes

最後我簡單比較SQL2014和SQL2016 Memory optimized table處理資料的效能改善

說明:我會在SQL2014和SQL2016建立相同Memory optizimed資料表，並使用SQLQuerystress執行insert and delete

use mymemoryDB
go
ALTER DATABASE mymemoryDB COLLATE Latin1_General_100_CS_AS--for SQL2014
GO


CREATE TABLE [dbo].[rsa241](
	[num] [int] identity NOT NULL,
	[stime] [char](8) NOT NULL,
	[event_code] [varchar](4)  NULL,
	[event_des] [nvarchar](50)  NULL,
	[event_card] [varchar](10)  NULL,
	[door_name] [nvarchar](20)  NULL,
	[device_id] [varchar](20)  NULL,
	[site_id] [varchar](12)  NULL,
	[ip] [varchar](16)  NULL,
	[device_name] [nvarchar](20)  NULL,
	[ncuip] [varchar](16)  NULL,
	[emp_id] [varchar](20)  NULL,
	[emp_name] [nvarchar](20)  NULL,
	[sdate] [varchar](10)  NULL,
	[emp_dep] [nvarchar](12) NULL,
	primary key nonclustered (num),
	index idx_hash nonclustered hash([num]) with(bucket_count=10000)
) 
with(memory_optimized=on,durability=schema_and_data)

INSERT INTO [dbo].[rsa241]
           ([stime]
           ,[event_code]
           ,[event_des]
           ,[event_card]
           ,[door_name]
           ,[device_id]
           ,[site_id]
           ,[ip]
           ,[device_name]
           ,[ncuip]
           ,[emp_id]
           ,[emp_name]
           ,[sdate]
           ,[emp_dep])
     VALUES
           ('16:22:20'
           ,'0125'
           ,N'管理卡通過'
           ,'0000814800'
           ,'0002'
           ,'0201'
           ,'P09405020001'
           ,'10.8.2.12'
           ,N'辦公區-1(大中)'
           ,'10.8.2.20'
           ,'M0171'
           ,N'小潘潘'
           ,'2010/01/07'
           ,'2600')
   delete [dbo].[rsa241]