{"id":96625,"date":"2023-05-15T10:30:23","date_gmt":"2023-05-15T10:30:23","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=96625"},"modified":"2023-05-09T13:50:55","modified_gmt":"2023-05-09T13:50:55","slug":"log-file-instant-file-initialization","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/log-file-instant-file-initialization\/","title":{"rendered":"Changing log growth strategy in SQL Server 2022"},"content":{"rendered":"

When I first saw a bullet item stating SQL Server 2022 would support instant file initialization<\/a> for log file growth, I was excited. When I later learned it only applies to automatic<\/em> growths, and only those of 64 MB or less, I was a little less excited. With those limitations, I was skeptical this enhancement could supplant my long-standing practice of using 1 GB autogrowth for log files – at least ever since SSDs and other modern storage became more commonplace.<\/p>\n

But after playing with it, I’m a believer<\/strong>.<\/p>\n

Since the advent of the instant file initialization feature, data<\/em> file growths are nearly instantaneous, because the newly-allocated space can be created empty. When log<\/em> files grow, on the other hand, the new space must be zero-initialized to make sure SQL Server uses the transaction log correctly, as Paul Randal explains in Why can\u2019t the transaction log use instant file initialization?<\/a><\/p>\n

PREEMPTIVE_OS_WRITEFILEGATHER<\/code> is the wait type you’ll see if a session is waiting on log file initialization. You can check if an instance is generally suffering this pain by comparing against other prevalent waits<\/a> in your workload, by checking sys.dm_os_wait_stats<\/code>:<\/p>\n

 SELECT wait_type, waiting_tasks_count, wait_time_ms\r\n   FROM sys.dm_os_wait_stats\r\n   WHERE wait_type = N'PREEMPTIVE_OS_WRITEFILEGATHER';<\/pre>\n
In the days of spinning rust platters, growing a log file was extremely painful, particularly in highly concurrent, write-heavy workloads. For most of my career, this alone has cast log files in general in a negative light. Even on fast and modern storage, log file expansion can be quite disruptive, because all transactions need to wait on any file growth operations.<\/p>\n
In most cases, best practice has dictated that you just size your log files as large as possible, to avoid any unexpected growth events. But this isn’t always possible – on systems with many databases, for example, you only have so many drives, and you can’t size all<\/em> of your log files to fill the disk up front. It is also sometimes difficult to predict which ones will grow more – or in more spiky ways – over time. In these cases, a smaller autogrowth setting for log files can still be a good strategy today, even without this enhancement.<\/p>\n
But let’s see the impact of the change<\/b>.<\/p>\n
On two different instances on the same machine, I’ll create the following four databases, each with an 8 GB data file and 32 MB log file – all on the same drive. The differences will just be the version and the log file’s filegrowth<\/code> setting:<\/p>\n