SQL Server TLog Flush Size and Allocation Unit Size

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:-

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;

 

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.

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

guest
0 Comments
Inline Feedbacks
View all comments

About The Author

Search Articles

Categories

Follow Fard Solutions