Post reply

Blocking and WRITELOG wait type

  • April 2, 2014 at 7:07 am

    #291898

    Hi

    We had an event whereby the app went slow slow for about 1 minute.

    I checked on the wait stats on the server and I saw that the WRITELOG wait type was the top wait with an average wait time of 13.1ms (this is since the server was restarted 3 days ago)

    Using 3rd part tools I noticed we had blocked process at exactly the same time the slow down of the app was reported - I also noticed using the same 3rd party tool that there was a spike on the disc average write time for the log drive of 120ms at the same time.

    That same L drive is shared by about 20 smaller different databases each with their log files placed on the L drive so its possible there was some i/o related issued happening.

    Would the locks that where taken out on the resources by the stored proc that was causing the blocking be held until the log file had been committed to disc - ? (the stored proc was doing an update to a table)

  • April 2, 2014 at 7:11 am

    #1702449

    PearlJammer1 (4/2/2014)

    Would the locks that where taken out on the resources by the stored proc that was causing the blocking be held until the log file had been committed to disc - ? (the stored proc was doing an update to a table)

    Yes. Locks for data modification are held until the transaction has committed. The commit cannot complete until the log records are hardened on 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 2, 2014 at 7:27 am

    #1702458

    Thanks Gail. That makes sense then and I think I have found out why we had the slowdown - because it looks like we had disc io performance issue on the L drive.

    I use this query to find out the historic Transaction Log drive backup size:

    SELECT backup_finish_date, backup_size/1024.0/1024.0 AS Backup_Size_MB,database_name AS DataBase_Log FROM msdb..backupset

    WHERE type='l' and backup_size > 10 and database_name = 'DataBaseXXX'

    AND backup_finish_date BETWEEN '20140402 09:00:00.000' AND '20140402 11:00:00.000'

    Would the next step be to run this against each database that has transaction log backups (every 10 mins in our shop) to find out if there where any large transactions causing the io subsystem problems for the time period the issue happened ?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply