SQLServer 2014 記憶體最佳化表

標籤：屬性   lte   記錄   恢複   資料庫   varchar   object_id   new   entity   

記憶體最佳化表是 SQLServer 2014 的新功能，它是可以將表放在記憶體中，這會明顯提升DML效能。
關於記憶體最佳化表，更多可參考兩位大俠的文章：SQL Server 2014新特性探秘(1)-記憶體資料庫 試試SQLSERVER2014的記憶體最佳化表

建立記憶體最佳化表也很簡單，以下測試：

添加記憶體最佳化資料庫檔案組：
[sql] view plain copy 在CODE上查看代碼片派生到My Code片
USE [master]
GO
-- 在當前資料庫中添加記憶體最佳化資料庫檔案組（每個資料庫僅1個檔案組）
ALTER DATABASE [Demo] ADD FILEGROUP [FG_MemoryTable] CONTAINS MEMORY_OPTIMIZED_DATA
GO

-- 建立新的檔案添加到該檔案組中
ALTER DATABASE [Demo]
ADD FILE
(
NAME = ‘Demo_MemoryTable‘,
FILENAME =‘F:\VMWareSystem\database\Demo‘
)
TO FILEGROUP [FG_MemoryTable];
GO
這裡添加的資料庫檔案，指定的是一個路徑。路徑檔案如下：
（忘了。。）

建立記憶體最佳化表：
[sql] view plain copy 在CODE上查看代碼片派生到My Code片
USE [Demo];
GO
--記憶體最佳化表
CREATE TABLE [MemoryTable]
(
[Guid] UNIQUEIDENTIFIER NOT NULL
CONSTRAINT IX_MemoryTable PRIMARY KEY NONCLUSTERED
HASH WITH (BUCKET_COUNT = 1024),
[Name] NVARCHAR(50) NOT NULL ,
[value] INT NULL
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);/*在記憶體和磁碟中*/
GO
--普通表
CREATE TABLE [ClusterTable]
(
[Guid] UNIQUEIDENTIFIER NOT NULL
CONSTRAINT IX_ClusterTable PRIMARY KEY NONCLUSTERED ,
[Name] NVARCHAR(50) NOT NULL ,
[value] INT NULL
)
GO

建立記憶體最佳化表當前只能用指令碼建立。WITH 語句來指定BUCKET_COUNT 的設定，它表明了在雜湊索引中應該建立的bucket數量。(每個bucket是一個槽，可以用來存放一組索引值對。)微軟建議bucket的數量為表唯一列數量的一到兩倍。
MEMORY_OPTIMIZED = ON ：為啟用記憶體最佳化表；
DURABILITY = SCHEMA_AND_DATA ：表示資料存放區在記憶體中和檔案組中。
（DURABILITY = SCHEMA_ONLY 表示資料只儲存在記憶體中）

 

測試IO情況：
[sql] view plain copy 在CODE上查看代碼片派生到My Code片
-- 插入1萬行資料
SET NOCOUNT ON
INSERT INTO [MemoryTable]
SELECT NEWID(),‘hello.kk‘+CONVERT(VARCHAR(10),ABS(CHECKSUM(NEWID()))%1000),ABS(CHECKSUM(NEWID()))%1000
GO 10000
SET NOCOUNT OFF

INSERT INTO [ClusterTable] SELECT * FROM [MemoryTable]
GO

-- 查看IO情況
SET STATISTICS IO ON
SELECT * FROM [MemoryTable] WITH(SNAPSHOT)
SELECT * FROM [ClusterTable]
SET STATISTICS IO OFF
GO

(10000 行受影響)
(1 行受影響)

(10000 行受影響)
表 ‘ClusterTable‘。掃描計數 1，邏輯讀取 68 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
(1 行受影響)

IO 結果，記憶體表 [MemoryTable] 沒有跟蹤到IO ，物理表 [ClusterTable] 可以看到進行了讀取。
這裡也看不出資料是否在記憶體中，現在再測試一次。到底資料是不是在記憶體呢？
現在測試：建立記憶體最佳化表，注意 DURABILITY = SCHEMA_ONLY ，資料只保留在記憶體中。
[sql] view plain copy 在CODE上查看代碼片派生到My Code片
-- 建立另一張測試表
CREATE TABLE [MemoryTest]
(
[Guid] UNIQUEIDENTIFIER NOT NULL CONSTRAINT IX_MemoryTest PRIMARY KEY NONCLUSTERED,
[Name] NVARCHAR(50) NOT NULL ,
[value] INT NULL
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);/*只在記憶體*/
GO

-- 插入10行資料
INSERT INTO [MemoryTest] SELECT NEWID(),‘hello.kk‘,100
GO 10

-- 查看，有資料！
SELECT * FROM [MemoryTest]


-- 注意：現在重啟 sqlserver 服務！


--再查看，表中沒有資料了！！
SELECT * FROM [MemoryTest]

--DROP TABLE [MemoryTest]

重啟sqlserver 服務後資料查詢不到了！因為資料只保留在記憶體中，任何導致sqlserver服務停止都會導致資料丟失。也就是說明了資料是在記憶體中的！ 執行 DBCC DROPCLEANBUFFERS 對記憶體最佳化表資料無影響。

記憶體最佳化表諸多限制：
記憶體表不支援 修改bucket_count，truncate table，DML觸發器，IDENTITY初始值或增量不為1的情況。
記憶體表不支援ALTER 操作(約束檢查、外鍵、添加或刪除索引、添加或刪除列、更改列屬性)
以下幾種情況在記憶體最佳化表是不支援的：
[sql] view plain copy 在CODE上查看代碼片派生到My Code片
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

訊息 10794，層級 16，狀態 92，第 113 行
此 語句 “TRUNCATE TABLE”未受到 記憶體最佳化表 的支援。
訊息 10794，層級 16，狀態 77，過程 TR_MemoryTable，第 116 行
此 操作 “CREATE TRIGGER”未受到 記憶體最佳化表 的支援。
訊息 12339，層級 16，狀態 21，第 125 行
記憶體最佳化表 不支援種子以及增量值使用 1 以外的數值。
訊息 10794，層級 16，狀態 14，第 132 行
此 操作 “ALTER TABLE”未受到 記憶體最佳化表 的支援。
訊息 10794，層級 16，狀態 14，第 134 行
此 操作 “ALTER TABLE”未受到 記憶體最佳化表 的支援。
訊息 10794，層級 16，狀態 14，第 136 行
此 操作 “ALTER TABLE”未受到 記憶體最佳化表 的支援。
訊息 10794，層級 16，狀態 12，第 138 行
此 操作 “CREATE INDEX”未受到 記憶體最佳化表 的支援。
訊息 10794，層級 16，狀態 14，第 140 行
此 操作 “ALTER TABLE”未受到 記憶體最佳化表 的支援。

使用記憶體最佳化表，還有一個重要的概念：原生編譯
原生編譯可提高訪問資料的速度和執行查詢的效率。在伺服器重啟過程中，將重新編譯記憶體最佳化表。為了加快資料庫恢複速度，原生編譯的預存程序不會在伺服器重啟過程中重新編譯，而是在首次執行時編譯。如果編譯失敗或中斷，則某些產生的檔案將不會被刪除。出於支援性目的這些檔案被有意保留，並且在刪除資料庫時會被刪除。
[sql] view plain copy 在CODE上查看代碼片派生到My Code片
-- 查看記憶體最佳化表編譯的 DLL
select description,name FROM sys.dm_os_loaded_modules
where name like ‘%xtp_t_‘ + cast(db_id() as varchar(10)) + ‘_‘ + cast(object_id(‘dbo.MemoryTable‘) as varchar(10)) + ‘.dll‘
go

-- 更多 DLL
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

原生編譯預存程序: 預存程序的內部參數或設定都是必要的！在建立時編譯，而解釋型預存程序在首次執行時編譯。
（更多參考： 原生編譯預存程序）
[sql] view plain copy 在CODE上查看代碼片派生到My Code片
CREATE PROCEDURE DBO.NATIVE_SP
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT,LANGUAGE=N‘簡體中文‘)
SELECT TOP 5 [Guid],[Name],[value]
FROM DBO.MemoryTable ORDER BY NEWID()
END
GO

EXEC DBO.NATIVE_SP
GO

-- 查看預存程序編譯的 DLL
select name, description FROM sys.dm_os_loaded_modules
where name like ‘%xtp_p_‘ + cast(db_id() as varchar(10)) + ‘_‘ + cast(object_id(‘dbo.NATIVE_SP‘) as varchar(10)) + ‘.dll‘
go

參數說明如下：
NATIVE_COMPILATION ： 表示本地編譯
SCHEMABINDING ：原生編譯預存程序必須綁定到其引用的對象的架構
EXECUTE AS OWNER ：原生編譯的預存程序不支援 EXECUTE AS CALLER，這是預設執行內容。因此，需要指定執行內容。支援選項 EXECUTE AS OWNER、EXECUTE ASuser 和 EXECUTE AS SELF。
BEGIN ATOMIC ：原生編譯的預存程序本文必須由恰好一個原子塊構成。原子塊確儲存儲過程的原子執行。如果在活動事務的上下文外調用該過程，它將開始一個新事務，這個新事務在原子塊的末尾提交。（更多參考 ：原子塊）
TRANSACTION ISOLATION LEVEL ：必須設定交易隔離等級 SNAPSHOT、REPEATABLEREAD 和 SERIALIZABLE。
LANGUAGE ：預存程序的語言必須設定為可用語言或語言別名之一。
[sql] view plain copy 在CODE上查看代碼片派生到My Code片
-- 查看預存程序編譯的 DLL
select name, description FROM sys.dm_os_loaded_modules
where name like ‘%xtp_p_‘ + cast(db_id() as varchar(10)) + ‘_‘ + cast(object_id(‘dbo.NATIVE_SP‘) as varchar(10)) + ‘.dll‘
go

更改預存程序則出錯：
訊息 10794，層級 16，狀態 25，過程 NATIVE_SP，第 168 行
此 操作 “ALTER PROCEDURE”未受到 原生編譯的預存程序 的支援。

交易隔離等級為 READ COMMITTED 則出錯：
訊息 10794，層級 16，狀態 81，過程 NATIVE_SP，第 171 行
此 交易隔離等級 “READ COMMITTED”未受到 原生編譯的預存程序 的支援。

記憶體最佳化表交易隔離等級 ：
訪問記憶體最佳化表的事務支援的隔離等級:SNAPSHOT,REPEATABLE READ,SERIALIZABLE,READ COMMITTED 。
記憶體最佳化表不使用鎖。可以使用更高的隔離等級（如 REPEATABLE READ 和 SERIALIZABLE）聲明所需的保障。
不支援鎖定提示。改為通過交易隔離等級聲明所需的保障。
（支援 NOLOCK 是因為 SQL Server 不對記憶體最佳化表使用鎖。請注意，與基於磁碟的表不同，NOLOCK 對於記憶體最佳化表並不暗示 READ UNCOMMITTED 行為。）

對於自動認可事務，隔離等級 READ COMMITTED 會隱式映射到記憶體最佳化表的 SNAPSHOT。顯式或隱式使用者事務不支援 READ COMMITTED。
因此，如果 TRANSACTION ISOLATION LEVEL 會話設定設為 READ COMMITTED，則在訪問記憶體最佳化表時無需通過表提示指定隔離等級。

以下這樣使用錯誤!
[sql] view plain copy 在CODE上查看代碼片派生到My Code片
--執行出錯！
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT TOP 5 [Guid],[Name],[value] FROM DBO.MemoryTable ORDER BY NEWID()
ROLLBACK TRAN
GO

訊息 41368，層級 16，狀態 0，第 198 行
僅對自動認可事務支援使用 READ COMMITTED 隔離等級訪問記憶體最佳化的表。它不適用於顯式或隱含交易。請使用表提示(如 WITH (SNAPSHOT))為記憶體最佳化的表提供支援的隔離等級。

正確執行方法！以下3種！
[sql] view plain copy 在CODE上查看代碼片派生到My Code片
--正確執行方法！以下3種！
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

事務隔離測試 ：

開啟會話1執行如下語句，使更新堵塞：
[sql] view plain copy 在CODE上查看代碼片派生到My Code片
-- 會話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

開啟會話2，查詢或更新同一條語句：
[sql] view plain copy 在CODE上查看代碼片派生到My Code片
-- 會話2
-- 可重複讀，讀取的是[會話1]之前的快照
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM [MemoryTable]WITH(SNAPSHOT) WHERE Guid=‘2D153C8A-498D-4619-A58F-491CEAD2A031‘

-- 更改則出錯
UPDATE [MemoryTable]WITH(SNAPSHOT) SET Name = ‘KK‘ WHERE Guid=‘2D153C8A-498D-4619-A58F-491CEAD2A031‘
/*
訊息 41302，層級 16，狀態 110，第 3 行
當前事務嘗試更新自該事務啟動後已更新的記錄。該事務已中止。
語句已終止。
*/

發現查詢並沒有堵塞，也就相當於可重複讀了！

有一些與訪問記憶體最佳化表的事務有關的錯誤情形:
41302.當前事務嘗試更新自事務啟動以來已更新過的記錄。(如果兩個事務嘗試更新同一行，則會發生寫/寫衝突)
41305.因某個可重複讀取驗證失敗，當前事務無法提交。
41325.因某個序列化讀取驗證失敗，當前事務無法提交。
41301.當前事務所依賴的前一事務已終止，當前事務無法再提交。
[sql] view plain copy 在CODE上查看代碼片派生到My Code片
-- 記憶體最佳化表允許使用更高隔離等級 REPEATABLE READ 和 SERIALIZABLE 來實現開放式並行存取控制。
-- 更改交易隔離等級為 REPEATABLE READ 或者SERIALIZABLE，然後必須在快照隔離（WITH(SNAPSHOT)）下執行語句 。
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

其他說明：
記憶體最佳化表不支援跨資料庫事務。訪問記憶體最佳化表的每個事務都無法訪問多個資料庫（對 tempdb 進行的讀/寫訪問以及對系統主要資料庫進行的唯讀訪問除外）。
記憶體最佳化表不支援分散式交易。以 BEGIN DISTRIBUTED TRANSACTION 開頭的分散式交易無法訪問記憶體最佳化表。
記憶體最佳化表不支援鎖定。記憶體最佳化表不支援通過鎖提示（如 TABLOCK、XLOCK、ROWLOCK）實現的顯式鎖。

更新統計資料：
預設情況下，不更新針對記憶體最佳化表的統計資訊。對於基於磁碟的表，如果自上次 sp_updatestats (Transact-SQL) 以來已修改表，sp_updatestats (Transact-SQL) 僅更新更新統計資料。對於記憶體最佳化的表，sp_updatestats (Transact-SQL) 始終產生更新的統計資訊。
[sql] view plain copy 在CODE上查看代碼片派生到My Code片
--更新單個記憶體最佳化表 (myschema. Mytable) 的統計資訊：
UPDATE STATISTICS [DBO].[MemoryTable] WITH FULLSCAN, NORECOMPUTE


--更新當前資料庫中所有記憶體最佳化表的統計資訊：
DECLARE @sql NVARCHAR(MAX) = N‘‘

SELECT @sql += N‘UPDATE STATISTICS ‘ + quotename(schema_name(schema_id)) + N‘.‘ + quotename(name) + N‘ WITH FULLSCAN, NORECOMPUTE ‘
FROM sys.tables WHERE is_memory_optimized=1

EXEC sp_executesql @sql

--記憶體最佳化表統計資訊上次更新時間：
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

SQLServer 2014 記憶體最佳化表