交易紀錄檔 VLF的數量對效能影響

在SQLSERVER中，不論DB在哪種復原模式下，所有的異動都會寫入交易紀錄檔。差別在於紀錄的方式以及清除的方式不同。因此，如果交易紀錄檔的效能不好，其影響是全面性的。

SQLSERVER會將交易紀錄檔（transaction log file / ldf）劃分成多個「虚擬紀錄檔（Virtual Log File - VLF）來管理。

SQL2014以前的版本（不含2014），當建立、手動或自動成長LDF檔時，系統會將其劃分成數個VLF，如下表所示：

增量的大小	VLF數
< 64MB	4
>=64MB and < 1GB	8
>=1GB	16

在SQL2014，則有不同的演算法：

如果成長的量小於現行log file size的1/8，那麼就只增加一個VLF。

如果大於現行log file size的1/8，那麼則依照舊有的規則。

我們可以使用dbcc loginfo來查看VLF的資訊，這也是目前唯一的方法。

以下圖為例，該資料庫的交易紀錄檔(*.ldf)，共被劃分為10個VLF來管理。

所以，假設你建立資料庫時，LDF檔初始大小設為10MB，那麼資料庫建立後，LDF檔會被切割為4個VLF。如果有設定自動成長，每次成長10MB，那麼每成長一次，就會再多出4個VLF。

在一個繁忙的系統中，交易紀錄檔的初始大小如果不夠大，在系統運作的過程中，則會經常觸發自動成長。每次成長，系統就會將該空間依前述的規則切成數個VLF，因此VLF的數量也會增加。

自動成長所分配的空間，加入到原本的ldf檔的尾部。在邏輯上它會是連續的。但從實體上看，則整個ldf檔會是由許多不連續的空間所組成。因此，如果你的VLF的數量越多，表示你的交易紀錄檔(ldf)破碎的越嚴重。通常這被稱為 Transaction Log Fragmentation - 交易紀錄碎片，或者 VLF Fragmentation 。

我們知道，一般資料檔的碎片，會影響效能，所以我們通常會定期的檢查並重整。

但~~~對於交易紀錄檔的碎片呢？

或許大家也跟我初次聽到時一樣~~~「啥….交易紀錄也有碎片…！！！」

交易紀錄碎片的定義是比較粗糙的，一般是使用交易紀錄檔中VLF的數量來評估。也就是VLF的數量越多，表示交易紀錄的碎片越嚴重。

至於VLF的數量超過多少個才應該進行一些處理，這並沒有一個確切的標準可供遵循。一般的建議是VLF不要超過1000個。

我們可以使用下列指令，來取得所有DB的VLF的計數：

DECLARE @query varchar(1000),

  @dbname varchar(1000),

  @count int

SET NOCOUNT ON

DECLARE csr CURSOR FAST_FORWARD READ_ONLY

FOR

SELECT name

FROM sys.databases

CREATE TABLE ##loginfo

(

  dbname varchar(100),

  num_of_rows int)

OPEN csr

FETCH NEXT FROM csr INTO @dbname

WHILE (@@fetch_status <> -1)

BEGIN

CREATE TABLE #log_info

(

  RecoveryUnitId tinyint,

  fileid tinyint,

  file_size bigint,

  start_offset bigint,

  FSeqNo int,

[status] tinyint,

  parity tinyint,

  create_lsn numeric(25,0)

)

SET @query = 'DBCC loginfo (' + '''' + @dbname + ''') '

INSERT INTO #log_info

EXEC (@query)

SET @count = @@rowcount

DROP TABLE #log_info

INSERT ##loginfo

VALUES(@dbname, @count)

FETCH NEXT FROM csr INTO @dbname

END

CLOSE csr

DEALLOCATE csr

SELECT dbname,

  num_of_rows

FROM ##loginfo

--WHERE num_of_rows >= 50

ORDER BY dbname

DROP TABLE ##loginfo

接下來，我們實際測試一下，交易紀錄的碎片真的對效能有影響嗎？

首先我建立兩個DB：

VLF_LARGE：模擬有許多的VLF，它的LOG的初始值10MB，然後用alter database的指令不斷的加LOG空間，以模擬自動成長，目地是增加VLF的數量。

VLF_SMALL：模擬有較少的VLF，它的LOG的初始值直接給4GB。

	VLF_SMALL	VLF_LARGE
資料檔大小	4GB	4GB
資料檔路徑	H:\SQLData	H:\SQLData
LOG檔大小	4GB	4GB
LOG檔路徑	G:\SQLLog	G:\SQLLog
復原模式	Full	Full
VLF數量	16	4,008

接著我在兩個DB各建立一個空的TABLE，然後測試從另一個DB的TABLE(300萬筆)，將資料一次性新增進來。並且再測試update及delete。

經過四輪的測試，結果如下表

DML	VLF_SMALL					VLF_LARGE
	1	2	3	4	平均	1	2	3	4	平均
INSERT	24	18	18	17	19.25	16	24	23	25	22
UPDATE	5	4	7	5	5.25	9	8	10	14	10.25
DELETE	7	5	11	7	7.5	17	10	21	15	15.75

從結果來看，VLF Fragmentation確實對這種大資料量的操作效能有所影響，我這個案例是測試300萬筆，是一個較大IO的操作。

由於交易紀錄是循序性的寫入，本文所測試的這種大資料量的操作，可能一次的操作所產生的交易紀錄量，就會跨許多不同的VLF才能儲存，因此若VLF碎片過高，會影響寫入的效能。

如果是小資料量的操作（如OLTP），由於交易紀錄量小（一次交易量不超過1個VLF），則不受VLF碎片的影響。

上述的測試，其DB後端storage是傳統的旋轉式硬碟。

有趣的是，後來我改用本機的SSD硬碟進行上述測試，則不論VLF_LARGE或VLF_SMALL，兩者對於DML的效能幾乎完全一致，也就是說如果DB的儲存媒體是SSD，則DML的效能不受VLF Fragementation的影響。這跟索引碎片在SSD的結果類似，碎片之所以在傳統旋轉式硬碟上會影響效能，主要是磁頭的物理移動頻繁所致，而SSD由於其存取的方式不同，所以沒有這個問題。

VLF數量對recovery效能的影響

VLF數量的多寡，對於recovery的影響則是巨大的，如果你的資料庫在啟動時需要很久才會online，且狀態處於recovery pending，那麼這篇文章會對你有所幫助。

我們做一個簡單的測試，有興趣的朋友也可以跟著試試看。

CREATE DATABASE [VLF_SMALL]

 ON  PRIMARY

( NAME = N'VLF_SMALL', FILENAME = N'H:\SQLData\VLF_SMALL.mdf' , SIZE = 2GB , FILEGROWTH = 1024KB )

 LOG ON

( NAME = N'VLF_SMALL_log', FILENAME = N'G:\SQLLog\VLF_SMALL_log.ldf' , SIZE = 2GB , FILEGROWTH = 10%)

GO

CREATE DATABASE [VLF_LARGE]

 ON  PRIMARY

( NAME = N'VLF_LARGE', FILENAME = N'H:\SQLData\VLF_LARGE.mdf' , SIZE = 2GB , FILEGROWTH = 1024KB )

 LOG ON

( NAME = N'VLF_LARGE_log', FILENAME = N'G:\SQLLog\VLF_LARGE_log.ldf' , SIZE = 1MB , FILEGROWTH = 10%)

GO

use master

go

declare @x int

declare @ssql varchar(1024)

set @x=10

while (@x<=2048)

   begin

      set @x=@x+1

      set @ssql='alter database VLF_LARGE modify file ( name = ''VLF_LARGE_log'', size = '+convert(varchar,@x)+'MB )'

      exec (@ssql)

   end

go

dbcc loginfo('VLF_LARGE')   --查看VLF數量有8160個

go

dbcc loginfo('VLF_SMALL')   --查看VLF數量有16個

go

資料庫相關指標如下

	VLF_SMALL	VLF_LARGE
資料檔大小	2GB	2GB
資料檔路徑	H:\SQLData	H:\SQLData
LOG檔大小	2GB	2GB
LOG檔路徑	G:\SQLLog	G:\SQLLog
復原模式	Full	Full
VLF數量	16	8,160

這兩個 DB都是空的，剛剛建立，其中沒有任何的user tables。接著我們將它offline後再online，並計算online所需的時間，指令如下：

declare @x datetime

alter database VLF_SMALL set offline

set @x=getdate()

alter database VLF_SMALL set online

select datediff(ss,@x,getdate())

由上圖可見，VLF_SAMLL的recovery僅需1秒鐘。而VLF_LARGE的recovery需49秒，兩者recovery的速度差異非常的大。

SQL SERVER在執行recovery時，一次掃瞄一個VLF，當VLF數目多，會有較頻繁的VLF切換，故導致recovery所需時間的增加。

當你的SQL SERVER啟動時DB StartUp需要太長的時間，請檢查DB的VLF數量是否太多。

VLF的數量及大小多少才適合，有幾個重要的參考指標：

一、每個VLF size不超過500MB。

二、在SQL2012當VLF數量超過1000個時，當SQL start、restore、attach時會有警告。

三、VLF的數量上限不要超過1000個。

四、VLF數量的甜蜜點（sweet spot）為100個。

當超過1萬個VLF時，如果DB有recovery的情況時，則系統會發出警告，但警告的內容會告訴你說DB超過1仟個VLF時可能導致long startup and backup效能問題。但其實只要有recovery的動作，如offline/online、restore都會跳訊息。訊息如下：

Database dbName has more than n virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log

VLF的數量及大小，也是影響SQL SERVER效能的一個重要環節，就像是我們日常重整索引的碎片，VLF碎片也需要定期的關注。

下一篇文章，我想接著談談VLF碎片的解決方法以及交易紀錄檔的壓縮(Shrink)。