Disk Activity Audit - important metrics?

Hello All,

I am trying to perform a performance audit on a SQL Server where I am positive my most likely bottle neck is the disks. I assume the primary metrics I should be interested in are %Disk Activity and Disk Queue Length, right?

What are the actually guidelines for these metrics? Also, Are there other perf mons I should be running against the disks?

Thanks In Advance.

Ryan HuntThe following link will answer your questions,

http://www.sql-server-performance.com/sql_server_performance_audit2.asp|||Thanks for that link. I am a bit confused about the measurements I am seeing on %busy and Average Disk Queue length. Graphically, each metric is showing up exactly identical. In other words, I cannot see two separate lines in a line graph...they are on top of each other. Also, if I look at the numeric data for each, there are again some discrepancies. The maximum for %busy has hit as high as 205.7%. The Average queue length is coming in as ((%busy)/100), or a maximum of 2.06 (rounded). Does this sound correct? How is the percent over 100%?

Thanks

Ryan Hunt

Disk activity

We are about to go to SQL 2005 and that means getting some new Servers and i am wondering if our SAN is adequate.

I like to measure over the next few weeks the amount of data that is sent to the SAN and how long its taking to come back....i.e if someone is running a large report how much data is sent and how long it takes to return.

This is for 2000 SQL (i like to get all these results to build my migration plan and hardware configuration).

I like to see...........18 mb of data , date time and how much read / write etc if possible the sql.

Any ideas

The tools are:
SQL Server Profiler (search for Profiler in Books Online. There are many topics)
Performance Monitor

Also see SQL Server 2005 Books Online topics:
System Monitor Scenarios
How to: Correlate a Trace with Windows Performance Log Data (SQL Server Profiler)

|||

I looked at profiler but could not get the results i really wanted....

I wanted totally bytes read and write and how long it takes the san to deliver

i.e it was say a 100 mb data request ....i was trying to figure out the SAN response to this.

|||

Look at Performance Monitor (perfmon). It's under Administrative tools on the Start/Programs menu.

Check out the LogicalDisk and System Performance Objects and the counters in the list for each. The topics I mentioned above have some help on aligning a Profiler trace with a perfmon trace if that's useful.

|||

I got the following to run

Disk Write Bytes /Sec

Disk Read Bytes/Sec

Avg Disk Bytes Read

Avg Disk Bytes Write

Avg. Disk sec/Transfer

Split IO/Sec

%Disk Read Time

%Disk Write Time

Current Disk Queue Length

I think that get me in the right direction......

sql