SQL Server Performance

WRITELOG wait

Discussion in 'Performance Tuning for DBAs' started by cchitanu, Apr 28, 2004.

  1. cchitanu New Member

    I want to reduce the % of the WRITELOG wait(now around 40%).Right now the log file is on its own array and is 2.5 GB.

    I am planning the next steps:
    1. shrink the log to 1 GB(I am thinking that a smaller file will improve the writes)
    2. add a second log file on a different disk.

    Do you think this will make the writes to the log faster?
  2. satya Moderator

    Heavy rollback activity adds additional log and disk I/O contention, and such activity has been observed as contributing to writelog timeouts. For best performance, attempt to minimize the number of transactions that must be rolled back; as activity on the server increases, using a query timeout that is too short may further exacerbate the problem by causing unnecessary rollbacks.

    Identify disk bottlenecks using counters, Profiler, ::fn_virtualfilestats, and Showplan.
    Any of the following will reduce these waits:
    - Adding additional I/O bandwidth.
    - Balancing I/O across other drives.
    - Placing the transaction log on its own drive.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. cchitanu New Member

    Hi Satya,
    Thanks for you response.

    So you don't think the size of the log file is affecting the write duration ?

    Also can you explain how to achieve this two for the log file :
    - Adding additional I/O bandwidth.
    - Balancing I/O across other drives.

    Best Regards
  4. cchitanu New Member

    The disks are on EMC and I don't see any I/O bottleneck
  5. Luis Martin Moderator

    Did you run Performance Monitor to see physical queue lengh for each drive?
    With that may be you find heavy I/O over one disk and low over other one.

    About balancing I/O, I gess Satya means to put data or index in differents drives.
    BTW: What SQL and SP have you install?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  6. satya Moderator

    True in the terms of Balancing the I/O the drives.
    As your hardware seems to be high-spec with no I/O bottlenecks, then leave it as it is.

    I would suggest to go as per your plan to add another Tlog in order to share the transaction log files.

    BTW what kind of transactions are held in the database?
    Any other applications sharing the server resources?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. Argyle New Member

    If you have more than one database you can identify the most active databases with fn_virtualfilestats as mentioned above.

    Run:
    SELECT * FROM ::fn_virtualfilestats(dbid,-1) on each db you have (replace dbid)
  8. cchitanu New Member

    After reducing the log size from 2.6 GB to 1 GB the WRITELOG waittype percentage
    dropped at 22% from 44%. So reducing the size of the log helped in this case.

    The second in percentage is CXPACKET(24%) and I plan to reduce the max degree of parallelism from 4 to 2(or to 0).

    Regarding the output of ::fn_virtualfilestats, how you interpret the values ?

    DbId FileId NumberReads Number Writes BytesRead BytesWritten IOStallMS
    -----------------------------------------------------------------------------------------
    814.864.959 389.053 153.623.085.056 3.859.742.720 20.981.951
    82 1.617 2.196.308 86.789.632 2.494.283.264 532
    71 675.155 35.411 21.937.078.272 362.954.752 2.086.094
    72 166 96.959 1290.240 130.366.976 377

    Is 20.981.951 a high value ?
    Is the number of Writes for the log file 21.196.308 high(I assume the log file is 2)

    Best Regards
    Cristian
  9. satya Moderator

    Thats good on the logwrite % and BTW what kind of H/w is on the machine.
    On the terms of Max degree of parallelism, test it before setting.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. cchitanu New Member

    Thanks Satya,
    The server is a cluster with 3 processor and 2 GB of RAM.
  11. gaurav_bindlish New Member

    What is the log backup strategy that you have implemented in the system? If there are operations in the system which need the log to be 2 GB then again the log file will come back to the original state.

    In any case, I generally recommend having regular transaction log backups so that both the log file size and the size of active log in it does not keep on increasing.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  12. cchitanu New Member

    We have transaction log backups every hour
  13. gaurav_bindlish New Member

    Then you can try reducing the log the maximum possible isze and let it grow to the level it needs. That way you'll know how muvh log space you typicallu need.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  14. satya Moderator

    Yes I agree with Gaurav about the interval of the log backups, which will take care of logical sizes for the TLog.

    During DBCC checks try to enable it atleast once in 15 mins.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page