performance

I have been looking at the performance of my SQL Server 2000.
For %Disk Time I get the following values for 24 hours (3AM to 3AM next day):
MIN MAX AVERAGE
0.494 39244.5 113.686
If I take out the busiest 45 minutes and look at the Statistics for 23 hours
and 15 minutes then I get the following values (3:45 AM to 3 AM next day)
MIN MAX AVERAGE
0.912 4860.8 42.423
Microsoft suggests a threshold value of 90% for this counter. Do these
values look OK? I am not having any performance problems on this machine.
Even the average %Processor Time is less than 3%. Any insight will be
appreciated. Thanks.%Disk Time is almost a useless counter and should be used very sparingly or
even not at all. If it is at 100 or less you can be pretty sure the disks
are not being taxed too much. Anything over that and it becomes pretty
unreliable and hard to correlate. The disk Q's will give a much better
overall indication of how busy they are but there are other disk counters
that will give more detailed insights to how efficient they are. The
fn_virtual_filestats are also a good place to start for disk related info.
Here are some other places:
http://sqlblog.com/blogs/linchi_shea/archive/2007/03/28/don-t-forget-the-business-transaction-throughput-and-response-time.aspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
IO Basics
http://www.microsoft.com/technet/prodtechnol/sql/2005/physdbstor.mspx
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:159A4413-BD9B-4123-A669-4D4140603888@.microsoft.com...
>I have been looking at the performance of my SQL Server 2000.
> For %Disk Time I get the following values for 24 hours (3AM to 3AM next
> day):
> MIN MAX AVERAGE
> 0.494 39244.5 113.686
> If I take out the busiest 45 minutes and look at the Statistics for 23
> hours
> and 15 minutes then I get the following values (3:45 AM to 3 AM next day)
> MIN MAX AVERAGE
> 0.912 4860.8 42.423
> Microsoft suggests a threshold value of 90% for this counter. Do these
> values look OK? I am not having any performance problems on this machine.
> Even the average %Processor Time is less than 3%. Any insight will be
> appreciated. Thanks.|||In addition to what Andy said, you should also look at the I/O response
counters (Avg Disk sec/read and Avg disk sec/write). These counters tell you
how long in average an I/O read or write take to complete. Together with Avg
Disk bytes/read and Avg Disk bytes/write--which tell you the sizes of your
I/O, you can get a pretty good picture of how your disks are doing. For
instance, if you are doing small I/Os and your avg disk response time is long
(e.g. > 20ms or a smaller threshold), something is up with your disk and you
should take a closer look.
Linchi
"sharman" wrote:
> I have been looking at the performance of my SQL Server 2000.
> For %Disk Time I get the following values for 24 hours (3AM to 3AM next day):
> MIN MAX AVERAGE
> 0.494 39244.5 113.686
> If I take out the busiest 45 minutes and look at the Statistics for 23 hours
> and 15 minutes then I get the following values (3:45 AM to 3 AM next day)
> MIN MAX AVERAGE
> 0.912 4860.8 42.423
> Microsoft suggests a threshold value of 90% for this counter. Do these
> values look OK? I am not having any performance problems on this machine.
> Even the average %Processor Time is less than 3%. Any insight will be
> appreciated. Thanks.