[SQL SERVER]最小化VLF

2015-02-23

2512
0
SQL_SERVER

[SQL SERVER]最小化VLF

很久以前我所分享造成交易記錄檔案暴增的常見原因一文有提到最小化VLF，

這篇補充如何最小化VLF數量。

為什麼VLFs數量要最小化

SQL Server Database Engine會在內部將每個實體記錄檔分成數個虛擬記錄檔(VLF)，

由於過多的VLF將導致新增、刪除、更新效能和資料庫啟動緩慢，

且執行交易紀錄檔備份和還原時間也會大大增加，

同時SQL2012如果VLFs數量超過10000，錯誤紀錄將會得到VLFs數量大於1000錯誤訊息(下面我會驗證測試)，

雖然Database Engine會盡量維持少量VLF，

但因為無可避免硬碟碎片的產生(交易紀錄檔成長大小最好不要過低 )，

所以DBA還是要介入觀察VLF數量(自己抓50)是否過多，

SQL2012以前版本可以透過以下規則來估算VLF數量

1.每一檔案不足 1 MB 內部產生2 VLFs

2.每一檔案少於 64 MB 內部產生4 VLFs

3.每一檔案大於 64 MB 並小於等於 1 GB 內部產生 8 VLFs

4.每一檔案大於 1 GB 內部產生 16 VLFs

測試觀察VLF

1.建立資料庫(交易紀錄檔初始大小512 KB,filegrowth=512 KB)

create database mytest on primary
(
name='mytest',filename='F:\sql2k12file\mytest.mdf',size=100mb,maxsize=1000mb,filegrowth=100mb
)
log on
(
name='mytest_log',filename='F:\sql2k12file\mytest_log.;df',size=512kb,maxsize=11000mb,filegrowth=512kb
)

dbcc loginfo('mytest')

可以看到產生 2 個VLF，第一個page(8K)正在使用中(status=2)。

現在我要讓交易紀錄檔成長5000次，讓VLFs數量超過10000( 2+(5000*2)=10002 )

DECLARE @CurrentSize AS INT,@strLogFile AS NVARCHAR(1000);
--- Get current log file size.
--PRINT 'Current log file size is ' + CAST(@CurrentSize AS VARCHAR(1000)) + ' KB';
SELECT @CurrentSize=size*8 FROM sys.master_files WHERE database_id = DB_ID('mytest') AND name = 'mytest_Log'
--- Try to grow the transaction log by 512 KB. This should result in two additional VLF.
--PRINT 'Growing the transaction log to ' + CAST((@CurrentSize + 1) AS VARCHAR(1000)) + ' KB';
SET @strLogFile = 'ALTER DATABASE mytest MODIFY FILE (NAME = mytest_Log, SIZE = ' + CAST((@CurrentSize + 512) AS VARCHAR(1000)) + ' KB);';
EXEC (@strLogFile);
GO 5000

dbcc loginfo('mytest')

雖然現在VLF數量超過10000，但不會馬上觸發錯誤，以下三個任一情況發生後才會觸發

1.SQL Server重啟

2.設定資料庫上線

3.還原資料庫

現在我重新設定資料庫上線後即可在錯誤紀錄中看到錯誤訊息

ALTER DATABASE mytest SET OFFLINE;

ALTER DATABASE mytest SET ONLINE;

錯誤記錄明確顯示VLFs數量大於1000將影響啟動、備份和還原時間，同時也告知使用shrinking來移除VLFs並適當調整成長大小。

利用 shrinking 管理VLFs數量

現在我的交易紀錄檔案大小=(512 +(512*5000))/1024 =2501 MB

SQL Server自動分配大小為 253952、270336依序循環(一組(253952+270336)/1024=512 KB)

移除最後2個VLF DBCC SHRINKFILE (N'mytest_log' , 2500) 2501-2500=1 MB

移除最後8個VLF DBCC SHRINKFILE (N'mytest_log' , 2498) 2500-2498=2 MB

移除最後16個VLF DBCC SHRINKFILE (N'mytest_log' , 2494) 2498-2494=4 MB

移除最後32個VLF DBCC SHRINKFILE (N'mytest_log' , 2486) 2494-2486=8 MB

移除最後64個VLF DBCC SHRINKFILE (N'mytest_log' , 2470) 2486-2470=16 MB

以這案例來看我得到一個規則，假設現在我只要保留VLF總數量為8，需壓縮檔案大小為2MB

DBCC SHRINKFILE (N'mytest_log' , 2)

現在你知道當VLFs過多時可以透過shrinkfile來移除管理，我們在來看看自動成長預設值所產生的VLFs數量

查詢VLFs數量

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

可以看到 msdb VLFs數量超過50，而這也是一般我常看到的情況，

因為大部分人的資料庫都採取預設自動成長10%，主要有以下3個缺點

1.需計算成長大小，當資料庫越來越大時，將造成block情況嚴重(計算大小需要時間)

2.過多的VLF

3.過多的硬碟碎片

基於VLFs規則，我建議給予一個固定大小且不要超過1 GB(視環境 500 MB~1 GB)。

SQL2014 VLF改善

SQL2014改善了VLF產生數量，規則如下

If
         growth < current size / 8 = 1 VLF
else
         growth <= 64MB = 4 VLFs
         growth > 64MB AND <= 1024MB = 8 VLFs
         growth > 1024MB = 16 VLFS

假設交易紀錄檔初始大小 1 MB，自動成長預設10 %，當交易紀錄檔成長到 101 MB時，VLF數量如下

SQL2012:4+(100*4)