SQL Server TLog Flush Size and Allocation Unit Size

The estimated reading time for this post is 1 minutes

It is been a while from my previous blog post, so in this blog post I would like to write about SQL Server transaction log flush size and what is the best allocation unit for transaction log disk drive.

The idea came from a discussion with one my friend – Special Thanks to Siavash Golchobian – regarding what is the best allocation unit size for Transaction log file as SQL Server holds the log records in 60KB log buffers and 112KB logpool buffers.Basically setting the allocation unit size too small will decrease the write performance due to increasing the IO operations, and setting the allocation unit size too big will cause to have lots of unused storage sector and at the end wasting lots of free spaces in our disk drive.

To come out the propose allocation unit sizing, it is better to test the SQL Server environment with the real-world workload, and monitor the SQL Server behavior towards that. To perform this task, I am going to use Sysinternals to monitor the SQL Server process.

I will run the following script to test the SQL Server behavior on my machine:-

INSERT INTO StressDB.dbo.StressData DEFAULT VALUES
Go 100000000000

 

Now it is time to run the ProcMon.exe tool from Sysinternals, and configure the necessary options, as the following figure:-

image-2469

As you see, the ProcMon.exe shows that SQL Server flushes every 1KB to the LDF file based on the given workload. Lets try the following workload:-

INSERT INTO StressDB.dbo.StressData(RecordPadding) 
SELECT TOP (2) PERCENT RecordPadding FROM StressDB.dbo.StressData;

 

image-2470

As you see the above figure, SQL Server flushes the log buffer every 60KB to the LDF file, even though SQL Server 2016 is installed on my machine.

Conclusion

Due to Disk sector and SQL Server log flushing behavior, I still recommend allocation unit size either 4KB or 8KB in production environments and make sure the LDF file is manually expanded to desired file size.

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz