[SQL Server]Read Committed Snapshot Isolation(RCSI)隔離層級的效能副作用

為了減輕查詢交易被大型更新交易封鎖(Blocked),也理解樂觀鎖定(Optimistic Locking)可能的交易衝突後,也許我們會在SQL Server上使用Oracle預設相同的隔離層級: Read Committed Snapshot Isolation(RCSI),她是一種Row Version Base的隔離層級,與snapshot isolation不太相同的是她是單一版本。當交易更新資料時,系統會自動將修改前的版本放到Tempdb提供給可能發生的查詢交易。因為也有搬分頁到其他資料庫的工作會執行,在效能上會不會有明顯的副作用?

SQL Server預設是Read Committed,Oracle則是Read Committed Snapshot Isolation(RCSI)

建立環境

建立資料庫

CREATE DATABASE [SnapshotDb]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'SnapshotDb', FILENAME = N'C:\SQL\SnapshotDb.mdf' , SIZE = 2GB , FILEGROWTH = 1GB )
 LOG ON 
( NAME = N'SnapshotDb_log', FILENAME = N'C:\SQL\SnapshotDb_log.ldf' , SIZE = 1GB , FILEGROWTH = 1GB)
GO

 

建立一個100萬筆的資料表

USE [SnapshotDb]
CREATE TABLE T1
(ID INT IDENTITY,C2 CHAR(2000),C3 uniqueidentifier, PRIMARY KEY(ID))

SET NOCOUNT ON 
DECLARE @I INT = 0
BEGIN TRAN
WHILE @I < 1000000
BEGIN
INSERT INTO T1
	VALUES (CONVERT(VARCHAR,@I),NEWID())
SET @I = @I + 1
END
COMMIT

 


對照組

1.尚未啟用Read Committed Snapshot Isolation之前,我們用批次交易更新100萬筆當中的10萬筆,但交易先不要結束(commit或是rollback)。

SET STATISTICS IO ON 
SET STATISTICS TIME ON 
BEGIN TRAN T1
UPDATE T1 
   SET C2 = NEWID()
WHERE ID >= 300000 AND ID < 400000

執行結果:

資料表 'T1'。掃描計數 1,邏輯讀取 33463,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。

SQL Server 執行次數: 
CPU 時間 = 250 ms,經過時間 = 277 ms

 

2.新開一個查詢視窗,查詢id = 300001的資料

SELECT * from t1 WITH(NOLOCK)
 where id = 300001

SELECT * from t1 
 where id = 300001

查詢交易封鎖中! 不過更新交易的CPU 時間 = 250 ms,經過時間 = 277 ms

好,然後把交易Rollback..

 


實驗組

1.啟用Read Committed Snapshot Isolation

ALTER DATABASE [SnapshotDb] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO

 

2.重新執行剛剛的批次更新交易

USE SnapshotDb
SET STATISTICS IO ON 
SET STATISTICS TIME ON 
BEGIN TRAN T1
UPDATE T1 
   SET C2 = NEWID()
WHERE ID >= 300000 AND ID < 400000

執行結果

資料表 'T1'。掃描計數 1,邏輯讀取 33463,實體讀取 3,讀取前讀取 33431,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。

 SQL Server 執行次數: 
,CPU 時間 = 391 ms,經過時間 = 560 ms。

 

3.新開一個查詢視窗,查詢id = 300001的資料

交易不再被封鎖,我們可以有中途查詢(Dirty Read)或是修改前的查詢(Row Version)兩種選擇。

 


透過DMV查詢目前資料列版本

SELECT
	 sdt.session_id as '[連線id]'
	,sdt.transaction_id as '[交易id]'
	,CAST(DATEADD(second, sdt.elapsed_time_seconds, '19000101') as TIME)  as '[資料列版本時間]'
	,[host_name] as '[主機名稱]'
	,last_request_start_time
	,last_request_end_time
	,st.[text]  as '[SQL 語法]'
	,s.row_count as '[資料筆數]'
FROM sys.dm_tran_active_snapshot_database_transactions sdt
INNER JOIN sys.dm_exec_sessions s
	ON s.session_id = sdt.session_id
JOIN sys.dm_exec_connections c
	ON s.[session_id] = c.[session_id]
LEFT OUTER JOIN sys.dm_exec_requests R
	ON s.[session_id] = R.[session_id]
CROSS APPLY sys.dm_exec_sql_text(c.[most_recent_sql_handle]) AS st
OUTER APPLY sys.dm_exec_query_plan(R.[plan_handle]) AS qp
WHERE s.is_user_process = 1
ORDER BY elapsed_time_seconds DESC;

查詢結果:

 


小結:

效能上有影響,執行時間多1倍,cpu也增加。不過如果都只是0.3秒的差別,也許就還能接受

隔離層級 CPU  執行時間
Read Committed  250ms 277ms
Read Committed Snapshot Isolation 391ms 560ms
  • tempdb牛仔很忙,要管user object、Internal job,還要支援version store,tempdb最佳化。
  • 大型交易分批做。
  • 如果再加上Online rebuild index,會有什麼火花?(下次試試)

 

隔離的世界,瑞士-格林德瓦

 

 


參考

Row Versioning Resource Usage

How Row versioning impact tempDB ?

Read Committed Snapshot Isolation– Two Considerations

Tempdb Space Usage in SQL Server

Understanding Row Versioning-Based Isolation Levels