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