6

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.

Improve this question
1
  • writes are tricky to consistently measure with "eyeball analytics" on a SAN because most of them are cached writes.
    – swasheck
    Apr 2, 2014 at 15:06

3 Answers 3

Reset to default
9

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

Also Kimberly Tripp touches on this in a worthwhile article:

Note that none of the 8 steps involves adding a transaction log file. In fact she recommends against it.

There are other perils to having multiple log files, particularly if they are large (think RTO) - and there really is nothing to gain.

Improve this answer
2
  • The only criteria for the system to write logs to the 2nd log file is that the 1st log file is full or if the size is unlimited then the disk is full. Correct?
    – variable
    Aug 11, 2021 at 17:04
  • Yes thank you I understand that in simple recovery model the log truncation happens automatically at each checkpoint and that in full/bulk mode the log truncation happens at every log backup. I also know that log truncation means marking records as inactive so the log space can be reused.
    – variable
    Aug 12, 2021 at 3:26
4

Transaction log files are written to and used in a sequential manner. This can be observed by using DBCC LOGINFO to examine the use of Virtual Log Files(VLFs) within your log file. For example, if I have a database and a table, where I insert 1000 rows into it, DBCC LOGINFO will provide the following:

RecoveryUnitId FileId      FileSize             StartOffset          FSeqNo      Status      Parity CreateLSN
-------------- ----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
0              2           3866624              8192                 32          2           64     0
0              2           3866624              3874816              33          2           64     0
0              2           3866624              7741440              34          2           64     0
0              2           4120576              11608064             0           0           0      0

The important columns to note are:

  • FSeqNo - This is the sequential order VLFs are used in
  • Status - Current status of the VLF. 2 means active, 0 means ready for re-use

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 DBCC LOGINFO from a database with two log files:

RecoveryUnitId FileId      FileSize             StartOffset          FSeqNo      Status      Parity CreateLSN
-------------- ----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
0              2           3866624              8192                 32          0           64     0
0              2           3866624              3874816              33          0           64     0
0              2           3866624              7741440              34          0           64     0
0              2           4120576              11608064             35          0           64     0
0              3           3866624              8192                 36          2           64     0
0              3           3866624              3874816              37          2           64     0
0              3           3866624              7741440              38          2           64     0
0              3           4120576              11608064             39          2           64     0

You can correlate the FSeqNo and Status columns with FileId (which is the same FileID that you find in sys.database_files). Notice the FSeqNo spans and maintains order across the files.

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.

Improve this answer
3

Your initial thoughts were correct: There is no benefit to having multiple transaction log files. SQL Server utilizes the transaction logs sequentially, not simultaneously.

why would increasing the number of log files end up have a very noticeable improvement on the Write speed to the Disk?

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.

Improve this answer
7
  • Doesn't really answer the question on why we see such a large difference in Writes to the disk when the # of files is increased. This is the same as I've seen when doing google searches on the matter, as they say the proof is in the pudding, there is something going on deep in the SQL code that just doesn't jive with the results seen.
    – MBulava
    Apr 2, 2014 at 14:36
  • 1
    It does answer the question on why you see different I/O. The point is, having multiple transaction logs is contributing absolutely nothing to your differing I/O stats.
    – Thomas Stringer
    Apr 2, 2014 at 14:37
  • 2
    @MBulava How can we possibly answer that question? We can't see your system, so we have absolutely no idea what other factors may be contributing to your observations. Knowing how SQL Server works, though, we can rule out your assumption that this all came magically from adding log files.
    – Aaron Bertrand
    Apr 2, 2014 at 14:39
  • I understand you can't see the system heck I can't even see all the details of the SAN subsystems myself, very large organization with very strict rules on separations of duties. However the Disk as I see in Windows is a single disk how many LUNS and where they are located in the frame are completely unknown to me. I get what you're saying, I guess it possible that one or more of the newer log files is on a Disk that Physically closer to the Server or on a faster Disk, and that would be the difference or traffic to the SAN decreased when we saw the jump in throughput.
    – MBulava
    Apr 2, 2014 at 14:50
  • 1
    @MBulava Great, but certainly other things could have been different. (a) log activity could have moved on to faster disk, bigger file, file with more space, etc. (b) log growth maybe wasn't necessary any more (jumping log files is cheaper than growing to make room). Providing more log space to avoid autogrow could also, of course, be accomplished by making the original file larger.
    – Aaron Bertrand
    Apr 2, 2014 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.