Post reply

Disk latency Writes and the Recovery Interval

  • March 1, 2020 at 11:13 pm

    #3729989

    I have a number of clients in Azure who are experiencing Disk Write Latency of 200ms and above. The issue in Azure is IOPS are capped based on the size of the disk, so on a P30, 1TB disk you are capped at 5,000 IOPS per disk. This creates a number of issues:

    1. If you have a small database (150GB) with high writes (particularly) then you can be paying for a lot of disk you don't need just to reduce the latency.
    2. SQL writes are driven by the Recovery Interval (min), and peak roughly every 60 sec when SQL writes dirty pages to disk. These peaks can very easily go over the threshold and become throttled causing write latency.

    A Microsoft technician recommended we reduce the Recovery Interval to write more often, but from all the research and testing I've done, you can't set the Recovery Interval to less than 1 minute. The default is 0, which is every ~60 sec, managed by SQL. You can't set this value anything except a positive integer value (and 0)

    Does anyone know of a way to increase the frequency of the checkpoint process? (I'd rather not put in a separate process like a job that needs to run all the time). The idea is if SQL writes to disk more often, each write will be fewer pages, the IO spikes will be lower and there will be less throttling.

    I'm also wondering if we are stressing too much about the Write Latency. The average read latency is acceptable, and there is sufficient RAM that PLE is in hours, and there's no evidence of significant performance problems. Although the write IOPS peak, they don't last more than a few seconds, and I can't see that these would be impacting on users.

    My big question... Should we as DBAs be putting less emphasis on Write Latency given "all" the MDF writes are only to harden the dirty pages to disk, and as long as:

    • The PLE is good (data reads from memory are good)
    • and the Write Latency isn't impacting the Read Latency
    • And the Write Peaks are not lasting more than a few seconds,

    why should we worry if Write latency is high?

     

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • March 1, 2020 at 11:42 pm

    #3729993

    Others will comment - most likely with better answers than mine.

     

    regarding the 1 min interval - that is the default on 2016+ but it can be set in seconds so I would expect you could set it to 15 or 30 seconds

    ALTER DATABASE testa SET TARGET_RECOVERY_TIME = 20 SECONDS; on a 2014 db worked fine

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

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