SQLSERVER2014的内存优化表
SQL Server 2014中的內存引擎(代號為Hekaton)將OLTP提升到了新的高度。
現在，存儲引擎已整合進當前的數據庫管理系統，而使用先進內存技術來支持大規模OLTP工作負載。
就算如此，要利用此新功能，數據庫必須包含“內存優化”文件組和表
即所配置的文件組和表使用Hekaton技術。
幸運的是，SQL Server 2014使這一過程變得非常簡單直接。
要說明其工作原理，我們來創建一個名為TestHekaton的數據庫，然后添加一個內存優化文件組到此數據庫
?
測試環境：Microsoft Azure 大陸版 虛擬機
4核 ，7G內存，Windows2012R2
SQLSERVER2014企業版
實驗
第一個實驗：內存表的簡單使用
?
步驟1：創建數據庫和MEMORY_OPTIMIZED_DATA文件組
USE master;GOCREATE DATABASE TestHekaton;GOALTER DATABASE TestHekatonADD FILEGROUP HekatonFG CONTAINS MEMORY_OPTIMIZED_DATA;GO注意ALTER DATABASE語句中的ADD FILEGROUP 語句包含文件組的名稱(HekatonFG)和關鍵字CONTAINS MEMORY_OPTIMIZED_DATA
它會指導SQL Server去創建支持內存OLTP引擎所必需的文件組類型。
注意：每個數據庫只能有一個MEMORY_OPTIMIZED_DATA文件組！！
要確認此文件組已經創建，可以訪問SSMS中數據庫屬性的Filegroups 界面，如下圖所示。
?
步驟2：
添加一個數據文件到文件組，可以通過ALTER DATABASE語句來實現。
添加一個新數據文件到HekatonFG文件組：
ALTER DATABASE TestHekatonADD FILE (NAME = 'HekatonFile',FILENAME ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\HekatonFile' )TO FILEGROUP [HekatonFG]; GO注意：在ADD FILE 語句中，我們只為文件路徑和文件名提供了一個友好的名稱。
并且，在TO FILEGROUP 語句中，為新文件組指定名稱。
然后可以去往數據庫屬性的 Files 界面來查看剛剛添加的文件，如圖所示。
?
?
步驟3：
在為數據庫設置了必需的文件組和文件之后，就可以創建自己的內存優化表了。
當在定義表的時候，會指定其“持久性”。
一個內存優化表可以是持久的或非持久的。
（1）對于一個持久表是將數據存儲在內存中，而且也保存在內存優化文件組中。
（2）對于一個非持久表，數據是僅存儲在內存中的，所以，如果系統崩潰或重啟，數據就會丟失。
?
在SQL Server 2014中默認用的是持久表，接下來我們來深入了解一下。
當定義一個持久內存優化表的時候，你還必須定義一個基于非聚集哈希索引的主鍵。
在一個哈希索引中，數據是通過一個內存散列表進行訪問的，而非固定大小頁。
哈希索引是在內存優化表中唯一支持的索引類型。
除了在表定義中定義主鍵外，還必須將表配置為內存優化的，如下CREATE TABLE 語句所示：
USE TestHekaton; GOCREATE TABLE Reseller([ResellerID] INT NOT NULLPRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),[ResellerName] NVARCHAR(50) NOT NULL ,[ResellerType] NVARCHAR(20) NOT NULL) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);INSERT INTO Reseller VALUES ( 1, 'A Bike Store', 'Value Added Reseller' );?
ResellerID 字段定義包含了定義為非聚集哈希的主鍵。
注意，必須包含一個WITH 語句來指定BUCKET_COUNT 的設置，它表明了在哈希索引中應該創建的bucket數量。
(每個bucket是一個槽，可以用來存放一組鍵值對。)
微軟建議bucket的數量應是一到兩倍于你所期望的表所要包含的唯一索引鍵的數量。
?
此表定義以第二個WITH 語句結束。
這里你指定MEMORY_OPTIMIZED 選項為ON 以及DURABILITY 選項為SCHEMA_AND_DATA，此選項是針對持久表的。
接著在表中插入一條記錄，這樣就可以進行測試了。
數據已經插入到表中
?
?
這就是創建一個內存優化表的全部步驟，其他的一切都會發生在幕后。
但是，要記住，SQL Server 2014對這些表有著很多限制。例如，它們不支持外鍵或約束檢查（感覺類似于MYSQL的memory存儲引擎），
它們也不支持IDENTITY 字段或DML觸發器。最為重要的是，內存耗盡會導致寫活動停止。
?
步驟4：
另一方面，內存優化表支持本地編譯存儲過程，只要那些存儲過程只引用內存優化表。
在這種情況下，存儲過程可以轉化為本地代碼，這樣會執行更快且要比典型存儲過程需要更少的內存。
除了只引用內存優化表，一個本地編譯存儲過程必須是模式綁定的并運行在一個特定執行內容內。
另外，每個本地編譯存儲過程必須完全由一個原子塊組成。
?
下面的CREATE PROCEDURE 語句定義了一個本地編譯存儲過程，它從前例中所創建的Reseller表中檢索數據
CREATE PROCEDURE GetResellerType ( @id INT )WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER ASBEGINATOMIC WITH(TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')SELECT ResellerName ,ResellerTypeFROM dbo.ResellerWHERE ResellerID = @idEND; GO?
在定義了參數之后，包含一個WITH 語句來指定NATIVE_COMPILATION 選項。
注意：此語句還包含SCHEMABINDING 選項和EXECUTE AS 選項，以及指定了OWNER 作為執行環境。
而WITH 語句負責實現本地編譯存儲過程的三大需求。
要解決原子塊需求，可以在BEGIN 關鍵字后指定ATOMIC ，之后是另一個包含有事務隔離級別和語言的WITH 語句。
對于訪問內存優化表的事務，可以使用SNAPSHOT，REPEATABLEREAD 或SERIALIZABLE 作為隔離級。
而且，對于此語言必須使用一個可用的語言或語言別名。
這是在定義存儲過程時所需要包含的全部內容。一旦創建，就可以通過執行EXECUTE 語句來對其加以測試，如下例中所示：
EXEC GetResellerType 1;?
此語句會返回經銷商的姓名和類型，在本例中分別是ABike Store和Value Added Reseller。
?
第一個實驗：內存表的數據查詢速度比較
聚集索引表和內存優化表的比較
建表語句
USE TestHekaton; GO--內存優化表 CREATE TABLE testmemory1([ID] FLOAT NOT NULLPRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),[Name] NVARCHAR(50) NOT NULL ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);?
USE TestHekaton; GO--聚集索引表 CREATE TABLE testmemory2([ID] FLOAT NOT NULL PRIMARY KEY,[Name] NVARCHAR(50) NOT NULL )?
---------------------------------------------------------------
插入性能比較
內存優化表
SET STATISTICS IO ON SET STATISTICS TIME ON INSERT into testmemory1([id],[name]) SELECT [id] ,[name] from sysobjects SET STATISTICS IO OFF SET STATISTICS TIME OFF Table 'sysschobjs'. Scan count 1, logical reads 33, 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 = 20 ms. (90 row(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.?
聚集索引表
SET STATISTICS IO ON SET STATISTICS TIME ON INSERT into testmemory2([id],[name]) SELECT [id] ,[name] from sysobjects SET STATISTICS IO OFF SET 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 33, 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 = 10 ms. (90 row(s) affected)SQL Server Execution Times:CPU time = 0 ms, elapsed time = 0 ms.?
-------------------------------------------------------------------------------
查詢性能比較
內存優化表
SET STATISTICS IO ON SET STATISTICS TIME ON SELECT * FROM testmemory1 ORDER BY [ID] DESC SET STATISTICS IO ON SET STATISTICS 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. (90 row(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.?
?
聚集索引表
SET STATISTICS IO ON SET STATISTICS TIME ON SELECT * FROM testmemory2 ORDER BY [ID] DESC SET STATISTICS IO ON SET STATISTICS TIME ON (91 row(s) affected) 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-ahead 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.?
可以看到內存優化表讀寫數據（insert 、select）的時候都看不到IO讀寫
?
補充測試：
?
我們先刪除剛才插入的數據，內存優化表是不支持truncate table的，只能用delete from 表
只能夠delete
插入測試
內存優化表
聚集索引表
?-------------------------------------------------------------------------------------------------
查詢測試
內存優化表
?
聚集索引表
我們看一下事務日志
CHECKPOINT GOSELECT Context , Operation, AllocUnitName FROM 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 |
?
總結
內存優化表也會寫事務日志的，在讀寫操作的時候發現內存優化表沒有I/O次數，應該是數據都已經在內存里了
?
更多詳細資料可以參考：
SQL Server 2014 新特性——內存數據庫
SQL Server 2014新特性：分區索引和內存優化表
MSDN:內存優化表
轉載于:https://www.cnblogs.com/mschen/p/4488703.html
總結
以上是生活随笔為你收集整理的SQLSERVER2014的内存优化表的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 使用 core dump 查找程序遇到严
- 下一篇： 关于XML文档操作类