SQL2016 In-Memory OLTP，現在可以讓我們很方便將資料存放至Memory table。
分散式Cache system我常見就是使用Redis，Redis效能雖然無庸置疑，
但我們必須付出額外維護和監控成本，如果你的系統架構都是MS導向，
那麼或許直接透過SQL2016 In-Memory OLTP來解決維護和Server成本問題，
且你可以使用熟悉TSQL來查詢現在有多少Cache類型、數量、資料..等，
無須額外使用Redis Desktop Manager並學習Redis知識和commands，
我看了MS分享In-Memory OLTP案例真讓人驚豔，
SQL2016 In-Memory OLTP 吞吐量1 200 000 batch requests/sec，
每秒可以有250,000 Requests~交易效能天殺的快
多台cache server，容易擴充且有HA。
Frmo Microsoft
透過SQL2016減少Server 數量，使用In-Memory OLTP為統一的Cache Database，
並透過Alwayson達到HA目的
Frmo Microsoft
Frmo Microsoft
實現上面核心架構相當簡單，從database角度來看基本上就是key/value概念，
你必須要先建立一個memory table，再透過native compilation SP和function進行InsertOrUpdate、
Delete(需搭配SQL Agent Job)、Select相關處理，
而我去年也把所有web server side的cache都搬入SQL2016 In-Memory table，
下面我簡單使用sessionID示範。
--create session table
CREATE TABLE dbo.[Sessions](
SessionId nvarchar(88) NOT NULL,
Created datetime2 NOT NULL,
Expires datetime2 NOT NULL,
ItemSize bigint NOT NULL,
Item varbinary(8000) NULL,
CONSTRAINT [PK_Sessions_SessionId]
PRIMARY KEY NONCLUSTERED HASH (SessionId) WITH (BUCKET_COUNT = 100)
)
WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY)—Alwayson下，必須改成 SCHEMA_AND_DATA，避免failover後資料遺失
GO
--確認sessionID筆數
CREATE FUNCTION [dbo].[uf_GetSessionCount_native](@SessionId nvarchar(88))
RETURNS tinyint
WITH NATIVE_COMPILATION, SCHEMABINDING , EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
DECLARE @ReturnValue tinyint=0;
SET @ReturnValue = (
select COUNT(1)
from dbo.Sessions
where SessionId=@SessionId)
RETURN (@ReturnValue);
END
--新增或更新session
CREATE PROCEDURE dbo.InsertOrUpdateStateItem(
@SessionId nvarchar(88) NOT NULL,
@Timeout int NOT NULL,
@ItemSize bigint NOT NULL,
@Item varbinary(8000) NOT NULL
) WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
DECLARE @Now AS datetime2 NOT NULL = GETUTCDATE();
DECLARE @Expires AS datetime2 NOT NULL = DATEADD(minute, @Timeout, @Now);
DECLARE @new tinyint =0;
select @new=dbo.uf_GetSessionCount_native(@SessionId);
IF @new=1
begin--update
UPDATE dbo.Sessions
SET
ItemSize = @ItemSize,
Expires = @Expires,
Item = @Item
WHERE SessionId = @SessionId
end
else if @new=0
begin
insert dbo.Sessions(SessionId,Created,Expires,ItemSize,Item)
values(@SessionId,@Now,@Expires,@ItemSize,@Item)
end
END
GO
--刪除過期session data
CREATE PROCEDURE dbo.DeleteOldSessions
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
delete dbo.Sessions
where Expires <GETUTCDATE()
END
--查詢session data
CREATE PROCEDURE dbo.GetSessionItem(@SessionId nvarchar(88) NOT NULL)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
select SessionId,Created,Expires,ItemSize,Item --explicit column
from dbo.Sessions
where SessionId =@SessionId
END
Controller我簡單隨便寫~XD
public class HomeController : Controller
{
private readonly string _connectionstring = ConfigurationManager.ConnectionStrings["dev"].ConnectionString;
// GET: Home
public ActionResult Index()
{
var userinfo = new UserInfo
{
Name = "rico",
Age = 34,
Dep = "dev",
Email = "rico@moon.com",
Role = "user"
};
string sql = @"dbo.InsertOrUpdateStateItem";
var sessionid = Guid.NewGuid().ToString();
var item = ObjectToByteArray(userinfo);
using (IDbConnection connection = new SqlConnection(_connectionstring))
{
var result = connection.Execute(sql, new { SessionId = sessionid, Timeout = 30, ItemSize = item.LongLength, Item = item },
null, commandType: CommandType.StoredProcedure);
}
using (IDbConnection connection = new SqlConnection(_connectionstring))
{
var result = connection.Query<byte[]>("select Item from dbo.Sessions where SessionId=@SessionId", new { SessionId = sessionid })
.FirstOrDefault();
return View(ByteArrayToObject(result));
}
}
byte[] ObjectToByteArray(object obj)
{
if (obj == null)
return null;
BinaryFormatter bf = new BinaryFormatter();
using (MemoryStream ms = new MemoryStream())
{
bf.Serialize(ms, obj);
ms.Position = 0;
return ms.ToArray();
}
}
UserInfo ByteArrayToObject(byte[] userinfoData)
{
using (MemoryStream ms = new MemoryStream(userinfoData))
{
BinaryFormatter bf = new BinaryFormatter();
return (UserInfo)bf.Deserialize(ms);
}
}
}
View也是青菜拉~哈
--設定Delete OldSessionData agent job(每10秒掃一次Sessions memory table)
USE [msdb]
GO
/****** Object: Job [DeleteOldSessions] Script Date: 2017/10/4 下午 04:41:40 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 2017/10/4 下午 04:41:40 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DeleteOldSessions',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'RICONB\rico', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Delete] Script Date: 2017/10/4 下午 04:41:41 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delete',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec dbo.DeleteOldSessions',
@database_name=N'mymemoryDB',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'DeleteOldSessions',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=2,
@freq_subday_interval=10,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20171004,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'6ff25ed2-016c-4e83-b5b9-94c372351736'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
接下來用我熟悉TSQL查詢這些Cache data
目前有4筆資料。
30分鐘過後，再次用熟悉的TSQL查詢cache data
過期資料自動會被agent job刪除，GC會自動回收並減少記憶體用量(只剩下3筆)。
參考
In-Memory OLTP in Azure SQL Database
SQL Server In-Memory OLTP as ASP.NET Session State Provider
New: ASP.NET Session State Provider for SQL Server In-Memory OLTP
Storing ASP.NET session outside webserver – SQL Server vs Redis vs Couchbase
Windows Server 2016 Hyper-V large-scale VM performance for in-memory transaction processing
ASP.NET Session State with SQL Server 2016
How bwin is using SQL Server 2016 In-Memory OLTP to achieve unprecedented performance and scale
