在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碎片的影響。
有趣的是，後來我改用本機的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)。
