So I've gotten into an argument with several people regarding the number of transaction log files a give databases should have. I've seen several post saying you should only have 1 transaction log file for each database, nothing in any Microsoft whitepapers though. However in many cases I've seen that increasing the number of transaction log files for a given database will actually improve the write performance to the database. I should note that all these databases are in Full recovery model and are using a large SAN frame for the I/O sub system. If as many of the posts say that the transaction writes all happen serially to the one file until the end of the file is reached then the writes move on to the subsequent log file, why would increasing the number of log files end up have a very noticeable improvement on the Write speed to the Disk? In the most recent case we saw IO jump from 700 Kb/s to more than 60 Mb/s by increasing the number of log files from 1 to 8. Any input would be appreciated.
Transaction log writes are sequential. Only one of the log files will ever be written to at any one time, so having multiple files - in and of itself - can't possibly change your I/O patterns for that database.
Unless you are getting lucky. For example, you've added a second log file to an SSD or otherwise faster or less busy disk, or split the log files across multiple disks and have done so for multiple databases, and you are observing I/O now because the log has switched to that file on the faster disk, or is more isolated from your other data/log files. In other words, I believe any observed I/O difference is due entirely to other factors and is merely a coincidence, not due to the fact that you added log files alone. SQL Server is explicitly designed to only use one log file at a time - so how could multiple log files possibly improve log write performance, unless the current log file is on faster / more isolated disk? I think you need to provide better empirical evidence (and in doing so you may discover for yourself the true cause of the improved performance).
Please read these posts in full - they were written by a pretty smart guy who worked on the SQL Server storage team for quite some time, so I don't think he's making any of this stuff up for fun:
...there are other perils to having multiple log files, particularly if they are large (think RTO) - and there really is nothing to gain.
Your initial thoughts were correct: There is no benefit to having multiple transaction log files. SQL Server utilizes the transaction logs sequentially, not simultaneously.
It wouldn't. There simply must be another factor involved that hasn't been accounted for (i.e. are you simply pushing more logged transactions for the higher throughput, causing actual higher throughput? Are the added transaction log files on faster storage than the single log file, causing the current transaction log file in the multiple test to out-perform the single transaction log in the initial test?).
The only time where it would make sense to have more than one transaction log file would be during an emergency situation where you need more transaction log space, and the current single transaction log can't grow (volume out of space, etc.). In this case, it'll get you in the clear, but having multiple transaction logs would be a very temporary state. It would be wise to clean that up and revert back to a single transaction log as soon as possible in a situation like that.
Transaction log files are written to and used in a sequential manner. This can be observed by using
The important columns to note are:
The engine can only use one VLF at a time and will use available VLFs in order until it reaches the end of the file. Once it does, it will attempt to return to the first available VLF in the file. If one is not available, the log file will then grow.
With multiple files, this order is maintained. VLFs in the second (or third or fourth) can not be used until all the VLFs in the preceding files have been used. For example, here's a
You can correlate the FSeqNo and Status columns with FileId (which is the same FileID that you find in
This is why multiple log files will not help you. The reason for multiple data files is so you can distribute IO streams to these files, but you can get no such benefit from the log file since everything done to it is in a round-robin, sequential manner.