Prfmon questions - Disks

I am monitoring a disk which has SQL data. There are the counters i am
monitoring
AVg.Disk.Read.Q.Length
AVg.Disk.Write.Q.Length
Disk Reads/sec
Disk Writes/sec
Split IO/sec
I have set all the scale to 1.0(how does this scale works?what is it
actually?) So, which counters i have to pay attention closely so that i could
tell them that they are reading or writing their disk extensively and sth
need to be done like splitting the data to several disks.
Any hints would be really great
TIAIt depends on what your IO subsystem can handle. But assuming the data is
being spread over at least 10 disks (preferably more), I get concerned when
any of the Avg queue lengths is greater than about 10 for a sustained time.
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i
> could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>|||Split i/o is the only one here I'd be looking at. A lot of split i/o is
indicative of a problem. % disk time is an easy counter to watch since it's
relatively indicative of whether your disks need to be watched closely. A
set of disks with 100% disk time will need to be tuned for performance and
SQL watched closely. If disk time is 20% or greater you'll need to start
keeping an eye on indexes, query plans and the like.
"rupart" wrote:
> I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>|||I prefer the following counters:
Current disk Q length
Current Read bytes/sec
Current Write bytes/sec
Percent Disk time is useless on SCSI subsystems with RAID or Command Tag
Queuing and on the new SATA disk systems with RAID or Native Command
Queuing. Average times can mask short term activity spikes. Most data
partition reads and writes are in 8K blocks so IOs/sec usually follows teh
bytes/sec very closely. Disk Q length is the real critical counter as it
indicates an IO bottleneck.
These counters work best if you have run IOmeter or any IO stress before
deploying the server so you have an idea of its true maximum capabilities
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i
> could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>|||> I have set all the scale to 1.0(how does this scale works?what is it
> actually?)
The counter value is multiplied by the specified scale and the result is
graphed according the min/max graph value. So, with a scale of 1 and the
default 0-100 min/max, computed values equal to or greater than over 100
will appear at the top of the graph and lower values somewhere between the
top and the bottom.
As a general rule, disk queue lengths should be no more than 2 times the
number of physical disks in the array. A higher number indicates i/o
requests are waiting for other i/o to complete rather than doing productive
work. So if you have 5 disks, you could specify a scale of 10 to that the
graphed metric will appear at the top when the value is >= 10.
Reads/Writes/Transfers per second is a measure of how much i/o is performed.
This can be a little tricky to interpret since it includes both sequential
and random i/o as well as i/o that is cached by the controller and disks.
There isn't necessarily a good or bad number but you can compare
transfers/sec to what your i/o subsystem is capable of doing to determine if
there is a problem. For example, if you run an i/o bound query and don't
have disk queuing and transfers/sec is well under your disk capability, this
could indicate a lot of disk seek time.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i
> could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>|||Thx alot guys for your replies. OK, here are my counters data for 30 days
and i only captured the average value. From here, what we can say abt it and
anything need to be done or worried? (Fyi: The scale set to 1 and data
captured for each 15mins interval)
counters Data
--
---
AVg.Disk.Read.Q.Length (average =1.6, min=0, max=96.84)
AVg.Disk.Write.Q.Length (average =0.228, min=0.04, max=13.22)
Disk Reads/sec (average =55.2, min=0.08, max=1456.990)
Disk Writes/sec (average =24.881, min=6.726, max=128.97)
Split IO/sec (average =0.077, min=0, max=1.774)
"Geoff N. Hiten" wrote:
> I prefer the following counters:
> Current disk Q length
> Current Read bytes/sec
> Current Write bytes/sec
> Percent Disk time is useless on SCSI subsystems with RAID or Command Tag
> Queuing and on the new SATA disk systems with RAID or Native Command
> Queuing. Average times can mask short term activity spikes. Most data
> partition reads and writes are in 8K blocks so IOs/sec usually follows teh
> bytes/sec very closely. Disk Q length is the real critical counter as it
> indicates an IO bottleneck.
> These counters work best if you have run IOmeter or any IO stress before
> deploying the server so you have an idea of its true maximum capabilities
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
> >I am monitoring a disk which has SQL data. There are the counters i am
> > monitoring
> >
> > AVg.Disk.Read.Q.Length
> > AVg.Disk.Write.Q.Length
> > Disk Reads/sec
> > Disk Writes/sec
> > Split IO/sec
> >
> > I have set all the scale to 1.0(how does this scale works?what is it
> > actually?) So, which counters i have to pay attention closely so that i
> > could
> > tell them that they are reading or writing their disk extensively and sth
> > need to be done like splitting the data to several disks.
> > Any hints would be really great
> >
> > TIA
> >
> >
>
>|||by the way, it's in RAID 0+1 currently
"rupart" wrote:
> Thx alot guys for your replies. OK, here are my counters data for 30 days
> and i only captured the average value. From here, what we can say abt it and
> anything need to be done or worried? (Fyi: The scale set to 1 and data
> captured for each 15mins interval)
> counters Data
> --
> ---
> AVg.Disk.Read.Q.Length (average =1.6, min=0, max=96.84)
> AVg.Disk.Write.Q.Length (average =0.228, min=0.04, max=13.22)
> Disk Reads/sec (average =55.2, min=0.08, max=1456.990)
> Disk Writes/sec (average =24.881, min=6.726, max=128.97)
> Split IO/sec (average =0.077, min=0, max=1.774)
>
> "Geoff N. Hiten" wrote:
> > I prefer the following counters:
> >
> > Current disk Q length
> > Current Read bytes/sec
> > Current Write bytes/sec
> >
> > Percent Disk time is useless on SCSI subsystems with RAID or Command Tag
> > Queuing and on the new SATA disk systems with RAID or Native Command
> > Queuing. Average times can mask short term activity spikes. Most data
> > partition reads and writes are in 8K blocks so IOs/sec usually follows teh
> > bytes/sec very closely. Disk Q length is the real critical counter as it
> > indicates an IO bottleneck.
> >
> > These counters work best if you have run IOmeter or any IO stress before
> > deploying the server so you have an idea of its true maximum capabilities
> >
> > --
> > Geoff N. Hiten
> > Senior Database Administrator
> > Microsoft SQL Server MVP
> >
> > "rupart" <rupart@.discussions.microsoft.com> wrote in message
> > news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
> > >I am monitoring a disk which has SQL data. There are the counters i am
> > > monitoring
> > >
> > > AVg.Disk.Read.Q.Length
> > > AVg.Disk.Write.Q.Length
> > > Disk Reads/sec
> > > Disk Writes/sec
> > > Split IO/sec
> > >
> > > I have set all the scale to 1.0(how does this scale works?what is it
> > > actually?) So, which counters i have to pay attention closely so that i
> > > could
> > > tell them that they are reading or writing their disk extensively and sth
> > > need to be done like splitting the data to several disks.
> > > Any hints would be really great
> > >
> > > TIA
> > >
> > >
> >
> >
> >|||You need to capture at 15 second to 2 minute intervals for a few days, at
least during peak hours. Then you can determine your performance
bottlenecks woth the graphs relative to each other. Note that the scale for
each counter only affects its presentation on the graph. The actual
underlying values are always what is recorded. Average, min, and max over
long periods of time really don't mean a lot. How long does a value stay
near its maximum?. Is the application slowing down during these times? Raw
numbers don't tell the story, nunbers in context do.
You might want to look at the SQLH2 performance collector module for this
type of data recording.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:66056E0C-363D-4FBF-A1FF-614508C7A552@.microsoft.com...
> Thx alot guys for your replies. OK, here are my counters data for 30 days
> and i only captured the average value. From here, what we can say abt it
> and
> anything need to be done or worried? (Fyi: The scale set to 1 and data
> captured for each 15mins interval)
> counters Data
> --
> ---
> AVg.Disk.Read.Q.Length (average =1.6, min=0, max=96.84)
> AVg.Disk.Write.Q.Length (average =0.228, min=0.04, max=13.22)
> Disk Reads/sec (average =55.2, min=0.08, max=1456.990)
> Disk Writes/sec (average =24.881, min=6.726, max=128.97)
> Split IO/sec (average =0.077, min=0, max=1.774)
>
> "Geoff N. Hiten" wrote:
>> I prefer the following counters:
>> Current disk Q length
>> Current Read bytes/sec
>> Current Write bytes/sec
>> Percent Disk time is useless on SCSI subsystems with RAID or Command Tag
>> Queuing and on the new SATA disk systems with RAID or Native Command
>> Queuing. Average times can mask short term activity spikes. Most data
>> partition reads and writes are in 8K blocks so IOs/sec usually follows
>> teh
>> bytes/sec very closely. Disk Q length is the real critical counter as it
>> indicates an IO bottleneck.
>> These counters work best if you have run IOmeter or any IO stress before
>> deploying the server so you have an idea of its true maximum capabilities
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "rupart" <rupart@.discussions.microsoft.com> wrote in message
>> news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>> >I am monitoring a disk which has SQL data. There are the counters i am
>> > monitoring
>> >
>> > AVg.Disk.Read.Q.Length
>> > AVg.Disk.Write.Q.Length
>> > Disk Reads/sec
>> > Disk Writes/sec
>> > Split IO/sec
>> >
>> > I have set all the scale to 1.0(how does this scale works?what is it
>> > actually?) So, which counters i have to pay attention closely so that i
>> > could
>> > tell them that they are reading or writing their disk extensively and
>> > sth
>> > need to be done like splitting the data to several disks.
>> > Any hints would be really great
>> >
>> > TIA
>> >
>> >
>>