No matter, what kind of storage you have been using, there can be IO issue in the server due to different reason. I am not going to analysis the reason of IO isssue in this blog but I am going to focus more on how you can find out if your storage is having latency issue.
I usually look on following performance counter in perfmon data.
Avg. Disk Sec/Read
Avg. Disk Sec/Write
Avg Disk queue length
I already showed you how to collect performance counter in database in previous blog.
So you can run perfmon on all the logical or physical disk in the server you want to collect data for some days to weeks.
After collecting data into database, now run the following query into database to get record for 1 day.
SELECT MachineName, DiskName= Case cdt.InstanceName when 'I:' then 'TempDB I:' when 'V:' then 'Data V:' when 'Y:' then 'Log Y:' else 'none' end, CounterName , cdt.ObjectName, substring(cd.CounterDateTime,0,20) as CDateTime, AVG(CounterValue) AS avgValue , DisplayString FROM dbo.CounterDetails cdt INNER JOIN dbo.CounterData cd ON cdt.CounterID = cd.CounterID INNER JOIN dbo.DisplayToID d ON d.GUID = cd.GUID WHERE MachineName = '\\serverName' AND ObjectName like '%LogicalDisk' and InstanceName in ('I:','V:','Y:') and CounterName like 'Avg. Disk sec%' and substring(cd.CounterDateTime,0,11)='2013-04-20' GROUP BY MachineName , InstanceName, CounterName , cdt.ObjectName , substring(cd.CounterDateTime,0,20), DisplayString order by substring(cd.CounterDateTime,0,20) asc
Copy the result of the value in the excel sheet. Now use Pivot chat tools in excel to convert the data into nice graph.
From the chart you can see potential bottleneck during certain time. Analyse the disk latency at certain time.
In this graph, i have selected only data disk counters.Blue color is avg. disk sec/read and red color is avg. disk sec/write.
X axis is time and y axis is disk sec per read or write. Y axis is in second unit. There are multiple time disk counter are above 20 millisecond. Here is the Microsoft recommendation for these counter.
Less than 10 ms – very good
Between 10 – 20 ms – okay
Between 20 – 50 ms – slow, needs attention
Greater than 50 ms – Serious I/O bottleneck
Now you can do your own calculation and find out where is your server from IO performance stand.
Further more you can use Avg Disk queue length.If avg disk queue length stays 1 for long time(that means 100%), it is bad.
Number never lies!!! Enjoy