(SQL SERVER 2000)
I keep getting the "primary filegroup is full" error when ever I try to write data even though the following things are true:
allow unlimited growth set for BOTH database and transaction log
auto shrink and auto update statistics flags are set in database options
growth set to 10 percent for both
database is 4GB but disk has 12GB freespace
transaction log is on same disk but is only 8MB ?
SQL shows the database has 2% free space but any attempts to write cause the error, shrinking drops this to 1% but still produces the same error on writes
SQL shows the transaction log has 90% free space
backup maintenance plans are in operation for both transaction log and database (write to different drive)
I am completely confused!Google's a wonderful thing
http://support.jodohost.com/showthread.php?t=981
Try bumping up the percent growth
I don't personally like to set to autogrow...the database should be managed to meet particular expectations, you should predicate the growth, and set alerts to imform you when your reaching those limits...
But I betcha that should fix it.|||Changed the growth percentage to 80% restarted SQL server agent shrank database, tried the write operation SAME ERROR! But now refuses to perfrom the transaction log backup but only give a general fail error, yet the drive still has 12GB free space!!!!
For some reason it just won't go beyond the 4GB size, weird, is Windows 2000 Server anything to do with it??|||Any chance you might have installed MSDE on that machine? According to the Capacity Specifications (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp), MSDE has a 2 Gb limit on the data portion of a database that might be causing havok.
-PatP|||If that was MSDE the poster wouldn't have had 4GB worth of data.
Showing posts with label write. Show all posts
Showing posts with label write. Show all posts
Friday, March 9, 2012
Wednesday, March 7, 2012
Prgramming for SQL Server 2005
Folks,
I'm new to sql server and want to know
1) what are the possible ways to program ( I mean do we have any frontend tools OR where do we write program which eventually talks to sql database)so we can do ETL operation for data of sql server DB.
2) How to learn that programing ( meaning what are the resources? )
3) I'm new to database concepts as i'm Java Programer - what basic things I must know in order to design sql database?
all above que is to support my current new job, PLS advise asap.
thanks.
Well, the ETL function of SQL Server 2005 is called SSIS, so you will want to study up on that. I, like you, have had to learn by pretty much diving into it head first. I do, however have a VS .Net 2003 and Visual Basic background.
I have used MSDN heavily, these forums heavily and have many books pertaining to Visual Basic, ADO.Net, SQL Server, etc.
I know that this is not much, but it might be a start. I do like the books from Microsoft Press. I also have some from McGraw-Hill.
|||you may want to start learning sql with then
database normalization, select statement , views, sp, udfs,index,file and filegroups
|||This is helpful..I'll check these things from some sql books.
Thanks a lot.
|||Good Info but can you tell me little more specificaly that if i don't use any .Net langauge to write small program is there anyh tool in sql server 2005 which provides me development enviroment? for example editor where I can write queries/procedures/programmes.
OR Let me ask you that,
Do i have to use some .Net lang as frontend OR there is some tools/editor/enviroment which i can use from sql server/client installation?
|||If you already have an existing database, you can start using it with SqlConnection and SqlCommand. Look them up in the MSDN. You’ll need a book to get started with the T-SQL language. Then you’ll use the Books Online of SQL Server.
About OR – we are working on it. A CTP is about to be released. Keep an eye on http://www.microsoft.com/data.
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
>> >
>> >
>>
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
>> >
>> >
>>
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
TIA
It 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...
>
>
|||by the way, it's in RAID 0+1 currently
"rupart" wrote:
[vbcol=seagreen]
> 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:
|||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...[vbcol=seagreen]
> 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:
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
It 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...
>
>
|||by the way, it's in RAID 0+1 currently
"rupart" wrote:
[vbcol=seagreen]
> 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:
|||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...[vbcol=seagreen]
> 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:
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 coul
d
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 co
uld
> 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...
>
>|||by the way, it's in RAID 0+1 currently
"rupart" wrote:
[vbcol=seagreen]
> 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 a
nd
> 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:
>|||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...[vbcol=seagreen]
> 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:
>
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 coul
d
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 co
uld
> 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...
>
>|||by the way, it's in RAID 0+1 currently
"rupart" wrote:
[vbcol=seagreen]
> 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 a
nd
> 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:
>|||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...[vbcol=seagreen]
> 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:
>
Subscribe to:
Posts (Atom)