SQL Server 2014 In-Memory OLTP測試與使用心得

在SQL Server 2014的所有新功能中，最被官方與大家所注視的，就是In-Memory的功能，本篇我們就來介紹此新功能，並將個人的使用心得與搜集到的資訊整理出來，也歡迎大家一定討論與分享。

一開始我們先來簡單的說明何謂In-Memory，一般來說資料庫的資料皆存在磁碟上，來要進行存取時，再透過SQL語法進行處理，並將資料撈取至記憶體中，再進行顯示或處理，在以往如果要加快處理，所以就有許多的軟體或硬體的搭配進而推出RAM-Disk的機制，雖然可以加快處理，但最大的問題是，如果遇到突然斷電時，會造成資料的損失，所以大部份的使用者只敢架構在TempDB的部份。而此次SQL Server整合了此功能(In-Memory)，即可解決此問題又可以達到加速處理的效果。

接下來我們就來建立範例並初步看整體In-Memory的運作情況，下列的範例是來自MSDN官網。

1、建立資料庫


CREATE DATABASE imoltp

ON PRIMARY (name = [imoltp_data], filename = 'c:\data\imoltp_mod1.mdf', size=500MB)

LOG ON (name = [imoltp_log], filename='C:\data\imoltp_log.ldf', size=500MB)

GO



ALTER DATABASE imoltp ADD FILEGROUP [imoltp_mod] CONTAINS MEMORY_OPTIMIZED_DATA

ALTER DATABASE imoltp ADD FILE (name = [imoltp_dir], filename= 'c:\data\imoltp_dir') TO FILEGROUP imoltp_mod;

GO

上述中我們特別建立了一個FileGroup(imoltp_mod)進行儲存，藉以存放Transaction Log與暫存檔，如下圖所示。

2、建立資料表與新的原生編譯的預存程序


USE imoltp;

GO



IF EXISTS (SELECT NAME FROM sys.objects  WHERE NAME = 'xx')

   DROP PROCEDURE xx

GO



IF EXISTS (SELECT NAME FROM sys.objects WHERE NAME = 'sql')

   DROP TABLE sql

GO



IF EXISTS (SELECT NAME FROM sys.objects WHERE NAME = 'hash')

   DROP TABLE hash

GO



IF EXISTS (SELECT NAME FROM sys.objects  WHERE NAME = 'hash1')

   DROP TABLE hash1

GO





CREATE TABLE [dbo].[sql] (

  c1 INT NOT NULL PRIMARY KEY,

  c2 NCHAR(48) NOT NULL

)

GO



CREATE TABLE [dbo].[hash] (

  c1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),

  c2 NCHAR(48) NOT NULL

) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);

GO



CREATE TABLE [dbo].[hash1] (

  c1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),

  c2 NCHAR(48) NOT NULL

) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);

GO



CREATE PROCEDURE xx

  @rowcount INT,

  @c NCHAR(48)

  WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER

  AS

  BEGIN ATOMIC

  WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

  DECLARE @i INT = 1;



  WHILE @i <= @rowcount

  BEGIN;

    INSERT INTO [dbo].[hash1] VALUES (@i, @c);

    SET @i += 1;

  END;

END;

GO

原生編譯的預存程序與傳統的預存程序差別在於原生的預存程序是建立的當下即進行編譯，而傳統的即是在第一次才進行，而且即載入記憶體中，所以可以快速的重覆執行，但原生的預存程序無法進行修改，進行修改時會得到下列的錯誤訊息。

Msg 10794, Level 16, State 25, Procedure xx, Line 52
The operation 'ALTER PROCEDURE' is not supported with natively compiled stored procedures.

3、測試執行差異


SET STATISTICS TIME OFF;

SET NOCOUNT ON;



-- inserts - 1 at a time



DECLARE @starttime datetime2 = sysdatetime();

DECLARE @timems INT;

DECLARE @i INT = 1

DECLARE @rowcount INT = 100000

DECLARE @c NCHAR(48) = N'12345678901234567890123456789012345678'



--- disk-based table and interpreted Transact-SQL



BEGIN TRAN;

  WHILE @I <= @rowcount

  BEGIN

    INSERT INTO [dbo].[sql] VALUES (@i, @c)

    SET @i += 1

  END;

COMMIT;



SET @timems = datediff(ms, @starttime, sysdatetime());

SELECT 'Disk-based table and interpreted Transact-SQL: ' + CAST(@timems AS VARCHAR(10)) + ' ms';



--- Interop Hash

SET @i = 1;

SET @starttime = sysdatetime();



BEGIN TRAN

  WHILE @i <= @rowcount

    BEGIN

      INSERT INTO [dbo].[hash] VALUES (@i, @c);

      SET @i += 1;

    END;

COMMIT;





SET @timems = datediff(ms, @starttime, sysdatetime());

SELECT ' memory-optimized table w/ hash index and interpreted Transact-SQL: ' + CAST(@timems as VARCHAR(10)) + ' ms';



--- Compiled Hash

SET @starttime = sysdatetime();



EXEC xx @rowcount, @c;



SET @timems = datediff(ms, @starttime, sysdatetime());

SELECT 'memory-optimized table w/hash index and native SP:' + CAST(@timems as varchar(10)) + ' ms';

在此次的測試中主要進行三個動作：
a. 新增100000筆資料到傳統的資料庫中
b. 新增100000筆資料到記憶體優化的資料表(memory-optimized table)中。
c. 透過原生編譯的預存程序將100000筆資料新增到記憶體優化的資料表(memory-optimized table)中。

執行結果

從上述的測試中，我們發現在透過記憶體優化的資料表(memory-optimized table)進行處理(第二個測試)，效能其實並沒有明顯的差異，但是在透過原生編譯的預存程序一同配合後，效能即約有3~4成的優化成長。

其實在In-Memory的部份目前限制還滿多的，而且並不是每一種資料庫都適用，根據官方的說法，目前只有下列的幾種情境倒是可以一試，但是從我的測試來看，如果沒有配合原生編譯的預存程序，效能改善 "可能" 有限，所以建議大家可以再多多的評估。

實作案例	實作案例	In-Memory OLTP 的優點
來自多個並行連接的高度資料插入比率。	主要的附加專用存放區。跟不上插入工作負載。	排除競爭。減少記錄。
定期批次插入和更新的讀取效能與比例調整。	高效能讀取作業，特別是在每個伺服器要求都有數個讀取作業要執行時。無法符合相應增加的要求。	在新資料到達時排除競爭。較低延遲的資料擷取。將程式碼執行時間縮到最短。
資料庫伺服器中密集的商務邏輯處理。	插入、更新及刪除工作負載。預存程序內部的大量計算。讀寫競爭。	排除競爭。將程式碼執行時間縮到最短，以減少延遲並提高輸送量。
低度延遲。	要求一般資料庫解決方案無法達成的低度延遲商務交易。	排除競爭。將程式碼執行時間縮到最短。低度延遲的程式碼執行。有效率的資料擷取。
工作階段狀態管理。	經常性插入、更新及點查閱。從許多無狀態的 Web 伺服器大範圍載入。	排除競爭。有效率的資料擷取。使用非持久性的資料表時，選擇性地減少或移除 IO

以上的資訊來自已MSDN：
In-Memory OLTP (記憶體中最佳化)
https://msdn.microsoft.com/zh-tw/library/dn133186(v=sql.120).aspx

*關於限制的部份，我這邊參考下列的網站整理成下列的列表：

參考網站：
SQL Server 2014, Part 3: Limitations of SQL Server 2014 In-Memory OLTP
http://blog.safepeak.com/sql-server-2014-part-3-limitations-sql-server-2014-memory-oltp/

1、Server and Database limitations

REPLICATION is not supported – Officially not supported, although in-memory tables can be defined as Subscribers (but not Publishers)
MIRRORING is not supported
DATABASE SNAPSHOT is not supported
250GB limit per server – Total data in all loaded in-memory tables cannot exceed 250GB
MARS is not supported – Multiple Active Result Sets (MARS) is not supported with natively compiled stored procedures, so your application can’t use MARS connection to talk with the database
Change Data Capture (CDC) is not supported
DTC (distributed transactions) are not supported
RTO (Recovery Time Objective) of your High Availability – the Starting and Recovery time is slower – For every database object (table, stored procedure) SQL Server has to compile and link the corresponding DLL file (that is loaded afterwards into the process space of sqlservr.exe), and this also takes some time. The compilation and linking is also performed when you are restarting your SQL Server, or when you perform a cluster failover.

2、SCHEMA, KEYS, INDEXes, TRIGGERS limitations:

FIXED Schema – You have to design your in-memory tables with knowledge of your data size. Indexes, statistics, and blocks cannot be changed / applied later.
ALTER TABLE for existing disk table is not supported – You can’t alter existing tables to become memory-optimized. You have to create new tables that are memory-optimized.
ALTER TABLE for in-memory table is not supported – You cannot add another column to a memory-optimized table in your production.
Row size limited to 8060 bytes
FOREIGN KEY’s and CHECK CONSTRAINTS are not supported
Datatypes:
Datetimeoffset, Geography, Hierarchyid, Image, Ntext, Text, Sql_variant, Varchar(max), Xml, User data types (UDTs) – not supported
CHAR and VARCHAR – Should be replaced to n(var)char
Various not supported TABLE and COLUMN definitions: IDENTITY, ON (file group or partition), Data type [name], Computed Columns, FILESTREAM, SPARSE, ROWGUIDCOL, UNIQUE
INDEX limitations: no COLUMNSTORE, CLUSTERED INDEX, no LOB datatypes
DDL TRIGGERS and Event Notifications (Server and Database level) – Have to be removed before creating or dropping memory-optimized tables and/or natively compiled procedures
LOGON TRIGGERS do not affect memory-optimized tables
DML TRIGGERS cannot be defined on memory-optimized tables – You can explicitly use stored procedures to insert, update, or delete data to simulate the effect of DML triggers.

3、T-SQL non supported operators:

Classic: OR, NOT, IN, LIKE, BETWEEN, CONTAINS, PERCENT, DISTINCT, NEXT VALUE FOR
UNION’s are not supported
MIN, MAX – limited to non strings
LEFT / RIGHT / FULL OUTER JOIN – Outer joins return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions – not supported (only Inner Joins are supported).
FOR XML, FOR BROWSE
Dynamic SQL (EXECUTE, EXEC) not supported
CURSORs are not supported
Sub-Queries are not supported

4、Transactions and Cross Database queries

BEGIN, COMIT, ROLLBACK are not supported – “Atomic Blocs” are an alternative
Cross Database queries and transactions are limited – You cannot access another database from the same transaction or the same query that also accesses a memory-optimized table. You can create Table Variables, create two transactions: 1) insert the data from the remote table into the variable; 2) Insert the data into the local memory-optimized table from the variable.
MERGE – A useful feature with performing insert, update, or delete operations on a target table based on the results of a join with a source table.

5、Functions, Views and Stored Procedures

User-defined functions cannot be used in natively compiled stored procedures
No In-Memory (“Native”) Functions
VIEWs – Views cannot be accessed from natively compiled stored procedures.
Disk-based tables cannot be accessed from natively compiled stored procedures.

6、T-SQL window functions are not supported at all. Examples:

ROW_NUMBER()
RANK()
OVER (PARTITION BY …) or OVER (PARTITION BY …)

參考資料：
Demonstration: Performance Improvement of In-Memory OLTP
https://msdn.microsoft.com/en-us/library/dn530757.aspx
In-Memory OLTP (記憶體中最佳化)
https://msdn.microsoft.com/zh-tw/library/dn133186(v=sql.120).aspx
SQL Server 2014, Part 3: Limitations of SQL Server 2014 In-Memory OLTP
http://blog.safepeak.com/sql-server-2014-part-3-limitations-sql-server-2014-memory-oltp/
Transact-SQL Constructs Not Supported by In-Memory OLTP
https://msdn.microsoft.com/en-us/library/dn246937.aspx

關鍵字：In-Memory、記憶體資料表、Memory-Optimized Table、Natively Compiled Stored Procedures

CaryHsu - 學無止盡

2015年6月28日星期日