October 11, 2017 at 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.
October 11, 2017 at 12:34 pm
dva2007 - Wednesday, October 11, 2017 1:41 AMI 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-latencyThe 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.mdfI 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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply