ความสำคัญของจำนวน Virtual Log Files (VLFs) ใน Transaction Log File

Transaction Log นั้นถูกใช้เป็นกลไกหลักตั้งแต่เริ่มสตาร์ท SQL Server โดยข้อมูลใน Transaction Log จะถูกอ่านเพื่อใช้ในกลไก Recovery ก่อนเริ่มปล่อยให้ผู้ใช้เข้าใช้งาน หากกลไก Recovery ใช้เวลานาน ก็จะปล่อยให้ผู้ใช้เข้าใช้งานช้าลงไป แม้ว่าเราสามารถกำหนดให้เวลาในการ Recovery ของ SQL Server สำเร็จภายในเวลาที่กำหนดผ่าน Recovery interval (minutes) ซึ่งเป็นการกำหนดค่าระดับ Server (หรือ Instance) ก็ตาม แต่กลไก Recovery นั้นมีขั้นตอนอยู่ทั้งหมด 4 ขั้นตอน ดังนี้

ทักษะ (ระบุได้หลายทักษะ)

Data

ความสำคัญของจำนวน Virtual Log Files (VLFs) ใน Transaction Log File

ผู้เขียนมักสอบถามถึงความเข้าใจเกี่ยวกับ Transaction Log เอาจากผู้เข้าอบรมอยู่เสมอๆ และก็มักจะพบถึงความเข้าใจที่คลาดเคลื่อน
เพราะส่วนใหญ่มักคิดว่ามันเหมือนกับระบบบันทึก Error Log และให้คุณค่ากับตัว Transaction Log น้อยเกินไป

การเข้าใจที่ไม่ถูกต้องส่งผลให้เกิดการจัดการกับ Transaction Log อย่างไม่ถูกต้อง จนส่งผลกระทบด้านประสิทธิภาพตามมา

Transaction Log นั้นถูกใช้เป็นกลไกหลักตั้งแต่เริ่มสตาร์ท SQL Server โดยข้อมูลใน Transaction Log จะถูกอ่านเพื่อใช้ในกลไก Recovery
ก่อนเริ่มปล่อยให้ผู้ใช้เข้าใช้งาน หากกลไก Recovery ใช้เวลานาน ก็จะปล่อยให้ผู้ใช้เข้าใช้งานช้าลงไป

แม้ว่าเราสามารถกำหนดให้เวลาในการ Recovery ของ SQL Server สำเร็จภายในเวลาที่กำหนดผ่าน Recovery interval (minutes)
ซึ่งเป็นการกำหนดค่าระดับ Server (หรือ Instance) ก็ตาม แต่กลไก Recovery นั้นมีขั้นตอนอยู่ทั้งหมด 4 ขั้นตอน ดังนี้

ซึ่งเป็นการกำหนดค่าระดับ Server (หรือ Instance) ก็ตาม แต่กลไก Recovery นั้นมีขั้นตอนอยู่ทั้งหมด 4 ขั้นตอน ดังนี้

ตั้งแต่ขั้นตอนที่ 2-4 ถูกควบคุมโดย การกำหนดค่า Recovery interval (minutes) ได้แต่ขั้นตอนที่ 1 นั้นเหนือการควบคุมจะขึ้นอยู่กับจำนวนของ Virtual Log Files (VLFs)

นอกจากนั้น Transaction Log ยังบันทึกรายละเอียดของ Transaction จากผู้ใช้งานที่ประกอบด้วยคำสั่ง INSERT, UPDATE และ DELETE เพื่อปรับปรุงข้อมูล
ก่อนที่ข้อมูลเหล่านั้นปรับปรุงลงใน Data Files เสียอีก (Data Files จะถูกปรับปรุงจากข้อมูลใน Buffer Pool หลัง Checkpoint ถูกปล่อยออกมาเป็นระยะ ๆ ตามความเหมาะสม

ผู้เขียนไม่ขอลงรายละเอียดในบทความนี้ ผู้อ่านสามารถหาอ่านในเรื่องของ Write-Ahead Logging (WAL)ได้จากหลายแหล่งข้อมูล หรือติดตามในบทความต่อไปของผู้เขียน
ดังนั้นหาก Transaction Log เกิดคอขวดขึ้น ก็จะทำให้ประสิทธิภาพในการปรับปรุงข้อมูลในฐานข้อมูลแย่ตามไปด้วย

นอกจากนั้นข้อมูลที่เหลือค้างอยู่ใน Transaction Log หรือที่เรียกกันว่า Tail-Log หลังจากฐานข้อมูลพังเสียหาย
แต่โชคดีที่ Transaction Log File ไม่ได้เสียหายตามไปด้วย ก็สามารถนำส่วน Tail-Log มาประกอบการกู้คืนข้อมูล โดยข้อมูลไม่หายเลย หรือ RPO=0 นั่นเอง

** การวางแผนเอาไว้ไม่ให้ส่วนของ Transaction Log File เสียหายตามไปด้วย นั้นสามารถทำได้ ผู้อ่านสามารถติดตามได้ในหลักสูตรฝีกอบรมจากทาง 9Expert
** RPO=Recovery Time Objective การระบุปริมาณข้อมูลที่หายว่าเป็นเท่าไหร่ทำได้ยาก จึงคิดเป็นหน่วยเวลาแทน เช่นหายไป 5 นาที RPO เป็นจุดที่เราตกลงยอมรับว่าจะยอมให้ข้อมูลหายได้เท่าไหร่เป็นหน่วยเวลา

จากทั้งหมดที่ผู้เขียนได้เล่าให้ฟังจะเห็นว่าตั้งแต่ก่อนเริ่มปล่อยให้ผู้ใช้เข้าใช้งานฐานข้อมูล ขณะใช้งานฐานข้อมูล หรือแม้จะทั้งฐานข้อมูลพังเสียหาย Transaction Log ก็เข้ามามีบทบาทสำคัญทั้งสิ้น
ดังนั้นควรเอาใจใส่ดูแล Transaction Log ให้ดี อยู่บททรัพยากรที่เหมาะสม ประสิทธิภาพและเสถียรภาพจึงจะออกมาน่าพอใจ

สำหรับเนื้อหาในบทความนี้ผู้เขียนจะไม่เล่าถึงการวางแผนทรัพยากรสำหรับ Transaction Log และการทดลองทั้งหมดในบทความนี้ตั้งอยู่บน Full Recovery Model เป็นหลัก
ดังนั้น Transaction Log จะถูก Truncate ก็ต่อเมื่อมีการ Backup ในส่วนของ Transaction Log เท่านั้น

ผู้เขียนขึ้นหัวบทความไว้เป็นเรื่องของจำนวน Virtual Log Files (VLFs) ใน Transaction Log File และมีพูดถึงนิดหน่อยในขั้นตอนที่ 1 ของการ Recovery บนย่อหน้าก่อนหน้า

Virtual Log Files (VLFs)

ในแต่ละฐานข้อมูลจะต้องมี Transaction Log File อย่างน้อยหนึ่งไฟล์ ตาม Best Practice
แล้วก็ไม่ควรมีมากกว่าหนึ่งไฟล์ การมีหลายไฟล์ไม่ได้ช่วยให้ประสิทธิภาพเพิ่มขึ้นเลย

บางท่านอาจมีข้อโต้แย้งว่า ที่ต้องมีหลายไฟล์เพราะ มันเต็มขนาดความจุในดิสก์ลูกเดิม จึงต้องเพิ่มไฟล์ในดิสก์ลูกใหม่
หากปล่อยให้ Transaction Log File เต็ม จะไม่สามารถปรับปรุงข้อมูลได้
ผู้เขียนก็จะตอบประเด็นนี้ในทันทีเช่นกันว่า นี่คือสิ่งที่ฟ้องออกมาว่าคุณไม่ได้บริหาร Transaction Log เลย

การที่มี Transaction Log File หลายไฟล์ไม่ได้ช่วยเพิ่มประสิทธิภาพเป็นเพราะการบันทึกข้อมูลลง Transaction Log File เป็นการบันทึกแบบ Sequential
จะต้องบันทึกไฟล์ที่หนึ่งจนเต็มเสียก่อนแล้วจึงจะขึ้นไฟล์ที่สองได้
แต่เราสามารถบริหารให้มีการเวียนบันทึกลงใน Transaction Log File ไฟล์เดียวนี่แหละ ผ่านทาง VLFs และองค์ประกอบที่เกี่ยวข้อง

การที่มี Transaction Log File หลายไฟล์ไม่ได้ช่วยเพิ่มประสิทธิภาพเป็นเพราะการบันทึกข้อมูลลง Transaction Log File เป็นการบันทึกแบบ Sequential

Transaction Log File จะถูกแบ่งออกเป็น VLFs เพื่อใช้ในการบริหารการเวียนบันทึก
สังเกตจากรูปภาพจะเห็นว่าในส่วนของ VLF 1 และ VLF 2 ได้ถูก Truncated ไปแล้ว
ทำให้สามารถนำมาเวียนบันทึกได้หากบันทึก Transaction ลงใน VLF 5 จนเต็ม

จากที่ผู้เขียนได้บอกไว้แล้วว่าจะใช้ Full Recovery Model
ดังนั้น Transaction Log จะถูก Truncate ก็ต่อเมื่อมีการ Backup ในส่วนของ Transaction Log เท่านั้น

เมื่อการ Backup ในส่วนของ Transaction Log เกิดขึ้น กลไกจะตรวจสอบว่า Minimum LSN อยู่ใน VLF ใด
การ Truncate จะเกิดกับ VLFs ทั้งหมดก่อนหน้าเท่านั้น

ในกรณีนี้ Minimum LSN อยู่ใน VLF3 จึงจะ Truncate กับ VLF 1 และ VLF 2
จำนวนของ VLFs จะขึ้นอยู่กับขนาดของ Transaction Log File ที่สร้างขึ้นและขนาดของ Autogrowth ในแต่ละครั้ง
ซึ่งกรณีของ Autogrowth เป็นต้นเหตุให้จำนวนของ VLFs เพิ่มจำนวนขึ้นจนส่งผลกระทบต่อประสิทธิภาพ

** Minimum LSN คือเลขประจำ Transaction ที่น้อยที่สุดที่ยัง Active อยู่

กรณีสร้าง Transaction Log File ขึ้นใหม่

ขนาด Transaction Log File	จำนวน VLFs	ขนาดของแต่ละ VLF
น้อยกว่า 1 Mbytes	2	ขนาด Transaction Log File/2
1 Mbytes - 64 Mbytes	4	ขนาด Transaction Log File/4
มากกว่า 64 Mbytes - 1 Gigabytes	8	ขนาด Transaction Log File/8
มากกว่า 1 Gigabytes	16	ขนาด Transaction Log File/16

ผู้เขียนจะทดลองสร้างฐานข้อมูลโดยกำหนดขนาดของ Transaction Log File ในแต่ละช่วงเพื่อทดสอบ

ขนาดน้อยกว่า 1 Mbytes

CREATE DATABASE A ON
( NAME = 'A', FILENAME = 'D:\Test\A.mdf' , SIZE = 8MB ,FILEGROWTH=0)
LOG ON
( NAME = 'A_log', FILENAME = 'D:\Test\A_log.ldf' , SIZE = 512KB, FILEGROWTH=0);

ผู้เขียนสร้างฐานข้อมูล A กำหนดให้ Transaction Log File มีขนาด 512 Kbytes
และไม่มี Autogrowth จากนั้นผู้เขียนได้สืบค้นจำนวน VLFs ด้วยสคริปต์ต่อไปนี้

USE A;
GO

DBCC LOGINFO(); --Previous SQL Server 2017

SELECT * FROM sys.dm_db_log_info(DB_ID()); --SQL Server 2017

หากทดสอบบน Microsoft SQL Server ก่อน Microsoft SQL Server 2017
ผู้เขียนแนะนำให้ใช้คำสั่ง DBCC LOGINFO()

แต่หากใช้งาน Microsoft SQL Server 2017 หรือ Azure Database
ผู้เขียนขอแนะนำ Dynamic Management Function ชื่อ sys.dm_db_log_info ที่ให้รายละเอียดได้มากกว่าเวอร์ชั่นก่อนหน้า

ผลลัพธ์ของ DBCC LOGINFO() เป็นดังนี้

และผลลัพธ์ของ sys.dm_db_log_info เป็นดังนี้

ผลลัพธ์ที่ได้คือ 2 แถวข้อมูลตามจำนวน VLFs=2 เนื่องจากขนาดของ Transaction Log File น้อยกว่า 1 Mbytes
และขนาดของ VLF เท่ากับ 512/2=256 Bytes หรือประมาณ 0.25 Mbytes

ขนาด 1 Mbytes ถึง 64 Mbytes

CREATE DATABASE B ON
( NAME = 'B', FILENAME = 'D:\Test\B.mdf' , SIZE = 8MB, FILEGROWTH=0)
LOG ON
( NAME = 'B_log', FILENAME = 'D:\Test\B_log.ldf' , SIZE = 4MB, FILEGROWTH=0);

ผู้เขียนสร้างฐานข้อมูล B กำหนดให้ Transaction Log File มีขนาด 4 Mbytes
และไม่มี Autogrowth จากนั้นผู้เขียนได้สืบค้นจำนวน VLFs ด้วยสคริปต์ต่อไปนี้

USE B;
GO

DBCC LOGINFO(); --Previous SQL Server 2017

SELECT * FROM sys.dm_db_log_info(DB_ID()); --SQL Server 2017

ผลลัพธ์ของ DBCC LOGINFO() เป็นดังนี้

และผลลัพธ์ของ sys.dm_db_log_info เป็นดังนี้

ผลลัพธ์ที่ได้คือ 4 แถวข้อมูลตามจำนวน VLFs=4 เนื่องจากขนาดของ Transaction Log File อยู่ในช่วง 1 Mbytes ถึง 64 Mbytes และขนาดของ VLF เท่ากับ 4/4 หรือประมาณ 1 Mbytes

ขนาดมากกว่า 64 Mbytes ถึง 1 Gigabytes

CREATE DATABASE C ON
( NAME = 'C', FILENAME = 'D:\Test\C.mdf' , SIZE = 8MB, FILEGROWTH=0)
LOG ON
( NAME = 'C_log', FILENAME = 'D:\Test\C_log.ldf' , SIZE = 100MB, FILEGROWTH=0);

ผู้เขียนสร้างฐานข้อมูล C กำหนดให้ Transaction Log File มีขนาด 100 Mbytes
และไม่มี Autogrowth จากนั้นผู้เขียนได้สืบค้นจำนวน VLFs ด้วยสคริปต์ต่อไปนี้

USE C;
GO

DBCC LOGINFO(); --Previous SQL Server 2017

SELECT * FROM sys.dm_db_log_info(DB_ID()); --SQL Server 2017

ผลลัพธ์ของ DBCC LOGINFO() เป็นดังนี้

และผลลัพธ์ของ sys.dm_db_log_info เป็นดังนี้

ผลลัพธ์ที่ได้คือ 8 แถวข้อมูลตามจำนวน VLFs=8 เนื่องจากขนาดของ Transaction Log File
อยู่ในช่วงมากกว่า 64 Mbytes ถึง 1 Gigabytes และขนาดของ VLF เท่ากับ 100/8 หรือประมาณ 12.5 Mbytes

ขนาดมากกว่า 1 Gigabytes

CREATE DATABASE D ON
( NAME = 'D', FILENAME = 'D:\Test\D.mdf' , SIZE = 8MB, FILEGROWTH=0)
LOG ON
( NAME = 'D_log', FILENAME = 'D:\Test\D_log.ldf' , SIZE = 2GB, FILEGROWTH=0);

ผู้เขียนสร้างฐานข้อมูล D กำหนดให้ Transaction Log File มีขนาด 2 Gigabytes
และไม่มี Autogrowth จากนั้นผู้เขียนได้สืบค้นจำนวน VLFs ด้วยสคริปต์ต่อไปนี้

USE D;
GO

DBCC LOGINFO(); --Previous SQL Server 2017

SELECT * FROM sys.dm_db_log_info(DB_ID()); --SQL Server 2017

ผลลัพธ์ของ DBCC LOGINFO() เป็นดังนี้

และผลลัพธ์ของ sys.dm_db_log_info เป็นดังนี้

ผลลัพธ์ที่ได้คือ 16 แถวข้อมูลตามจำนวน VLFs=16
เนื่องจากขนาดของ Transaction Log File มากกว่า 1 Gigabytes
และขนาดของ VLF เท่ากับ 2048/16 หรือประมาณ 128 Mbytes

จากผลการทดลองสร้าง Transaction Log File ในแต่ละช่วงขนาด จำนวน
และขนาดของ VLFs ก็จะเป็นไปตามตารางสูตรที่ให้ไว้

แต่สำหรับกรณีที่มี Autogrowth เกิดขึ้นตามมาจะเป็นไปตามสูตรคำนวณต่อไปนี้

แต่สำหรับกรณีที่มี Autogrowth เกิดขึ้นตามมาจะเป็นไปตามสูตรคำนวณต่อไปนี้

เริ่มการทดสอบโดยสร้างฐานข้อมูล X กำหนดให้ Transaction Log File มีขนาด 1 Mbytes
หรือเท่ากับ 1,048,576 bytes และให้ Autogrowth ที่ละ 1 Mbytes

CREATE DATABASE X ON
( NAME = 'X', FILENAME = 'D:\Test\X.mdf' , SIZE = 8MB, FILEGROWTH=0)
LOG ON
( NAME = 'X_log', FILENAME = 'D:\Test\X_log.ldf' , SIZE = 1MB, FILEGROWTH=1MB);
GO

ALTER DATABASE X SET RECOVERY FULL WITH NO_WAIT
GO

จากนั้นผู้เขียนได้สร้างตาราง test.person เพื่อใช้ในการทดสอบ ดังสคริปต์ต่อไปนี้

USE X;
GO
CREATE SCHEMA test;
GO

CREATE TABLE test.person(
       personid             int IDENTITY(1,1)    NOT NULL
,      lastname             nvarchar(20)         NOT NULL
,      firstname            nvarchar(10)         NOT NULL
,      email                nvarchar(200)        NOT NULL
,      country              nvarchar(8)          NOT NULL
,      phone                nvarchar(24)         NOT NULL
,      modifieddate        datetime2            NOT NULL
)
GO

INSERT test.person (lastname, firstname, Email, country, phone, modifieddate)
VALUES
('Davis','Sara','[email protected]','Thailand','(206) 555-0101',GETDATE())
,('Funk','Do','[email protected]','Thailand', '(206) 555-0100',GETDATE())
,('Lew','Judy','[email protected]','Thailand', '(206) 555-0103',GETDATE())
,('Peled','Yael','[email protected]','Thailand', '(206) 555-0104',GETDATE())
,('Buck','Sve','[email protected]','Thailand', '(71) 234-5678',GETDATE())
,('Suurs','Paul','[email protected]','Thailand', '(71) 345-6789',GETDATE())
,('King','Russell','[email protected]','Thailand','(71) 123-4567',GETDATE())
,('Camero','Maria','[email protected]','Thailand','(206) 555-0102',GETDATE())
,('Dolgopyatova','Zoya','[email protected]','Thailand','(71) 456-7890',
GETDATE())
GO

เมื่อได้ตารางที่ใช้ในการทดสอบแล้ว ผู้เขียนได้สร้างสคริปต์ เพื่อทำการวนลูป UPDATE ข้อมูลในตารางทดสอบทั้งตาราง เป็นจำนวน 100,000 รอบ
เพื่อให้เกิดการบันทึกลงใน Transaction Log จำนวนมากจนเกินขนาดตั้งต้น เพื่อบังคับให้เกิด AutoGrowth

SET NOCOUNT ON;
GO

DECLARE @i int =1
WHILE @i<=100000
       BEGIN
              UPDATE test.person
              SET modifieddate=SYSDATETIME() ,email=email;

              SET @i+=1;
       END;
GO

SET NOCOUNT OFF;
GO

หลังจากนั้นผู้เขียนทำการสืบค้นจำนวนครั้งที่เกิด AutoGrowth จาก Dynamic Management View
ชื่อ sys.dm_os_performance_counters
ดังสคริปต์ต่อไปนี้

USE Master;
GO

SELECT
*
FROM sys.dm_os_performance_counters
WHERE counter_name in      (
                                                'Log Growths'
                                         ,      'Percent Log Used'
                                         ,      'Log Flush Waits/sec'
                                         ,      'Log Bytes Flushed/sec'
                                         ,      'Log Flushes/sec'
                                         )
AND instance_name='X';

ผลลัพธ์ที่ได้คือ

พบว่า Transaction Log File เกิด AutoGrowth ขนาดคงที่ 1 Mbytes ทั้งสิ้น 10 ครั้ง
ผู้เขียนจึงทำการคาดคะเนจำนวน VLFs ด้วย Excel ไว้ดังนี้

ครั้งที่	ขนาด Transaction Log File (bytes)	1/8 (bytes)	VLFs	หมายเหตุ
0	1,048,576	131,072	4	เริ่มต้นสร้าง Database
1	2,097,152	262,144	8	ขนาดที่ Growth มากกว่า 1/8 ของครั้งก่อนหน้า เพิ่ม 4 VLFs
2	3,145,728	393,216	12	ขนาดที่ Growth มากกว่า 1/8 ของครั้งก่อนหน้า เพิ่ม 4 VLFs
3	4,194,304	524,288	16	ขนาดที่ Growth มากกว่า 1/8 ของครั้งก่อนหน้า เพิ่ม 4 VLFs
4	5,242,880	655,360	20	ขนาดที่ Growth มากกว่า 1/8 ของครั้งก่อนหน้า เพิ่ม 4 VLFs
5	6,291,456	786,432	24	ขนาดที่ Growth มากกว่า 1/8 ของครั้งก่อนหน้า เพิ่ม 4 VLFs
6	7,340,032	917,504	28	ขนาดที่ Growth มากกว่า 1/8 ของครั้งก่อนหน้า เพิ่ม 4 VLFs
7	8,388,608	1,048,576	32	ขนาดที่ Growth มากกว่า 1/8 ของครั้งก่อนหน้า เพิ่ม 4 VLFs
8	9,437,184	1,179,648	36	ขนาดที่ Growth เท่ากับ 1/8 ของครั้งก่อนหน้า เพิ่ม 4 VLFs
9	10,485,760	1,310,720	37	ขนาดที่ Growth น้อยกว่า 1/8 ของครั้งก่อนหน้า เพิ่ม 1 VLFs
10	11,534,336	1,441,792	38	ขนาดที่ Growth น้อยกว่า 1/8 ของครั้งก่อนหน้า เพิ่ม 1 VLFs

เพื่อเป็นการยืนยันว่าหาก AutoGrowth ทั้งสิ้น 10 ครั้ง จะมีจำนวน VLFs เท่ากับ 38 หรือไม่ สามารถทดสอบได้ด้วยสคริปต์เดิมคือ

USE X;
GO

DBCC LOGINFO(); --Previous SQL Server 2017

SELECT * FROM sys.dm_db_log_info(DB_ID()); --SQL Server 2017

เนื่องจากผลลัพธ์มีจำนวน 38 แถวข้อมูลตามจำนวน VLFs
ผู้เขียนขอเลือกมาเพียงสองคอลัมน์ที่ได้จาก Dynamic Management Function ชื่อ sys.dm_db_log_info

vlf_create_lsn	vlf_size_mb	Comment
00000000:00000000:0000	0.24	ฐานข้อมูลถูกสร้าง กำหนดขนาด Transaction Log = 1Mbytes จึงสร้าง 4 VLFs ขนาดแต่ละ VLF คือ 1/4 ≈ 0.25 Mbytes
00000000:00000000:0000	0.24
00000000:00000000:0000	0.24
00000000:00000000:0000	0.26
00000026:000001d1:0028	0.24	AutoGrowth ครั้งที่ 1 ขนาดที่ Growth คือ 1 Mbytes มากกว่า 1/8 ของขนาด Transaction Log File ก่อนหน้า จึงสร้าง 4 VLFs ขนาดแต่ละ VLF คือ 1/4 ≈ 0.25 Mbytes
00000026:000001d1:0028	0.24
00000026:000001d1:0028	0.24
00000026:000001d1:0028	0.27
0000002c:000001b2:0002	0.24	AutoGrowth ครั้งที่ 2 ขนาดที่ Growth คือ 1 Mbytes มากกว่า 1/8 ของขนาด Transaction Log File ก่อนหน้า จึงสร้าง 4 VLFs ขนาดแต่ละ VLF คือ 1/4 ≈ 0.25 Mbytes
0000002c:000001b2:0002	0.24
0000002c:000001b2:0002	0.24
0000002c:000001b2:0002	0.27
00000037:00000022:0002	0.24	AutoGrowth ครั้งที่ 3 ขนาดที่ Growth คือ 1 Mbytes มากกว่า 1/8 ของขนาด Transaction Log File ก่อนหน้า จึงสร้าง 4 VLFs ขนาดแต่ละ VLF คือ 1/4 ≈ 0.25 Mbytes
00000037:00000022:0002	0.24
00000037:00000022:0002	0.24
00000037:00000022:0002	0.27
00000045:000001b2:0002	0.24	AutoGrowth ครั้งที่ 4 ขนาดที่ Growth คือ 1 Mbytes มากกว่า 1/8 ของขนาด Transaction Log File ก่อนหน้า จึงสร้าง 4 VLFs ขนาดแต่ละ VLF คือ 1/4 ≈ 0.25 Mbytes
00000045:000001b2:0002	0.24
00000045:000001b2:0002	0.24
00000045:000001b2:0002	0.27
00000057:000001e2:0002	0.24	AutoGrowth ครั้งที่ 5 ขนาดที่ Growth คือ 1 Mbytes มากกว่า 1/8 ของขนาด Transaction Log File ก่อนหน้า จึงสร้าง 4 VLFs ขนาดแต่ละ VLF คือ 1/4 ≈ 0.25 Mbytes
00000057:000001e2:0002	0.24
00000057:000001e2:0002	0.24
00000057:000001e2:0002	0.27
0000006d:000001b2:0002	0.24	AutoGrowth ครั้งที่ 6 ขนาดที่ Growth คือ 1 Mbytes มากกว่า 1/8 ของขนาด Transaction Log File ก่อนหน้า จึงสร้าง 4 VLFs ขนาดแต่ละ VLF คือ 1/4 ≈ 0.25 Mbytes
0000006d:000001b2:0002	0.24
0000006d:000001b2:0002	0.24
0000006d:000001b2:0002	0.27
00000088:000001f2:0002	0.24	AutoGrowth ครั้งที่ 7 ขนาดที่ Growth คือ 1 Mbytes มากกว่า 1/8 ของขนาด Transaction Log File ก่อนหน้า จึงสร้าง 4 VLFs ขนาดแต่ละ VLF คือ 1/4 ≈ 0.25 Mbytes
00000088:000001f2:0002	0.24
00000088:000001f2:0002	0.24
00000088:000001f2:0002	0.27
000000a6:000001b2:0002	0.24	AutoGrowth ครั้งที่ 8 ขนาดที่ Growth คือ 1 Mbytes มากกว่า 1/8 ของขนาด Transaction Log File ก่อนหน้า จึงสร้าง 4 VLFs ขนาดแต่ละ VLF คือ 1/4 ≈ 0.25 Mbytes
000000a6:000001b2:0002	0.24
000000a6:000001b2:0002	0.24
000000a6:000001b2:0002	0.27
000000c8:000001b2:0002	1	AutoGrowth ครั้งที่ 9 ขนาดที่ Growth คือ 1 Mbytes น้อยกว่า 1/8 ของขนาด Transaction Log File ก่อนหน้า จึงสร้าง 1 VLFs ขนาดแต่ละ VLF คือ 1/1 = 1 Mbytes
000000ec:000001f2:0002	1	AutoGrowth ครั้งที่ 10 ขนาดที่ Growth คือ 1 Mbytes น้อยกว่า 1/8 ของขนาด Transaction Log File ก่อนหน้า จึงสร้าง 1 VLFs ขนาดแต่ละ VLF คือ 1/1 = 1 Mbytes

หรือหากจะนับเพียงจำนวน VLFs ผ่าน Dynamic Management Function ชื่อ sys.dm_db_log_info
โดยใช้ Aggregate Function ก็สามารถกระทำได้

USE X;
GO

SELECT COUNT(*) as VLFs_cnt FROM sys.dm_db_log_info(DB_ID());

ผลลัพธ์ที่ได้ก็เท่ากับ 38 เช่นกัน

จำนวน VLFs ในแต่ละฐานข้อมูลหากมีจำนวนมากเกินไป จะส่งผลกระทบต่อประสิทธิภาพ
ไม่ว่าจะเป็นเวลาที่ใช้ในการ Recovery เพราะขั้นตอนที่ 1 ในการ Recovery คือ Discovery
จะเป็นการสะแกนทุก ๆ VLFs หากจำนวน VLFs มีมากก็จะทำให้ขั้นตอนนี้ทำงานช้าลง

นอกเหนือจากนั้นทุก ๆ กิจกรรมที่เกี่ยวข้องกับ Transaction Log ไม่ว่าจะเป็นการปรับปรุงข้อมูล
หรือการ Backup ในส่วนของ Transaction Log ก็จะทำงานช้าลงไปด้วย

จำนวน VLFs ที่แนะนำไม่ควรเกิน 50 เราสามารถควบคุมจำนวน VLFs ได้ควบคุมจำนวนการเกิด AutoGrowth
ซึ่งสามารถทำได้โดย การกำหนดขนาด Transaction Log File ให้มีขนาดใหญ่เพียงพอ
และจากนั้นให้ทำการ Backup ในส่วนของ Transaction Log สม่ำเสมอ เพื่อให้เกิดการ Truncate
จากนั้นการบันทึกลง Transaction Log ก็จะเกิดการเวียนบันทึกในพื้นที่ที่ถูก Truncated ไป
เพียงเท่านี้ AutoGrowth ก็จะไม่เกิดหรือเกิดเพียงจำนวนน้อย

การแก้ไขจำนวน VLFs ที่มากเกินไป
หากจำนวน VLFs มีจำนวนเกินค่าที่แนะนำ เราสามารถแก้ไขให้มีจำนวนเหมาะสมได้
ก่อนอื่นให้เลือกเวลาที่ฐานข้อมูลไม่มีการใช้งานใด ๆ จากนั้น

ทำการ Backup ในส่วนของ Transaction Log เพื่อให้เกิดการ Truncate

BACKUP LOG X TO Device1 WITH MEDIANAME = 'MyMedia';

ปิด AutoGrowth บน Transaction Log File

USE master
GO

ALTER DATABASE X MODIFY FILE ( NAME = 'X_log', FILEGROWTH = 0);
GO

รันคำสั่ง DBCC SHRINKFILE โดยกำหนด Target Size เป็น 0 (ค่าที่ต่ำที่สุดที่สามารถกำหนดได้ แต่ SQL Server จะ Shrink file ได้เท่าที่สามารถทำได้)

USE X
GO

DBCC SHRINKFILE ('X_log', 0)
GO

ตรวจสอบขนาดปัจจุบัน และจำนวน VLFs ปัจจุบัน หลังจาก Shrink File ด้วยสคริปต์ต่อไปนี้

USE X;
GO

SELECT
                DB_NAME(lsu.database_id) AS DatabaseName
,               db.recovery_model_desc AS RecoveryModel
,               CAST(lsu.total_log_size_in_bytes/1048576.0 AS DECIMAL(10, 2)) AS [Total Log Space (MB)]
FROM sys.dm_db_log_space_usage AS lsu WITH (NOLOCK)
INNER JOIN sys.databases AS db WITH (NOLOCK)
ON lsu.database_id = db.database_id;

DBCC LOGINFO(); --Previous SQL Server 2017

SELECT * FROM sys.dm_db_log_info(DB_ID()); --SQL Server 2017

ผลลัพธ์ที่ได้พบว่าขนาดไฟล์ลดลงเหลือเพียง 1 Mbytes และจำนวน VLFs ลดลงเหลือเพียง 4 VLFs เท่านั้น

ให้ขยายขนาดของ Transaction Log File ให้มีขนาดเหมาะสม และพิจารณาไม่เปิดใช้ AutoGrowth อีก (ถ้าทำได้)

USE master;
GO

ALTER DATABASE X MODIFY FILE ( NAME = 'X_log', SIZE = 2GB, FILEGROWTH = 0 );
GO

เมื่อทำตามขั้นตอนที่แนะนำนี้แล้ว ก็จะได้ขนาดของ Transaction Log File ที่เหมาะสม และจำนวน VLFs ที่เหมาะสม
จากนี้ไปผู้อ่านก็เพียงแต่ตั้งเวลาให้มีการ Backup ในส่วนของ Transaction Log อย่างสม่ำเสมอ ก็จะขจัดปัญหาจำนวน VLFs ที่มากเกินไปได้หมดสิ้น

ส่วนการออกแบบทรัพยากรให้เหมาะสมกับ Transaction Log นั้นผู้เขียนจะนำมาเล่าให้ฟังในโอกาสต่อ ๆ ไป

บทความโดย

อาจารย์ภัคพงศ์ กฤตวัฒน์

วิทยากรผู้ดูแลและออกแบบหลักสูตร
- กลุ่มวิชา SQL Server/Window Server
Microsoft SQL Server Specialist
Microsoft Certified Trainer (2002-Present)
Co-Founder at Data Meccanica Co., Ltd.
- https://www.data-meccanica.com