前言

記憶體表是比較少會拿來用到的表，一般公司用 ERP 等等通常都不會特別去用這個東西，通常都用在需要非常高速的環境中才會使用。
所以這不會是初學者會理解的範疇，以下我盡量寫成讓第一次接觸的人都會看得懂，所以有些地方會寫得很簡陋，實際上更深層的細節則以補充說明。

前面有提到過 In-Memory OLTP，這是一個 SQL Server 功能，透過將 table 的所有資料，存在記憶體中，提供顯著的效能改善。

前面只有說明過 In-Memory 的檔案結構，這邊會詳細的說明，到底什麼是 In-Memory Table。

1. 因為記憶體是無法永久儲存的關係，所以在前面 filegroup 的時候已經有說明 SQL Server 是如何把記憶體表，永久的儲存下來。
2. 而這些資料表的磁碟版本，本身其實不是已結構化格式儲存在資料庫內，它存在資料庫引擎之外並且使用 filestream 為基礎的技術。
3. 記憶體表的 checkpoint 會更頻繁的發生，當交易紀錄自上一次自動 checkpoint 發生後成長了 512 MB，就會自動執行一次 checkpoint。
4. 記憶體表的交易紀錄 I/O 競爭會降低，因為記錄的資料量很少。
5. 只有 table 變更會被記錄，索引變更不紀錄。
6. 用原生編譯的 SP 來存取資料，而不是傳統的直譯程式，降低 CPU 負擔。

Durability

建立 memory-optimized table 時，可以指定 durability setting 為 SCHEMA_AND_DATA 或 SCHEMA_ONLY。

如果選擇 SCHEMA_AND_DATA，資料表中的所有資料都會被持久化到磁碟，而且 transaction 會被記錄。

如果選擇 SCHEMA_ONLY，資料不會被持久化，transaction 也不會被記錄。這表示 SQL Server service 重新啟動之後，資料表的結構仍然會保留完整，但資料表內不會有任何資料。

這對於暫時性處理流程很有用，例如在 ETL load 過程中用來暫存資料。

建立 & 管理 memory-optimized table

雖然 memory-optimized table 速度快很多，但是他其實有諸多限制。

memory-optimized table 應該只在例外情況下使用。

1. 建立之前，一定要先有 memory-optimized filegroup，再這裡有討論過。
   https://ithelp.ithome.com.tw/articles/10401352

2. 建立 memory-optimized table 時，一樣是使用 CREATE TABLE 的語法，差別在於必須指定 WITH clause，用來說這個是記憶體表。同時 WITH clause 也會用來指定我們需要的 durability level。

3. Memory-optimized table 也必須包含一個 index。支援的 index 如下 :

   • Nonclustered hash index
   • Nonclustered index

4. Hash index 會被組織成 bucket。建立 hash index 時，必須使用 BUCKET_COUNT parameter 指定 bucket count。理想情況下，bucket count 應該是 index key 中 distinct value 數量的兩倍。

   如果是 unique index 的話，如果是一般會建議20~100倍。

5. 但你不一定永遠知道實際會有多少 distinct value；在這種情況下，你可能會想要大幅增加 BUCKET_COUNT。取捨是：bucket 越多，index 消耗的 memory 就越多。一旦 table 建立完成，index 的大小就會固定，而且不能修改 table 或它的 index。

因為只有在這裡才會用到 hash index 所以講解一下

在 SQL Server 中，有很多地方會用到 hash，但是 hash index 只有在這裡會用到。
SQL Server 會先用一個 hash function 算出 key 應該去哪個 bucket；

bucket 想像成是好幾個小格子，準備來放東西用的。

例如說假設現在有一個記憶體表，把 OrderNumber 這個欄位做 hash index，然後正常使用中插入一筆資料，是 OrderNumber = 1001，那 SQL Server 就會拿這個 1001 去跑 hash function。

然後 function 跑出來，假設結果是 5，那意思是應該要去 bucket 5 的這個空間裡，放入指向 OrderNumber = 1001 的 offset。

如果今天，一個查詢要查 WHERE OrderNumber = 1001，SQL Server 就會又把這個 1001 拿去算 hash，因為是一樣的 hash function、一樣的參數，所以這次算出來也會是 5。

然後 SQL Server 就會去找 bucket 5，然後沿著 bucket 5 裡面的 offset 就可以找到 OrderNumber = 1001 真正存在哪邊，最後 SQL Server 會再比對最後一次 key 確定正確，然後才回傳資料。

所以，Hash index 在等值查詢中，可以比 B-tree 更快速的定位， B-tree 是一層一層的找，Hash index 是直接算出然後定位。這也是 Hash index 最大的優點，在 = 的這種查詢中，非常快。

至於 Hash index 的缺點 :

1. 不適合範圍查詢，例如 WHERE OrderNumber BETWEEN 1000 AND 2000，因為 hash index 是不排序的，所以 range scan 會很差。
2. BUCKET_COUNT 要先估；這是上面的第五點，因為今天不知道到底有多少不同的值，所以如果 BUCKET 估的太少，會發生 hash collision；就是說有100萬個 key，但是只有 1 萬個 bucket，那就會發生一個 bucket 裡面放 100 個 key 的狀況，導致就算 SQL Server 算出來要去哪一個 bucket 找 offset，找到 bucket 之後還要慢慢比對 key 最後才能找到正確的 offset。而一次設太大的 bucket 也不好，會佔用太多 RAM。
3. 不適合在重複值很多的欄位。假設用性別欄為來做 hash index，就算你開很多 bucket，也沒有意義，因為大量 row 只會集中到少數幾個 key 上。key 總共就兩個，會讓 bucket 這個小格子塞爆。

實作

接下來會

1. 建立一個名為 OrdersMem 的 memory-optimized table
2. 並設定為 full durability，然後將資料填入其中。
3. 它會在 ID column 上建立一個 nonclustered hash index，bucket count 設為 2,000,000，因為我們會插入 1,000,000 rows。

實作效能測試

接下來會建立一個新的實體 Table，叫 OrdersDisc，然後填入一樣的資料，來比較效能。

測試環境 : CPU AMD R5 5600X、16G RAM、HDD

一、基本測試

對每張 Table 執行 SELECT *

從這個結果看消耗CPU、總耗時卻不相上下，是因為現在做的是 SELECT *

二、聚合測試

對兩張 table 執行 COUNT(*) query。

當換成這次實驗的時候就會看到明顯差距，CPU 效能差了 3倍、總時間更是相差將近 7 倍。

由此可知記憶體表正確使用下的效能差距。

三、加上 WHERE

由於 memory-optimized table 被 scan，但 disk-based table 上的 clustered index 能夠執行 index seek，因此 disk-based table 的表現大約比 memory-optimized table 快 10 倍。

這也是前面說的，Hash index 非常不適合用範圍搜尋，即使在記憶體內，依舊是會比硬碟上的 clustered index 慢。

Natively Compiled Objects 原生編譯物件

In-Memory OLTP 為 memory-optimized table 和 stored procedure 引入了 native compilation（原生編譯），可以顯著提升效能。

原生編譯的 Table

其實在建立 memory-optimized table 時，SQL Server 會使用 native code（原生程式碼） 將該 table 編譯成 DLL，並將這個 DLL 載入到 memory 中。

基於安全性原因，每次 SQL Server service 啟動時，這些檔案都會根據 database metadata 重新編譯。這表示如果這些 DLL 被竄改，所做的變更不會被保留下來。此外，這些檔案會與 SQL Server process 連結，以防止它們被修改。

當這些 DLL 不再需要時，SQL Server 會自動移除它們。當 table 被 drop，並且之後發出 CHECKPOINT 之後，這些 DLL 會從 memory 中卸載，並且在 instance 重新啟動、database 被設為 offline，或 database 被 drop 時，從 file system 中實體刪除。

原生編譯的 Stored Procedures

除了原生編譯的 memory-optimized table 之外，SQL Server 也支援 原生編譯的 stored procedure。如前面所述，這些 procedure 可以降低 CPU overhead，並且相較於傳統 interpreted stored procedure 提供效能優勢，因為它們在執行期間需要較少的 CPU cycles。

建立原生編譯 stored procedure 的語法，和建立 interpreted stored procedure 的語法類似，但有一些細微差異。

1. 首先，procedure 必須以 BEGIN ATOMIC clause 開始。procedure 的主體必須精確包含一個 BEGIN ATOMIC clause。這個 block 內的 transaction 會在 block 結束時 commit。這個 block 必須用 END statement 結束。

2. 當開始 atomic block 時，必須指定要使用的隔離等級以及 language。

3. WITH clause 包含了 NATIVE_COMPILATION、SCHEMABINDING 和 EXECUTE AS options。

4. 因為原生編譯的 stored procedure 必須指定 SCHEMABINDING。這可以防止它所依賴的 object 被修改。

5. 必須指定 EXECUTE AS clause，因為 EXECUTE AS 的預設值是 caller，但這不是 native compilation 支援的選項。如果打算將現有的 interpreted SQL migration 到原生編譯的 stored procedure，這一點會有影響

6. 這也代表在 code migration 之前，你應該重新評估你的 security policy。這個 option 本身算是相當直觀。
   
   在規劃將程式碼 migration 到原生編譯 stored procedure 時，這類 stored procedure 有許多限制，而且他們將無法使用某些功能

7. table variable

8. CTE（common table expression）

9. subquery

10. WHERE clause 中的 OR operator

11. 以及 UNION。

和 memory-optimized table 一樣，原生編譯的 stored procedure 也會產生 DLL。

下一篇 : 索引