SQL Server Performance

Writelog waits

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by shark68, Oct 11, 2007.

  1. shark68 New Member

    Hi guys,
    we have some performance issues on the server and i see lot's of writelog waits all the time while we inserting transactions constantly into the database.

    The server is 2005 sp2 64-bit with 4g of ram. Log file is raid 1 with 2 disks. Data file on 4 disks raid 10.

    This is the behavior that i see:

    when i run this command:
    select * from sys.dm_os_waiting_tasks where wait_type = 'WRITELOG'

    i see like 10-13 sessions all at the average of 400 ms per session, then when i execute the command again it goes down to 300, then to 200 , 100 , 0 and start all over from 0 to 100, 200 and so on up to 400 and then back to 0 again.
    those are the numbers from SELECT * FROM ::fn_virtualfilestats(5,-1)

    5 1 -1251658953 61153 3117793280 7709506 305247 5897732096 8341717 16051223 1845493760 0x0000000000001040
    5 2 -1251658953 26 542208 38 1104728 2204745728 55250569 55250607 1572864000 0x0000000000000900


    Can you please point me to the right direction where should i look?

    Thanks
  2. thomas New Member

    For your log drive, have you had a look at system monitor [i.e. PerfMon] counters? Specifically, average disk queue length, disk bytes/transfer. disk transfers/sec, read and write breakdowns too?

    Need to analyse if the disks are saturated.

    Are you getting impact on your application(s)? Users noticing?
  3. Greg Larsen New Member

    Here is a query that shows stats for all database files on your server, maybe this will help compare your problem database with others DBs on the same server to determine one that has an avg_write_stalls_ms greatly different then the next. Also you could use sys.dm_os_performance_counters to peak at those System Monitor counters if you want to do all your analyis using T-SQLselect DB_NAME(database_id) DB_NAME, file_id,io_stall_read_ms
    ,num_of_reads
    ,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
    ,io_stall_write_ms,num_of_writes
    ,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms',io_stall_read_ms + io_stall_write_ms as io_stalls
    ,num_of_reads + num_of_writes as total_io
    ,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'from
    sys.dm_io_virtual_file_stats(null,null)order
    by avg_io_stall_ms desc
  4. 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.

Share This Page