February 19, 2010 at 3:35 am
Log File writing is Sequential so normally it is advisable to create only ONE log file and to place it on faster disk for better performance.
We have a application which is inserting too too many records into tables. Every insert will write into the log fille and our transaction completed duration goes up due to HIGH LOG WRITE WAIT. (PerfMon counter)
I feel that in such above unique scnario we should create multiple log file on different disk which will surely slow down bit log writing but REDUCE log write wait.
It is NOT possible to simulate such scnario in UAT environment so I needs expert advice before creating additional LOG file on another disk in live environment.
February 19, 2010 at 8:33 am
That is not going to help your situation. Adding more log files won't have any affect because SQL Server writes to the log file sequentially.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 19, 2010 at 10:37 am
Is your log file on a disk that is experiencing a lot of other write activity? It is always recommended to keep your data files and log files on separate disks in order to reduce IO contention. But this also means keep your log files off of any disk that is receiving heavy write activity from any other services as well. I haven't seen too many issues where the database is "writing too many records to the log file". In fact, since the log file writes sequentially, you can't get a whole lot faster with regards to writes and I would think that you would get problems on your data disks first. Unless of course your disk is having to constantly move the write head from one place to another in order to service multiple processes or too flush data to disk as could be the case if they your log and data files share the same disks.
Tim Januario
February 19, 2010 at 9:49 pm
Jeffery and Tim,
Thanks for your response.
Please note that
1. Log file is on it's own dedicated disk.
2. Reg. Log file write is Sequential: I have created two log file on
seperate dedicated physical drive and my log load was
distributed to two files. Initial log file size was 100 MB and
after the heavy insert operation, both log files grow to 500
MB. THis is because writing into both the log files.
I understand that writing in the log file is sequential but because it is very very heavy insert into the multiple tables so when writing into LOG file, the LOG FILE WAIT goes up.
What I am trying to achieve?
By creating multiple log files, I am trying to distribute this load.
In case of two log files (ON DIFFERENT DEDICATED DISK) it will write half of the log into first and other half on the second. I understand that splitting has some overhead.
Now the write load will be half on each disk which should reduce LOG FILE WAIT because each disk load is reduce by half.
February 19, 2010 at 9:57 pm
Paresh Randeria (2/19/2010)
Log file is on it's own dedicated disk.I understand that writing in the log file is sequential but because it is very very heavy insert into the multiple tables so when writing into LOG file, the LOG FILE WAIT goes up.
What I am trying to achieve?
By creating multiple log files, I am trying to distribute this load.
In case of two log files (ON DIFFERENT DEDICATED DISK) it will write half of the log into first and other half on the second. I understand that splitting has some overhead.
Now the write load will be half on each disk which should reduce LOG FILE WAIT because each disk load is reduce by half.
No, writing to the log file is sequential - so it will write to the first log file until it gets to the end of that file and then start writing to the second log file. When it gets to the end of that file the log file with either grow - or it will roll back over to the first log file.
It will NEVER write to both log files at the same time.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 20, 2010 at 12:41 am
Multiple log files does not reduce log waits. The files are used sequentially, not in parallel. Having 2 200MB log files is no different from 1 400MB log file.
What's the RAID level on the disk that the log is on?
Dedicated physical disk? (no portions shared with anything else?
SAN, iSCSI, direct attached disk?
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
February 21, 2010 at 3:56 am
You could also look into why the SQL Server is generating so much log activity. There might be a quick win there.
In an ideal world, the DBA would just gather evidence that the server is being held up by log waits, and pass that to your storage professional for her to make the appropriate adjustments. Sadly, many places do not have a suitably skilled person, even in a SAN environment.
If your 'dedicated' log drive is on a SAN, be sure that someone with the appropriate knowledge checks to see what other drives (LUNs) are using the same controllers and/or physical disks inside the SAN.
This almost has to be a case of over-logging (which the DBA can fix) or poor disk configuration. On a properly configured system, it is pretty tough to generate waits on sequential log writes. Not impossible, but tough.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 21, 2010 at 2:01 pm
Here's a recent blog by Paul Randal which might help you in your situation:http://feedproxy.google.com/~r/PaulSRandal/~3/FAP0XwEkE4Y/post.aspx. (check the next one in that series also).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 21, 2010 at 10:49 pm
RBarryYoung (2/21/2010)
Here's a recent blog by Paul Randal which might help you in your situation:http://feedproxy.google.com/~r/PaulSRandal/~3/FAP0XwEkE4Y/post.aspx. (check the next one in that series also).
Yes, it is an excellent series. I hope Paresh doesn't get bogged-down in the detail though. There are some simple points to take away, and frequently a much less detailed analysis is required to achieve the traditional 80% improvement.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 22, 2010 at 1:38 am
Paul White (2/21/2010)
RBarryYoung (2/21/2010)
Here's a recent blog by Paul Randal which might help you in your situation:http://feedproxy.google.com/~r/PaulSRandal/~3/FAP0XwEkE4Y/post.aspx. (check the next one in that series also).Yes, it is an excellent series. I hope Paresh doesn't get bogged-down in the detail though. There are some simple points to take away, and frequently a much less detailed analysis is required to achieve the traditional 80% improvement.
Paul
I pointed to this specific one by Paul Randal because it is one of the very few articles that I have read that directly addressed Log file performance. If I knew of a simpler version, I certainly would have pointed to that instead, but its not an area of particular expertise for me. Maybe you could write up something for Steve, Paul? 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 22, 2010 at 2:00 am
Steve can't afford me :laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 22, 2010 at 4:04 am
Jeffrey, Gila and Paul, Thanks for your response too.
RBarryYoung, Thanks for your response and article pointer.
Now it's clear to me that adding log file will not help. Will focussed on SAN and SQL Query.
Thanks again to All.
February 22, 2010 at 6:31 am
Glad we could help!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply