SQL Server 2022: что можно сделать с VLF? / Habr

Виртуальные файлы журналов (Virtual Log Files – VLF) — это внутреннее разбиение реальных физических файлов, которое создает Database Engine SQL Server для работы с журналом транзакций. У VLF нет четко определенного размера, и их количество на один физический файл не является фиксированным. VLF создаются компонентом Database Engine во время создания или расширения файлов журналов. Размер VLF рассчитывается динамически на основе размеров существующего журнала и нового файла приращения. За VLF, однако, числится дурная слава, которая заключается в том, что слишком большое количество VLF может вызвать ряд проблем, среди которых можно выделить существенное снижение скорости запуска базы данных, операции резервного копирования и восстановления журналов. Чем больше VLF было сгененрировано, тем дольше процесс восстановления базы данных, что может привести к превышению времени ожидания, ошибкам, связанным с памятью, и целому ряду других проблемам.

Оптимальное разделение на VLF в SQL Server 2022

Виртуальные файлы журналов (VLF) являются способом взаимодействия с физическими журналами транзакций в SQL Server. В предыдущих версиях, если файл журнала увеличивался менее чем на 1/8 от его текущего размера, чтобы покрыть весь объем приращения, создавался только один VLF. Если приращение оказывалось больше, то создавалось несколько VLF в зависимости от размера приращения. Однако SQL Server 2022 внедрил новый подход. Если приращение журнала меньше или равно 64 МБ, создается только один VLF, который покроет его целиком. При приращении от 64 МБ до 1 ГБ создается 8 виртуальных файлов, а если увеличение более чем на 1 ГБ создается 16 виртуальных файлов для охвата всего объема новой информации. Этот новый метод призван оптимизировать создание VLF и повысить производительности базы данных.

Мониторинг VLF

В примере ниже продемонстрирован SQL-код, который можно использовать для получения информации о количестве виртуальных файлов журнала (VLF) в базе данных:

USE YourDatabaseName;
GO
DBCC LOGINFO;

Эта команда выведет информацию о количестве и статусе VLF в текущей базе данных. В выходных данных будет по строке на каждый VLF с такими столбцами, как FileId, FileOffset и Status. В столбце Status будет указано, является ли VLF активным (2), неактивным (0) или частично используемым VLF (1).

Также для отслеживания количества VLF с течением времени можно использовать следующий SQL-код:

SELECT name, log_reuse_wait_desc, database_id,
log_reuse_wait, log_size_in_bytes/1024/1024 AS log_size_mb,
log_size_in_bytes/1024/1024/log_file_count AS avg_vlf_size_mb, 
log_file_count
FROM sys.dm_db_log_space_usage;

Этот запрос вернет информацию о файле журнала, используемом для каждой базы данных на сервере, включая количество VLF и средний размер каждого VLF. Столбец log_reuse_wait_desc будет указывать, почему файл журнала не используется, например, из-за ожидания резервной копии или ожидания создания контрольной точки. На основе этой информации вы можете определить базы данных, у которых слишком много VLF, и другие проблемы, связанные с использованием файла журнала.

Устранение проблемы с VLF

Ниже приведен пример SQL-кода, который призван решать проблему со слишком большим количеством VLF в SQL Server:

— Уменьшаем размер файла журнала транзакций вручную:

DBCC SHRINKFILE('<logical file name of transaction log>', TRUNCATEONLY);

— Увеличиваем размер файл журнала транзакций до необходимого вручную:

ALTER DATABASE <database name> 
MODIFY FILE (NAME='<logical file name of transaction log>', 
SIZE = <required size>);

— Выводим и по необходимости изменяем параметры автоматического увеличения файла журнала транзакций:

ALTER DATABASE <database name> 
MODIFY FILE (NAME='<logical file name of transaction log>', 
FILEGROWTH = <new growth size>);

Обратите внимание, что вы должны заменить <logical file name of transaction log>, <database name>, <required size> и <new growth size> соответствующими значениями для вашего конкретного случая. Кроме того, как упоминалось ранее, перед выполнением любой из этих операций убедитесь, что у вас есть актуальная восстанавливаемая резервная копия.

Лучшие практики

Также стоит отметить, что уже успели сформироваться лучшие практики управления VLF, которым вы должны следовать, чтобы избежать проблем с производительностью. Среди этих практик я бы выделил следующие:

Размер файлов журналов должен соответствовать вашей рабочей нагрузке, чтобы избежать частых событий прироста.
Установите значение Growth_increment меньшем или равным 64 МБ, чтобы уменьшить количество VLF, созданных во время роста журнала.
Мониторьте свои VLF на регулярной основе, используя продемонстрированные здесь SQL-запросы, для выявления проблем, таких как слишком большое количество VLF или длительное время восстановления.

В заключение можно сказать, что управление VLF — это критически важный аспект администрирования базы данных SQL Server, который может значительно повлиять на производительность базы данных. Благодаря усовершенствованиям, внесенным в SQL Server 2022, и использованию лучших практик администраторы могут оптимизировать разделение на VLF, сократив их количество, что приведет к сокращению времени запуска базы данных, ускорению операций резервного копирования и восстановления и повышению общего удобства для конечных пользователей.

Материал подготовлен в преддверии старта онлайн-курса "MS SQL Server Developer".