Новое в SQL Server 2022: изменение стратегии прироста журналов транзакций

Автор оригинала: Aaron Bertrand

Когда я впервые узнал о том, что SQL Server 2022 будет поддерживать мгновенную инициализацию файлов журнала транзакций, я был заинтригован. Когда позже выяснилось, что это относится только к автоматическому приращению и только с размером не больше 64 МБ, интрига несколько потеряла свой «блеск». Из-за этих ограничений я скептически отнесся к тому, что новая стратегия сможет заменить сложившуюся практику автоматического увеличения файлов журналов на 1 ГБ — по крайней мере, с тех пор, как SSD и другие современные хранилища получили достаточное распространение.

Но поигравшись с этим новшеством, я проникся.

С появлением функции мгновенной инициализации прироста файла данных это происходит практически мгновенно, поскольку выделенное для приращения файла дисковое пространство не изменяется, если оказывается не пустым. С другой стороны, когда приращиваются файлы журнала транзакций, новое пространство из соображений безопасности забивается нулями, как это объясняет Пол Рэндал в статье: Search Engine Q&A #24: Why can’t the transaction log use instant initialization?

PREEMPTIVE_OS_WRITEFILEGATHER — это тот тип ожидания, который вы увидите, если сеанс ожидает инициализации файла журнала. Вы можете проверить, является ли это проблемой на вашем экземпляре, сравнив его с другими распространенными типами ожиданий в вашей рабочей нагрузке, всё это видно в sys. dm_os_wait_stats:

SELECT wait_type, waiting_tasks_count, wait_time_ms FROM sys.dm_os_wait_stats WHERE wait_type = N'PREEMPTIVE_OS_WRITEFILEGATHER';

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

В большинстве случаев в соответствии с общепринятой практикой вы просто увеличиваете файлы журналов до максимально возможного, но разумного размера, чтобы избежать любых непредвиденных событий роста. Но это не всегда возможно — например, в системах с большим количеством баз данных у вас есть ограниченное дисковое пространство, и вы не можете увеличить размеры всех ваших файлов журналов, чтобы заполнить диск заранее. Также иногда трудно предсказать, какие из них со временем вырастут больше или будут доставлять больше хлопот. В этих случаях сравнительно небольшой размер автоматического приращения файлов журналов может быть достаточно хорошей стратегией, даже без этого улучшения.

В двух разных экземплярах на одном компьютере я создал следующие четыре базы данных, каждая с файлом данных размером 8 ГБ и файлом журнала транзакций размером 32 МБ — все на одном диске. Различия заключаются только в версии и настройке файла журнала «filegrowth»:

SQL Server 2019:

1 GB autogrow
64 MB autogrow

SQL Server 2022:

1 GB autogrow
64 MB autogrow

/* values in brackets should all be set before running script {2019|2022} = version; {64|1024} = log file size */ CREATE DATABASE ifi_{64|1024} ON ( name = N'ifi_data', filename = 'D:\data\ifi_{2019|2022}_{64|1024}.mdf', size = 8192MB, filegrowth = 2048MB ) LOG ON ( name = N'ifi_log', filename = 'D:\data\ifi_{2019|2022}_{64|1024}.ldf', size = 32MB, filegrowth = {64|1024}MB );

Затем я создал простую, но достаточно объёмную рабочую нагрузку, которую направил на каждую из созданных баз данных, и измерял следующие метрики:

продолжительность выполнения нагрузки
общую длительность ожидания preemptive_OS_writefilegather
количество событий прироста журнала
общее количество VLF
средний размер VLF
результирующий размер файла журнала

Сценарий создания рабочей нагрузки довольно прост:

DROP TABLE IF EXISTS dbo.dummy; SELECT TOP (0) ID = IDENTITY(int,1,1), name = CONVERT(nchar(2048), N'x') INTO dbo.dummy; CREATE UNIQUE CLUSTERED INDEX CIX_dummy ON dbo.dummy(ID); INSERT dbo.dummy(name) SELECT TOP (500000) N'x' FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2;

Вы можете отслеживать влияние этой «рабочей нагрузки» несколькими способами, например, запрашивая sys. dm_os_wait_stats по аналогии с приведенным выше примером, или создав Extended Events session, которая будет включать следующее:

CREATE EVENT SESSION [log_size_changes] ON SERVER ADD EVENT sqlos.wait_info_external (WHERE ([wait_type] = N'PREEMPTIVE_OS_WRITEFILEGATHER')), ADD EVENT sqlserver.database_file_size_change ADD TARGET package0.event_file(SET filename = N'ifi_log_size_changes') /* ... */

Тут стоит обратить внимание на то, что продолжительность одних событий указана в микросекундах, а других — в миллисекундах.

А ещё можно запросить трассировку по умолчанию, если она у вас все ещё включена:

SELECT t.DatabaseName, GrowthEvents = COUNT(*), AverageDuration = AVG(t.Duration/1000.0), TotalDuration = SUM(t.Duration/1000.0) FROM ( SELECT [path] = REVERSE(SUBSTRING(p, CHARINDEX(N'\', p), 260)) + N'log.trc' FROM (SELECT REVERSE([path]) FROM sys.traces WHERE is_default = 1) AS s(p) ) AS p CROSS APPLY sys.fn_trace_gettable(p.[path], DEFAULT) AS t WHERE t.EventClass = 93 GROUP BY t.DatabaseName;

И заглянув в sys. dm_db_log_info можно получить информацию о количестве виртуальных файлов журнала и их размерах:

SELECT d.name, VLFCount = COUNT(li.database_id), AvgSizeMB = AVG(li.vlf_size_mb) FROM sys.databases AS d CROSS APPLY sys.dm_db_log_info(d.database_id) AS li GROUP BY d.name;

После запуска рабочей нагрузки на каждой базе данных я зафиксировал следующее:

Новое в SQL Server 2022: изменение стратегии прироста журналов транзакций

Цитата из официальной документации:
Журнал виртуальных файлов (VLF) — это абстракция физического журнала транзакций. Наличие большого количества небольших VLFs на основе роста журнала может повлиять на производительность таких операций, как восстановление. Мы изменили алгоритм для того, сколько файлов VLF мы создаем во время определенных сценариев роста журнала. Дополнительные сведения об изменении этого алгоритма в SQL Server 2022 (16.x) см. в статье "Файлы виртуальных журналов(VLFs)". Это изменение было введено в SQL Server 2022 (все выпуски) и включено в базу данных SQL Azure. (Примечание переводчика: Перевод машинный, не чертыхайтесь. На английской странице текст немного другой, более точный: «В предыдущих версиях SQL Server, если следующий прирост превышает 1/8 текущего размера журнала, и составляет менее 64 МБ, создавалось четыре VLF. В SQL Server 2022 создается только один VLF, если рост меньше или равен 64 МБ и превышает 1/8 текущего размера журнала».)

Это приятные и многообещающие результаты —продолжительность рабочей нагрузки сократилась примерно на 20%, и это только из-за перехода на автоматическое приращение журнала на 64 МБ. Достигнуто это благодаря устранению ожиданий preemptive_OS_writefilegather при использовании мгновенной инициализации файлов. Кроме того, мы видим небольшую выгоду и от улучшенного алгоритма управления VLF (недавно озвученного), что приводит к уменьшению количества VLF для точно таких же событий прироста журнала.

Теперь в вашем распоряжении больше вариантов и возможностей. На более мощном «железе» процент времени, затрачиваемого на инициализацию, может быть ниже, поэтому преимущество может быть менее заметным. Тем не менее, обнуление прибавляемой частифайла журнала никогда не будет мгновенным для приращений больше 64 МБ. Итак, когда вы планируете установку SQL Server 2022 или даже успели его развернуть, этот вариант конфигурации стоит протестировать на вашей рабочей нагрузке и навашем оборудовании. Это особенно полезно, если вы не можете заранее достаточносильно увеличить размер файлов, чтобы приращение не понадобилось. В концеконцов, самое быстрое приращение файла — это то, которое вообще не должнопроисходить; следующим по быстроте является тот вариант, который позволяетполучить использование мгновенной инициализации файла.

Новое в SQL Server 2022: изменение стратегии прироста журналов транзакций

Давайте посмотрим на влияние новшества