It is also sometimes difficult to predict which ones will grow more – or in more spiky ways – over time. 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 of your log files to fill the disk up front. In most cases, best practice has dictated that you just size your log files as large as possible, to avoid any unexpected growth events. Even on fast and modern storage, log file expansion can be quite disruptive, because all transactions need to wait on any file growth operations. For most of my career, this alone has cast log files in general in a negative light. In the days of spinning rust platters, growing a log file was extremely painful, particularly in highly concurrent, write-heavy workloads. You can check if an instance is generally suffering this pain by comparing against other prevalent waits in your workload, by checking sys.dm_os_wait_stats: PREEMPTIVE_OS_WRITEFILEGATHER is the wait type you’ll see if a session is waiting on log file initialization. When log 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’t the transaction log use instant file initialization? Since the advent of the instant file initialization feature, data file growths are nearly instantaneous, because the newly-allocated space can be created empty. 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.īut after playing with it, I’m a believer. When I later learned it only applies to automatic growths, and only those of 64 MB or less, I was a little less excited. When I first saw a bullet item stating SQL Server 2022 would support instant file initialization for log file growth, I was excited.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |