Read and write latency Performance

  • dva2007

    SSCertifiable

    Points: 7695

    I am not DBA by any means but i am keen to understand more about DBA side and how i can improve performance of SQL Server. I was reading about IOPS and read, write latency and came across these two useful links.

    https://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/
    https://sqlperformance.com/2015/03/io-subsystem/monitoring-read-write-latency

    The output of the script from paul shows following in the first line. 

    ReadLatency    WriteLatency    Latency    AvgBPerRead    AvgBPerWrite    AvgBPerTransfer    Drive    DB           physical_name
    46                     1807                 870           52963                70300                61079                      X:         tempdb    X:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf

    I can see about 300 records where latency is more than 100 which is pretty bad as the article says less than 20 ms is acceptable or less than 5 is good. The other records are for databases and one of the reason this is caused as there is partition on specific big database and all partition growth is 1 MB which is not ideal setting as the article says it is "Death by thousand cuts". I found it is making the difference when i changed this growth setting however i am not sure how to rectify this tempdb issue. Where do i start? Any advise on reading other technical detail would be very useful. 

    Thank you for your help.

  • Sue_H

    SSC Guru

    Points: 89721

    dva2007 - Wednesday, October 11, 2017 1:41 AM

    I am not DBA by any means but i am keen to understand more about DBA side and how i can improve performance of SQL Server. I was reading about IOPS and read, write latency and came across these two useful links.

    https://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/
    https://sqlperformance.com/2015/03/io-subsystem/monitoring-read-write-latency

    The output of the script from paul shows following in the first line. 

    ReadLatency    WriteLatency    Latency    AvgBPerRead    AvgBPerWrite    AvgBPerTransfer    Drive    DB           physical_name
    46                     1807                 870           52963                70300                61079                      X:         tempdb    X:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf

    I can see about 300 records where latency is more than 100 which is pretty bad as the article says less than 20 ms is acceptable or less than 5 is good. The other records are for databases and one of the reason this is caused as there is partition on specific big database and all partition growth is 1 MB which is not ideal setting as the article says it is "Death by thousand cuts". I found it is making the difference when i changed this growth setting however i am not sure how to rectify this tempdb issue. Where do i start? Any advise on reading other technical detail would be very useful. 

    Thank you for your help.

    There is a good whitepaper on tempdb that came out for SQL Server 2005 that is worth reading:
    Working with tempdb in SQL Server 2005

    There are several more articles on sqlskills that are worth reading on tempdb - just search the site. Another one I can think of which has additional links referenced would be:
    Tempdb configuration survey results and advice

    Sue

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

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