How High VLF Counts Kill Your SQL Server Performance

How a High VLF Count Kills SQL Server Performance

Category: Performance

What Is a Virtual Log File (VLF)?

A transaction log file is physically divided internally into several log files (VLFs).

This number can grow depending on how often active transactions write to disk and the autogrowth settings on the log file.

Backups run slower when VLF counts are high.

Some T-SQL operations, like UPDATE/DELETE, take longer.

The SQL Engine service takes longer to start. Replication, AlwaysOn, mirroring, log shipping, and other operations — all suffer.

How Do You Identify the Problem?

You can get information about the VLF count using DBCC LOGINFO.

For newer SQL Server versions (SQL Server 2016 SP2 and later), you can use the following query, which relies on SQL Dynamic Management Functions (DMFs).

SELECT [name] AS 'Database Name',

COUNT(l.database_id) AS 'VLF Count',

SUM(CAST(vlf_active AS INT)) AS 'Active VLF',

COUNT(l.database_id)-SUM(CAST(vlf_active AS INT)) AS 'Inactive VLF',

SUM(vlf_size_mb) AS 'VLF Size (MB)',

SUM(vlf_active*vlf_size_mb) AS 'Active VLF Size (MB)',

SUM(vlf_size_mb)-SUM(vlf_active*vlf_size_mb) AS 'Inactive VLF Size (MB)'

FROM sys.databases s

CROSS APPLY sys.dm_db_log_info(s.database_id) l

GROUP BY [name]

ORDER BY COUNT(l.database_id)

When VLF count is under 50 — you can ignore it.

Between 100 and 200 — you can ignore it, but it’s better to fix it.

Above 400 — it becomes urgent, so fix it.

Above 600 — slowdowns start happening, but they’re not easy to diagnose.

Above 5000 — fix it immediately.

How Do You Fix It?

1. Fix the database’s default autogrowth settings.
2. Shrink the transaction log files, then pre-grow them to the correct size.