Когда я впервые узнал о том, что 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;

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

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

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