October 25, 2012 at 3:09 pm
So I've been doing some research. According to a lot of forums and user groups only one tlog file is needed per database. Because they are written sequentially having multiple log files will not improve performance.
However when I run some tests on a highly transactional db, and I add another log file on a different volume then the query performance in terms of writes is significantly faster.
Can someone comment on this?
October 25, 2012 at 3:30 pm
Luk (10/25/2012)
So I've been doing some research. According to a lot of forums and user groups only one tlog file is needed per database. Because they are written sequentially having multiple log files will not improve performance.
That is correct. Only one log file is written to at a time, sequential writes, never parallel.
However when I run some tests on a highly transactional db, and I add another log file on a different volume then the query performance in terms of writes is significantly faster.
How are you measuring?
What has improved?
Is either log file sharing with the data file? TempDB?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 25, 2012 at 3:51 pm
Each proc that runs returns the amount of time it took to complete and saves this information to the audit table. With a single log file the execution is slower.
So I'm using an audit table to compare run times. I have two mirror environments, configured the same in terms of hardware. The only difference is the SQL config, where the second box has multiple tlog files.
Also I'm seeing that two log files are growing more in parallel. I'm not sure if it's quickly switching between the two or if it's actually doing parallel work.
October 25, 2012 at 5:12 pm
If both logs are full, it'll autogrow one, then the other, repeat. SQL does not ever write log records in parallel to multiple log files.
As for the tests, can you tell for sure that it's time from the log writes? Are either of the log files sharing a drive with the data file or anything else (not just drive, entire IO channel)? Are you running multiple tests, ignoring the first and ensuring that the differences are statistically significant?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 25, 2012 at 10:21 pm
The log files are on two different drives. It's native storage in RAID1 config. The data files are located on different drive and that's RAID5. We're seeing run times significantly better. A proc that with a single log file takes 50 minutes to execute will run for about 35 minutes on a system with two log files.
October 26, 2012 at 2:56 am
Without seeing more details (tests, waits, exec plans, time breakdown), I can't begin to tell you why, however the fact remains, log files are not written to in parallel.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 26, 2012 at 3:17 am
Are you sure the one server isn't just busier than the other and that's the reason why things take longer?
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
October 26, 2012 at 7:04 am
Luk (10/25/2012)
The log files are on two different drives.
I'm guessing the two drives are identical but can you confirm that? If one drive is faster then that would explain it.
Have you tried growing the log file to the size it needs to grow to before running the proc? That won't explain why two log files is faster but the proc will run faster if it doesn't need to wait for the grow events. And if after that the two systems have the same run time we know it's something to do with the grow events.
October 26, 2012 at 7:07 am
What if you reverse the configuration? If you change which server has multiple log files, does the performance on the servers stay the same or does it also switch to the other server. This would rule out there being some difference at the storage layer.
October 26, 2012 at 7:54 am
What about autogrowths? Perhaps you are stalling out regularly due to those on server 1 but with server 2 having more free space in tlog you aren't autogrowing (which MUST be zeroed out before being used even if Instant File Initialization is enabled)?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 26, 2012 at 8:07 am
Try repeating your test, but ensure that the single log file is >> max log space needed by your test and that the two log files on the other server are half the size of the single log file.
Basically, ensure that you have more than enough log space for the entire test and that the two cases you have the same amount of log space in total. Otherwise one case will have more autogrows than the other, which will have an impact on time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply