使用SQL Server記憶體優化表 In-Memory OLTP

如果你的系統有高併發的要求，可以嘗試使用SQL Server記憶體優化表來提升你的系統性能。你甚至可以把它當作Redis來使用。

要使用記憶體優化表，首先要在現在資料庫中添加一個支持記憶體優化的文件組。

Memory Optimized File Group

可以使用下列腳本來向現有資料庫添加記憶體優化文件組：

ALTER DATABASE SomeDatabase
ADD FILEGROUP Memory
CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE SomeDatabase
ADD FILE
    (
        NAME = 'SomeDatabase_InMemory',
        FILENAME = 'E:\Database\SomeDatabase_InMemory'
    )
TO FILEGROUP Memory;

ALTER DATABASE SomeDatabase
SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;
GO

創建記憶體優化表，使用MEMORY_OPTIMIZED = ON來啟用記憶體優化：

CREATE TABLE [dbo].[CachedData]
(
    [Key] [VARCHAR](900) NOT NULL,
    [Data] [VARBINARY](MAX) NOT NULL,
    [Expiry] [DATETIME] NOT NULL,
    PRIMARY KEY NONCLUSTERED ([Key] ASC)
)
WITH (MEMORY_OPTIMIZED = ON);

這樣記憶體優化表就可以使用了，另外如果你的數據不需要進行持久化存儲，可以使用DURABILITY = SCHEMA_ONLY，來得到更好的性能：

• Defining Durability for Memory-Optimized Objects

優化進階

在使用記憶體優化表的過程中發現，當併發大或資料庫壓力大時，訪問記憶體優化表會提示記憶體不足的情況，但實際記憶體是夠的。這其實是一個誤報，可以查看微軟官方文檔瞭解更多詳情：

• Resolve Out Of Memory issues

簡單的說，要解決這個問題就是需要打開Resource Governor，Resource Governor在預設情況下是關閉的。

為了更好的管理資源的使用情況，我們需要給緩存的這個資料庫創建一個獨立的Resource Pool，與系統預設的Resource Pool獨立開。

Create Resource Pool

創建一個新的Resource Pool：

-- Disable resource governor
ALTER RESOURCE GOVERNOR DISABLE;
GO
CREATE RESOURCE POOL CacheDbPool
WITH
(
    MAX_CPU_PERCENT = 50,
    MAX_MEMORY_PERCENT = 30
);
GO
-- Reconfigure resource governor
-- Reconfigure enables resource governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

詳細配製參數可參閱微軟官方文檔：

• Resource Governor Resource Pool

Bind Resource Pool

將新建的Resource Pool和資料庫進行關聯：

-- Bind Database with resource pool
EXEC sys.sp_xtp_bind_db_resource_pool @database_name = N'SomeDatabase',
                                      @pool_name = N'CacheDbPool';

-- Take database offline and then bring it back online to begin using resource pool.
GO
USE [master];
GO
ALTER DATABASE [SomeDatabase] SET OFFLINE;
GO
ALTER DATABASE [SomeDatabase] SET ONLINE;
GO

這樣記憶體優化就設置完成了。

如果需要刪除Resource Pool，需要先解綁資料庫：

-- Unbind resource pool and drop it.
EXEC sys.sp_xtp_unbind_db_resource_pool @database_name = N'SomeDatabase';
DROP RESOURCE POOL CacheDbPool;

原文地址：http://www.zkea.net/codesnippet/detail/sql-server-in-memory-oltp.html