Am I interpreting perfmon counter values properly? (SQL server)

Was hoping soemone might tell me if I'm interpreting this perfmon graph properly,
as it related to SQL server counters..
In the following graphic, you can see the 2 counters I'm capturing, for purposes of seeing SQL server I/O for Reads/Writes needing to go to Disk, instead of getting them from buffer memory.
My question is, since I've set the vertical scale to 100, and the counters are both displaying in ms, is it correct to say the Avg Disk sec/Write(in RED) is mostly
staying below 10ms?
and the Reads(in BLUE) are mainly above 10ms, even going off the chart a few times..?
In this sql
Richard GouetteIT ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Máté FarkasDatabase Developer and AdministratorCommented:
These counters tell you how many millisecond a Write or Read takes in average.
If you want to monitor disk performance then you should check Current Disk Queue Length, Disk Bytes Read/sec, Disk Bytes Write/sec instead.
Disk Queue Length tells you how many operations are waiting in disk queue to perform. If this number is higher than 1 or 2 than your disk is overloaded.
0
Richard GouetteIT ManagerAuthor Commented:
ok, let's assume I wrote the wrong thing I was monitoring.
Can we then focus on the question of graph / scale/ interpretation?

To restate even more plainly: how can I best display the graph to show counters that
output in milliseconds, where I'm looking to show say, values from 0 to  100ms?
0
Máté FarkasDatabase Developer and AdministratorCommented:
Actually those counters show values in Seconds in millisecond precision and because the values can be between 1 millisec and 10 000 millisec (10 seconds) so displaying it in a linear scale is almost impossible.
So you should use another counter: https://blogs.technet.microsoft.com/askcore/2012/03/16/windows-performance-monitor-disk-counters-explained
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Richard GouetteIT ManagerAuthor Commented:
here's what those counters look like.. any thoughts?
why does  the read counter appear to go downward?
and are they both well over the 1 or 2 you mentioned, much of the time?


Capture.PNG
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you're trying to analyze a disk performance I would recommend you to check the %Disk Time (% Disk Read Time, % Disk Write Time)  counters. The returned values will be more easy to read as they are percentages, so between 0 and 100.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Capture also the Disk Transfers/sec (Disk Reads/sec, Disk Writes/sec)  counters. Together with the %Disk Time, you should have a better look of the disk performance.
0
Richard GouetteIT ManagerAuthor Commented:
sigh..
0
Scott PletcherSenior DBACommented:
I've always had trouble getting properly scaling on those graphs, and then when you finally do you can't keep it permanently anyway.

What I do is keep adjusting the scale until it shows reasonably on the page. while using a normal SQL query to give me a range of values that I'm looking at.  For example, the query below will give you latency times in ms.  If it returns, say, 40 (yeah, not too good), then you know when reading the chart that "4<however_many_zeros>" means 40, not 400 or 4 or .4.

You can do something similar for CPU, memory, etc..  Not exactly pleasant or easy, but I haven't found a better way.  Also, it's often easier to use 2 (or even 3) perfmons to view different factors on a matching scale.  That is, all that need a tiny scale on one chart, all that need a huge scale on another, etc..

SELECT @@ServerName as instanceName,
    [Drive], --volume_mount_point,
    CASE WHEN num_of_reads = 0 THEN 0 ELSE (io_stall_read_ms/num_of_reads) END AS [Read_Latency_ms],
    CASE WHEN io_stall_write_ms = 0 THEN 0 ELSE (io_stall_write_ms/num_of_writes) END AS [Write_Latency_ms],
    CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 ELSE (io_stall/(num_of_reads + num_of_writes)) END AS [Overall_Latency_ms],
    CASE WHEN num_of_reads = 0 THEN 0 ELSE (num_of_bytes_read/num_of_reads) END AS [Avg_Bytes_Per_Read],
    CASE WHEN io_stall_write_ms = 0 THEN 0 ELSE (num_of_bytes_written/num_of_writes) END AS [Avg_Bytes_Per_Write],
    CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0
         ELSE ((num_of_bytes_read + num_of_bytes_written)/(num_of_reads + num_of_writes))
         END AS [Avg_Bytes_Per_Transfer]
FROM (
    SELECT LEFT(UPPER(mf.physical_name), 2) AS Drive,  --UPPER (volume_mount_point) as volume_mount_point
        SUM(num_of_reads) AS num_of_reads,
        SUM(io_stall_read_ms) AS io_stall_read_ms, SUM(num_of_writes) AS num_of_writes,
        SUM(io_stall_write_ms) AS io_stall_write_ms, SUM(num_of_bytes_read) AS num_of_bytes_read,
        SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall
    FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
    INNER JOIN sys.master_files AS mf WITH (NOLOCK) ON
        vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
    --CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID)
    GROUP BY LEFT(UPPER(mf.physical_name), 2) --,UPPER (volume_mount_point)) AS tab
) AS derived
ORDER BY [Overall_Latency_ms] DESC
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard GouetteIT ManagerAuthor Commented:
thanks fellas.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.