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.
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.