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

asked Jul 11, 2011 at 11:39 AM in Default

ebzm gravatar image

423 29 33 35

What is missing from perfmon? What OS version are you running? What version of SQL Server?
Jul 11, 2011 at 12:03 PM Shawn_Melton
Taking physical disk counters for reads and writes latency analysis on mount points and there is no data at all.
Jul 11, 2011 at 01:51 PM ebzm
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
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.
more ▼

answered Jul 11, 2011 at 01:10 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

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.
Jul 11, 2011 at 01:49 PM ebzm

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 http://msdn.microsoft.com/en-us/library/ms187309(v=SQL.100).aspx
Jul 11, 2011 at 02:43 PM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

You should really be using DMVs for this kind of information now. Take a look at sys.dm_io_virtual_file_stats and [sys.dm_os_wait_stats][2]. They have great information to help identify and troubleshoot disk issues.

[2]: http://msdn.microsoft.com/en-us/library/ms179984.aspx
more ▼

answered Jul 11, 2011 at 04:31 PM

SirSQL gravatar image

4.8k 1 3

(comments are locked)
10|1200 characters needed characters left
Have you tried looking at the counters under Logical Disk in PerfMon?
more ▼

answered Jul 11, 2011 at 12:58 PM

Blackhawk-17 gravatar image

11.9k 28 31 36

I used the PhysicalDisk and not the LogicalDisk. Is that what I should use?
Jul 11, 2011 at 01:15 PM ebzm
Mount points are considered Logical from within PerfMon - yes.
Jul 12, 2011 at 05:18 AM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 11, 2011 at 11:39 AM

Seen: 1781 times

Last Updated: Jul 11, 2011 at 11:39 AM