question

ebzm avatar image
ebzm asked

Disk performance

How do you monitor disk performance on mount point on sql cluster? Perfmon is not doing the job. Any other option that could be useful? Please let me know. Thanks.
sql-server-2008sqlserver
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What is missing from perfmon? What OS version are you running? What version of SQL Server?
0 Likes 0 ·
Taking physical disk counters for reads and writes latency analysis on mount points and there is no data at all.
0 Likes 0 ·
Blackhawk-17 avatar image
Blackhawk-17 answered
Have you tried looking at the counters under Logical Disk in PerfMon?
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mount points are considered Logical from within PerfMon - yes.
1 Like 1 ·
I used the PhysicalDisk and not the LogicalDisk. Is that what I should use?
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
`select * from fn_virtualfilestats(-1,-1)` might provide some help, down at the database device level... Record the output from that periodically (say, every 10 minutes), do some appropriate calculations, and you have a reasonable view of which DB files are most used.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The `fn_virtualfilestats` function returns IOStallReadMS and IOStallWriteMS - you can divide that by the number of reads / writes to determine if there's a problem. (This assumes you've got regular snapshots of the data, and have done the appropriate calculations to show you only the changes per timeslot.) fn_virtualfilestats documentation is at
1 Like 1 ·
I am wondering about how I should analyze/measure the disk response times from fn_virtualfilestats(-1,-1) number of reads and writes. Any suggestions? For example, using perfmon counters, I will take the response time’s greater than 15 milliseconds as good and disk subsystem is keeping up and there is not that much overhead left. And, if it is more than 25 milliseconds, there is a slow performance.
0 Likes 0 ·
SirSQL avatar image
SirSQL answered
You should really be using DMVs for this kind of information now. Take a look at [sys.dm_io_virtual_file_stats][1] and [sys.dm_os_wait_stats][2]. They have great information to help identify and troubleshoot disk issues. [1]: http://msdn.microsoft.com/en-us/library/ms190326.aspx [2]: http://msdn.microsoft.com/en-us/library/ms179984.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.