we have simultaneously upgraded our server hardware and changed from
sql server 2000 to sql server 2005 and found that we have some
performance problems.
The database is of medium size (about 10Gb) and is a mix of reporting
and business transaction processing. We use a mix of 2-tier and 3-tier
components, there are about 20 users (most access through 3-tier, so
don't touch thedb directly).
The feeling -- user perception and measurment of top-line component
performance (i.e., "that big report used to run in 3 minutes and now it
takes 6") -- that things are slower than they were. They certainly
aren't faster for most operations and given that we have jumped up
quite a lot in hardware seems a little disappointing. The machine is a
hyperthreaded, dual xeon proc dell with separate RAID disks for log
and data and 4gb of ram. the pagefile is on a separate disk.
Things have improved with doing some maintenance like update stats and
reorganise indexes, but we don't have sufficient downtime during the
week to rebuild indexes.
So, is the solution to attack the areas of worst performance or is
there some general, server-wide thing that I am missing? Should I try
and find downtime to do the database tuning wizard or did people not
need to?
One of the few things that have jumped out of analysis of the wait
types is that CXPACKET can account for up to 80% of the wait time (see
sql below). Googling on that led me to
http://blogs.msdn.com/slavao/archiv.../12/492119.aspx
which is linked to a fair bit. We do have a hyperthreaded box, but do
people have any strong opinions on hyperthreading? Did turning it off
work for you and what kind of improvement did you see?
Finally, one oddity, is that in task manager, Sql server shows that it
is using only 80 MB (sic, I really mean Megabytes) and we have 4Gig
available. Looking at other things, it seems like most of the memory is
being used and the 80Mb is a mistake.
any help appreaciated,
samOn 27.09.2006 17:10, sam.m.gardiner wrote:
> we have simultaneously upgraded our server hardware and changed from
> sql server 2000 to sql server 2005 and found that we have some
> performance problems.
This makes determining causes of your performance problems a bit more
difficult because now you do not know whether it's the software or the
hardware.
> The database is of medium size (about 10Gb) and is a mix of reporting
> and business transaction processing. We use a mix of 2-tier and 3-tier
> components, there are about 20 users (most access through 3-tier, so
> don't touch thedb directly).
Um, other people would put 10GB rather in the "small" category... :-)
> So, is the solution to attack the areas of worst performance or is
> there some general, server-wide thing that I am missing? Should I try
> and find downtime to do the database tuning wizard or did people not
> need to?
IMHO you don't necessarily need downtime. You can do a profiler trace
while people do the slow queries and then have it analyzed.
> One of the few things that have jumped out of analysis of the wait
> types is that CXPACKET can account for up to 80% of the wait time (see
> sql below). Googling on that led me to
> http://blogs.msdn.com/slavao/archiv.../12/492119.aspx
> which is linked to a fair bit. We do have a hyperthreaded box, but do
> people have any strong opinions on hyperthreading? Did turning it off
> work for you and what kind of improvement did you see?
Not exactly what you asked for but: I know that for MaxDB you must not
count hyperthreading kernels but real kernels, i.e. a one CPU machine
with HT counts as 1.
Maybe try also maxdop = 1 to see how that changes things:
http://www.sqlblogs.com/top/ng/grou...lism/index.aspx
> Finally, one oddity, is that in task manager, Sql server shows that it
> is using only 80 MB (sic, I really mean Megabytes) and we have 4Gig
> available. Looking at other things, it seems like most of the memory is
> being used and the 80Mb is a mistake.
Actually, this was my first question when I read your message: what are
the memory settings for the instance in question? What do you mean by
"other things"? Does the DB use 80MB or not?
Kind regards
robert|||There is something very wrong with your memory - possibly there is another
bottleneck.
What I normally do is to run profiler for an hour or so, then save it in a
sql table, and place indexes on cpu, reads, writes, duration. I then query
by top 100 for duration, cpu, reads, write like this
select top 100 textdata from trace order by duration desc.
This way the most expensive operations come to the top.
I then sum common operations and again order them by duration, cpu, etc. You
have to parse the statements to reduce them to the proc names to do this.
You then have an idea of which procs to improve.
Note that Microsoft has spent 5 or more years adding features to this
product and improving performance of it. They have added a lot of code to
the query engine. Some plain vanilla operations might under some
circumstances show no improvement or take longer as instead of having to go
through 2000 program statements as was the case in SQL 2000, it might now
have to go through 3000 program statement to issue the same query plan. You
might be bumping into one of these cases.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"sam.m.gardiner" <sam.m.gardiner@.gmail.com> wrote in message
news:1159369843.899959.259710@.k70g2000cwa.googlegroups.com...
> we have simultaneously upgraded our server hardware and changed from
> sql server 2000 to sql server 2005 and found that we have some
> performance problems.
> The database is of medium size (about 10Gb) and is a mix of reporting
> and business transaction processing. We use a mix of 2-tier and 3-tier
> components, there are about 20 users (most access through 3-tier, so
> don't touch thedb directly).
> The feeling -- user perception and measurment of top-line component
> performance (i.e., "that big report used to run in 3 minutes and now it
> takes 6") -- that things are slower than they were. They certainly
> aren't faster for most operations and given that we have jumped up
> quite a lot in hardware seems a little disappointing. The machine is a
> hyperthreaded, dual xeon proc dell with separate RAID disks for log
> and data and 4gb of ram. the pagefile is on a separate disk.
> Things have improved with doing some maintenance like update stats and
> reorganise indexes, but we don't have sufficient downtime during the
> week to rebuild indexes.
> So, is the solution to attack the areas of worst performance or is
> there some general, server-wide thing that I am missing? Should I try
> and find downtime to do the database tuning wizard or did people not
> need to?
> One of the few things that have jumped out of analysis of the wait
> types is that CXPACKET can account for up to 80% of the wait time (see
> sql below). Googling on that led me to
> http://blogs.msdn.com/slavao/archiv.../12/492119.aspx
> which is linked to a fair bit. We do have a hyperthreaded box, but do
> people have any strong opinions on hyperthreading? Did turning it off
> work for you and what kind of improvement did you see?
> Finally, one oddity, is that in task manager, Sql server shows that it
> is using only 80 MB (sic, I really mean Megabytes) and we have 4Gig
> available. Looking at other things, it seems like most of the memory is
> being used and the 80Mb is a mistake.
> any help appreaciated,
> sam
>|||Robert Klemme wrote:
> This makes determining causes of your performance problems a bit more
> difficult because now you do not know whether it's the software or the
> hardware.
No kidding. Memo to self, don't do this again.
> Um, other people would put 10GB rather in the "small" category... :-)
hey, it's all I have!
> IMHO you don't necessarily need downtime. You can do a profiler trace
> while people do the slow queries and then have it analyzed.
Well, I've spent quite a lot of time looking at waitstats and I have
run the profiler but as I say I don't want to spend time drilling down
on queries to find I have some gross problem with the overall setup, so
I've started with the slow components, and I'll work in from there. It
does seem like there is a general system-wide performance issue, so if
I start digging into the profiler i might not see the wood for the
trees. But I will try it soon.
What i've found is really missing from online resources is very broad
estimates for what is "normal". Obviously this varies a lot depending
on hardware and db design but i'm prepared to bet that there are
milions of installations of a similar nature; single db server with a
small ;-) database but I can't find any stats for what is normal. For
example I read in one article some things about correcting the perfmon
stats for disk IO for a RAID config.
http://www.microsoft.com/technet/pr...5/tsprfprb.mspx
and it talks about number of "I/os per disk" and other articles talk
about "i/os per spindle".. but what is typical 1, 10, 100? and where
will performance problems start? I'm not asking for an answer to this
specific question, just ighlighting my general ignorance on these
things.. ;-)
> Actually, this was my first question when I read your message: what are
> the memory settings for the instance in question? What do you mean by
> "other things"? Does the DB use 80MB or not?
the memory settings are the out of the box ones, i.e., the max memory
usage is 2147483647MB which is obviously way more than we have. I have
seen the sql server running at what is "normal" for this machine, i.e,.
1.7 Gb but since a restart (to move the page file) the task manager
shows approx 80 Mb. However, the total memory free (seen from task
manager) is only 150 Mb so something must be using up the rest (and
there are no other big apps running). I assume that it is really SQL
server and this is just being misreported. is this a problem or should
it be a issue with Microsoft?
thanks a lot for your interest, I know my problem is vague and hard to
diagnose.
sam|||On 27.09.2006 19:04, sam.m.gardiner wrote:
> Robert Klemme wrote:
>
> No kidding. Memo to self, don't do this again.
Good.
> and it talks about number of "I/os per disk" and other articles talk
> about "i/os per spindle".. but what is typical 1, 10, 100? and where
> will performance problems start? I'm not asking for an answer to this
> specific question, just ighlighting my general ignorance on these
> things.. ;-)
There is only one rule: there is no general rule. Every DB and
application is different and there are no general values (other than
maybe for the demo databases - but I do not know whether someone
actually uses them for benchmarking).
> the memory settings are the out of the box ones, i.e., the max memory
> usage is 2147483647MB which is obviously way more than we have. I have
> seen the sql server running at what is "normal" for this machine, i.e,.
> 1.7 Gb but since a restart (to move the page file) the task manager
> shows approx 80 Mb. However, the total memory free (seen from task
> manager) is only 150 Mb so something must be using up the rest (and
> there are no other big apps running). I assume that it is really SQL
> server and this is just being misreported. is this a problem or should
> it be a issue with Microsoft?
Then start with the obvious and change the memory setting in a way that
SQL Server won't use more than 80~90% of your physical memory. You know
what happens if it tries to use more mem, do you...?
Cheers
robert|||OK so we changed the max memory setting to 3000 MB (we have 4GB) but
sql server still reports that it is using ~100 Mb. There are no other
large memory users, and performance monitor says the same thing
(although, I admit this isn't really extra evidence).. So, if no one
has any more advice on the subject I think I might have to go the MS
issue route, this may not be a problem but it's worrying me enough to
want to find out what is going on. AWE is still enabled, is it possible
that this is causing misreporting of memory in taskmanager?
also, hyperthreading being switched off hasn't dramatically improved
performance (given that there were other maintenance steps like index
rebuild etc on the weekend) but I don't see the 80% CXPACKET waits that
were occurring, so I feel that this must be a little better.
The other thing that I noticed on friday is that someone (not me,
*really*) has installed Symantec antivirus on the machine. how likely
is it that this is causing a problem? I've switched it off for now, and
we'll see what happens.
thanks for your help,
sam|||On 02.10.2006 13:38, sam.m.gardiner wrote:
> OK so we changed the max memory setting to 3000 MB (we have 4GB) but
> sql server still reports that it is using ~100 Mb. There are no other
> large memory users, and performance monitor says the same thing
> (although, I admit this isn't really extra evidence).. So, if no one
> has any more advice on the subject I think I might have to go the MS
> issue route, this may not be a problem but it's worrying me enough to
> want to find out what is going on. AWE is still enabled, is it possible
> that this is causing misreporting of memory in taskmanager?
Maybe SQL Server then does not need the memory although if you access
large volumes of data I'd expect memory consumption to go up.
> also, hyperthreading being switched off hasn't dramatically improved
> performance (given that there were other maintenance steps like index
> rebuild etc on the weekend) but I don't see the 80% CXPACKET waits that
> were occurring, so I feel that this must be a little better.
> The other thing that I noticed on friday is that someone (not me,
> *really*) has installed Symantec antivirus on the machine. how likely
> is it that this is causing a problem? I've switched it off for now, and
> we'll see what happens.
You should at least exclude the directory tree where DB files reside.
But if other people have access to this machine and do actually install
software there then anything can happen. This is probably something you
want to change.
Regards
robert
Showing posts with label hardware. Show all posts
Showing posts with label hardware. Show all posts
Wednesday, March 21, 2012
performance problems after upgrade to sql server 2005 sp1
we have simultaneously upgraded our server hardware and changed from
sql server 2000 to sql server 2005 and found that we have some
performance problems.
The database is of medium size (about 10Gb) and is a mix of reporting
and business transaction processing. We use a mix of 2-tier and 3-tier
components, there are about 20 users (most access through 3-tier, so
don't touch thedb directly).
The feeling -- user perception and measurment of top-line component
performance (i.e., "that big report used to run in 3 minutes and now it
takes 6") -- that things are slower than they were. They certainly
aren't faster for most operations and given that we have jumped up
quite a lot in hardware seems a little disappointing. The machine is a
hyperthreaded, dual xeon proc dell with separate RAID disks for log
and data and 4gb of ram. the pagefile is on a separate disk.
Things have improved with doing some maintenance like update stats and
reorganise indexes, but we don't have sufficient downtime during the
week to rebuild indexes.
So, is the solution to attack the areas of worst performance or is
there some general, server-wide thing that I am missing? Should I try
and find downtime to do the database tuning wizard or did people not
need to?
One of the few things that have jumped out of analysis of the wait
types is that CXPACKET can account for up to 80% of the wait time (see
sql below). Googling on that led me to
http://blogs.msdn.com/slavao/archive...12/492119.aspx
which is linked to a fair bit. We do have a hyperthreaded box, but do
people have any strong opinions on hyperthreading? Did turning it off
work for you and what kind of improvement did you see?
Finally, one oddity, is that in task manager, Sql server shows that it
is using only 80 MB (sic, I really mean Megabytes) and we have 4Gig
available. Looking at other things, it seems like most of the memory is
being used and the 80Mb is a mistake.
any help appreaciated,
sam
On 27.09.2006 17:10, sam.m.gardiner wrote:
> we have simultaneously upgraded our server hardware and changed from
> sql server 2000 to sql server 2005 and found that we have some
> performance problems.
This makes determining causes of your performance problems a bit more
difficult because now you do not know whether it's the software or the
hardware.
> The database is of medium size (about 10Gb) and is a mix of reporting
> and business transaction processing. We use a mix of 2-tier and 3-tier
> components, there are about 20 users (most access through 3-tier, so
> don't touch thedb directly).
Um, other people would put 10GB rather in the "small" category... :-)
> So, is the solution to attack the areas of worst performance or is
> there some general, server-wide thing that I am missing? Should I try
> and find downtime to do the database tuning wizard or did people not
> need to?
IMHO you don't necessarily need downtime. You can do a profiler trace
while people do the slow queries and then have it analyzed.
> One of the few things that have jumped out of analysis of the wait
> types is that CXPACKET can account for up to 80% of the wait time (see
> sql below). Googling on that led me to
> http://blogs.msdn.com/slavao/archive...12/492119.aspx
> which is linked to a fair bit. We do have a hyperthreaded box, but do
> people have any strong opinions on hyperthreading? Did turning it off
> work for you and what kind of improvement did you see?
Not exactly what you asked for but: I know that for MaxDB you must not
count hyperthreading kernels but real kernels, i.e. a one CPU machine
with HT counts as 1.
Maybe try also maxdop = 1 to see how that changes things:
http://www.sqlblogs.com/top/ng/group...ism/index.aspx
> Finally, one oddity, is that in task manager, Sql server shows that it
> is using only 80 MB (sic, I really mean Megabytes) and we have 4Gig
> available. Looking at other things, it seems like most of the memory is
> being used and the 80Mb is a mistake.
Actually, this was my first question when I read your message: what are
the memory settings for the instance in question? What do you mean by
"other things"? Does the DB use 80MB or not?
Kind regards
robert
|||There is something very wrong with your memory - possibly there is another
bottleneck.
What I normally do is to run profiler for an hour or so, then save it in a
sql table, and place indexes on cpu, reads, writes, duration. I then query
by top 100 for duration, cpu, reads, write like this
select top 100 textdata from trace order by duration desc.
This way the most expensive operations come to the top.
I then sum common operations and again order them by duration, cpu, etc. You
have to parse the statements to reduce them to the proc names to do this.
You then have an idea of which procs to improve.
Note that Microsoft has spent 5 or more years adding features to this
product and improving performance of it. They have added a lot of code to
the query engine. Some plain vanilla operations might under some
circumstances show no improvement or take longer as instead of having to go
through 2000 program statements as was the case in SQL 2000, it might now
have to go through 3000 program statement to issue the same query plan. You
might be bumping into one of these cases.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"sam.m.gardiner" <sam.m.gardiner@.gmail.com> wrote in message
news:1159369843.899959.259710@.k70g2000cwa.googlegr oups.com...
> we have simultaneously upgraded our server hardware and changed from
> sql server 2000 to sql server 2005 and found that we have some
> performance problems.
> The database is of medium size (about 10Gb) and is a mix of reporting
> and business transaction processing. We use a mix of 2-tier and 3-tier
> components, there are about 20 users (most access through 3-tier, so
> don't touch thedb directly).
> The feeling -- user perception and measurment of top-line component
> performance (i.e., "that big report used to run in 3 minutes and now it
> takes 6") -- that things are slower than they were. They certainly
> aren't faster for most operations and given that we have jumped up
> quite a lot in hardware seems a little disappointing. The machine is a
> hyperthreaded, dual xeon proc dell with separate RAID disks for log
> and data and 4gb of ram. the pagefile is on a separate disk.
> Things have improved with doing some maintenance like update stats and
> reorganise indexes, but we don't have sufficient downtime during the
> week to rebuild indexes.
> So, is the solution to attack the areas of worst performance or is
> there some general, server-wide thing that I am missing? Should I try
> and find downtime to do the database tuning wizard or did people not
> need to?
> One of the few things that have jumped out of analysis of the wait
> types is that CXPACKET can account for up to 80% of the wait time (see
> sql below). Googling on that led me to
> http://blogs.msdn.com/slavao/archive...12/492119.aspx
> which is linked to a fair bit. We do have a hyperthreaded box, but do
> people have any strong opinions on hyperthreading? Did turning it off
> work for you and what kind of improvement did you see?
> Finally, one oddity, is that in task manager, Sql server shows that it
> is using only 80 MB (sic, I really mean Megabytes) and we have 4Gig
> available. Looking at other things, it seems like most of the memory is
> being used and the 80Mb is a mistake.
> any help appreaciated,
> sam
>
|||Robert Klemme wrote:
> This makes determining causes of your performance problems a bit more
> difficult because now you do not know whether it's the software or the
> hardware.
No kidding. Memo to self, don't do this again.
> Um, other people would put 10GB rather in the "small" category... :-)
hey, it's all I have!
> IMHO you don't necessarily need downtime. You can do a profiler trace
> while people do the slow queries and then have it analyzed.
Well, I've spent quite a lot of time looking at waitstats and I have
run the profiler but as I say I don't want to spend time drilling down
on queries to find I have some gross problem with the overall setup, so
I've started with the slow components, and I'll work in from there. It
does seem like there is a general system-wide performance issue, so if
I start digging into the profiler i might not see the wood for the
trees. But I will try it soon.
What i've found is really missing from online resources is very broad
estimates for what is "normal". Obviously this varies a lot depending
on hardware and db design but i'm prepared to bet that there are
milions of installations of a similar nature; single db server with a
small ;-) database but I can't find any stats for what is normal. For
example I read in one article some things about correcting the perfmon
stats for disk IO for a RAID config.
http://www.microsoft.com/technet/pro.../tsprfprb.mspx
and it talks about number of "I/os per disk" and other articles talk
about "i/os per spindle".. but what is typical 1, 10, 100? and where
will performance problems start? I'm not asking for an answer to this
specific question, just ighlighting my general ignorance on these
things.. ;-)
> Actually, this was my first question when I read your message: what are
> the memory settings for the instance in question? What do you mean by
> "other things"? Does the DB use 80MB or not?
the memory settings are the out of the box ones, i.e., the max memory
usage is 2147483647MB which is obviously way more than we have. I have
seen the sql server running at what is "normal" for this machine, i.e,.
1.7 Gb but since a restart (to move the page file) the task manager
shows approx 80 Mb. However, the total memory free (seen from task
manager) is only 150 Mb so something must be using up the rest (and
there are no other big apps running). I assume that it is really SQL
server and this is just being misreported. is this a problem or should
it be a issue with Microsoft?
thanks a lot for your interest, I know my problem is vague and hard to
diagnose.
sam
|||On 27.09.2006 19:04, sam.m.gardiner wrote:
> Robert Klemme wrote:
>
> No kidding. Memo to self, don't do this again.
Good.
> and it talks about number of "I/os per disk" and other articles talk
> about "i/os per spindle".. but what is typical 1, 10, 100? and where
> will performance problems start? I'm not asking for an answer to this
> specific question, just ighlighting my general ignorance on these
> things.. ;-)
There is only one rule: there is no general rule. Every DB and
application is different and there are no general values (other than
maybe for the demo databases - but I do not know whether someone
actually uses them for benchmarking).
> the memory settings are the out of the box ones, i.e., the max memory
> usage is 2147483647MB which is obviously way more than we have. I have
> seen the sql server running at what is "normal" for this machine, i.e,.
> 1.7 Gb but since a restart (to move the page file) the task manager
> shows approx 80 Mb. However, the total memory free (seen from task
> manager) is only 150 Mb so something must be using up the rest (and
> there are no other big apps running). I assume that it is really SQL
> server and this is just being misreported. is this a problem or should
> it be a issue with Microsoft?
Then start with the obvious and change the memory setting in a way that
SQL Server won't use more than 80~90% of your physical memory. You know
what happens if it tries to use more mem, do you...?
Cheers
robert
|||OK so we changed the max memory setting to 3000 MB (we have 4GB) but
sql server still reports that it is using ~100 Mb. There are no other
large memory users, and performance monitor says the same thing
(although, I admit this isn't really extra evidence).. So, if no one
has any more advice on the subject I think I might have to go the MS
issue route, this may not be a problem but it's worrying me enough to
want to find out what is going on. AWE is still enabled, is it possible
that this is causing misreporting of memory in taskmanager?
also, hyperthreading being switched off hasn't dramatically improved
performance (given that there were other maintenance steps like index
rebuild etc on the weekend) but I don't see the 80% CXPACKET waits that
were occurring, so I feel that this must be a little better.
The other thing that I noticed on friday is that someone (not me,
*really*) has installed Symantec antivirus on the machine. how likely
is it that this is causing a problem? I've switched it off for now, and
we'll see what happens.
thanks for your help,
sam
|||On 02.10.2006 13:38, sam.m.gardiner wrote:
> OK so we changed the max memory setting to 3000 MB (we have 4GB) but
> sql server still reports that it is using ~100 Mb. There are no other
> large memory users, and performance monitor says the same thing
> (although, I admit this isn't really extra evidence).. So, if no one
> has any more advice on the subject I think I might have to go the MS
> issue route, this may not be a problem but it's worrying me enough to
> want to find out what is going on. AWE is still enabled, is it possible
> that this is causing misreporting of memory in taskmanager?
Maybe SQL Server then does not need the memory although if you access
large volumes of data I'd expect memory consumption to go up.
> also, hyperthreading being switched off hasn't dramatically improved
> performance (given that there were other maintenance steps like index
> rebuild etc on the weekend) but I don't see the 80% CXPACKET waits that
> were occurring, so I feel that this must be a little better.
> The other thing that I noticed on friday is that someone (not me,
> *really*) has installed Symantec antivirus on the machine. how likely
> is it that this is causing a problem? I've switched it off for now, and
> we'll see what happens.
You should at least exclude the directory tree where DB files reside.
But if other people have access to this machine and do actually install
software there then anything can happen. This is probably something you
want to change.
Regards
robert
sql
sql server 2000 to sql server 2005 and found that we have some
performance problems.
The database is of medium size (about 10Gb) and is a mix of reporting
and business transaction processing. We use a mix of 2-tier and 3-tier
components, there are about 20 users (most access through 3-tier, so
don't touch thedb directly).
The feeling -- user perception and measurment of top-line component
performance (i.e., "that big report used to run in 3 minutes and now it
takes 6") -- that things are slower than they were. They certainly
aren't faster for most operations and given that we have jumped up
quite a lot in hardware seems a little disappointing. The machine is a
hyperthreaded, dual xeon proc dell with separate RAID disks for log
and data and 4gb of ram. the pagefile is on a separate disk.
Things have improved with doing some maintenance like update stats and
reorganise indexes, but we don't have sufficient downtime during the
week to rebuild indexes.
So, is the solution to attack the areas of worst performance or is
there some general, server-wide thing that I am missing? Should I try
and find downtime to do the database tuning wizard or did people not
need to?
One of the few things that have jumped out of analysis of the wait
types is that CXPACKET can account for up to 80% of the wait time (see
sql below). Googling on that led me to
http://blogs.msdn.com/slavao/archive...12/492119.aspx
which is linked to a fair bit. We do have a hyperthreaded box, but do
people have any strong opinions on hyperthreading? Did turning it off
work for you and what kind of improvement did you see?
Finally, one oddity, is that in task manager, Sql server shows that it
is using only 80 MB (sic, I really mean Megabytes) and we have 4Gig
available. Looking at other things, it seems like most of the memory is
being used and the 80Mb is a mistake.
any help appreaciated,
sam
On 27.09.2006 17:10, sam.m.gardiner wrote:
> we have simultaneously upgraded our server hardware and changed from
> sql server 2000 to sql server 2005 and found that we have some
> performance problems.
This makes determining causes of your performance problems a bit more
difficult because now you do not know whether it's the software or the
hardware.
> The database is of medium size (about 10Gb) and is a mix of reporting
> and business transaction processing. We use a mix of 2-tier and 3-tier
> components, there are about 20 users (most access through 3-tier, so
> don't touch thedb directly).
Um, other people would put 10GB rather in the "small" category... :-)
> So, is the solution to attack the areas of worst performance or is
> there some general, server-wide thing that I am missing? Should I try
> and find downtime to do the database tuning wizard or did people not
> need to?
IMHO you don't necessarily need downtime. You can do a profiler trace
while people do the slow queries and then have it analyzed.
> One of the few things that have jumped out of analysis of the wait
> types is that CXPACKET can account for up to 80% of the wait time (see
> sql below). Googling on that led me to
> http://blogs.msdn.com/slavao/archive...12/492119.aspx
> which is linked to a fair bit. We do have a hyperthreaded box, but do
> people have any strong opinions on hyperthreading? Did turning it off
> work for you and what kind of improvement did you see?
Not exactly what you asked for but: I know that for MaxDB you must not
count hyperthreading kernels but real kernels, i.e. a one CPU machine
with HT counts as 1.
Maybe try also maxdop = 1 to see how that changes things:
http://www.sqlblogs.com/top/ng/group...ism/index.aspx
> Finally, one oddity, is that in task manager, Sql server shows that it
> is using only 80 MB (sic, I really mean Megabytes) and we have 4Gig
> available. Looking at other things, it seems like most of the memory is
> being used and the 80Mb is a mistake.
Actually, this was my first question when I read your message: what are
the memory settings for the instance in question? What do you mean by
"other things"? Does the DB use 80MB or not?
Kind regards
robert
|||There is something very wrong with your memory - possibly there is another
bottleneck.
What I normally do is to run profiler for an hour or so, then save it in a
sql table, and place indexes on cpu, reads, writes, duration. I then query
by top 100 for duration, cpu, reads, write like this
select top 100 textdata from trace order by duration desc.
This way the most expensive operations come to the top.
I then sum common operations and again order them by duration, cpu, etc. You
have to parse the statements to reduce them to the proc names to do this.
You then have an idea of which procs to improve.
Note that Microsoft has spent 5 or more years adding features to this
product and improving performance of it. They have added a lot of code to
the query engine. Some plain vanilla operations might under some
circumstances show no improvement or take longer as instead of having to go
through 2000 program statements as was the case in SQL 2000, it might now
have to go through 3000 program statement to issue the same query plan. You
might be bumping into one of these cases.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"sam.m.gardiner" <sam.m.gardiner@.gmail.com> wrote in message
news:1159369843.899959.259710@.k70g2000cwa.googlegr oups.com...
> we have simultaneously upgraded our server hardware and changed from
> sql server 2000 to sql server 2005 and found that we have some
> performance problems.
> The database is of medium size (about 10Gb) and is a mix of reporting
> and business transaction processing. We use a mix of 2-tier and 3-tier
> components, there are about 20 users (most access through 3-tier, so
> don't touch thedb directly).
> The feeling -- user perception and measurment of top-line component
> performance (i.e., "that big report used to run in 3 minutes and now it
> takes 6") -- that things are slower than they were. They certainly
> aren't faster for most operations and given that we have jumped up
> quite a lot in hardware seems a little disappointing. The machine is a
> hyperthreaded, dual xeon proc dell with separate RAID disks for log
> and data and 4gb of ram. the pagefile is on a separate disk.
> Things have improved with doing some maintenance like update stats and
> reorganise indexes, but we don't have sufficient downtime during the
> week to rebuild indexes.
> So, is the solution to attack the areas of worst performance or is
> there some general, server-wide thing that I am missing? Should I try
> and find downtime to do the database tuning wizard or did people not
> need to?
> One of the few things that have jumped out of analysis of the wait
> types is that CXPACKET can account for up to 80% of the wait time (see
> sql below). Googling on that led me to
> http://blogs.msdn.com/slavao/archive...12/492119.aspx
> which is linked to a fair bit. We do have a hyperthreaded box, but do
> people have any strong opinions on hyperthreading? Did turning it off
> work for you and what kind of improvement did you see?
> Finally, one oddity, is that in task manager, Sql server shows that it
> is using only 80 MB (sic, I really mean Megabytes) and we have 4Gig
> available. Looking at other things, it seems like most of the memory is
> being used and the 80Mb is a mistake.
> any help appreaciated,
> sam
>
|||Robert Klemme wrote:
> This makes determining causes of your performance problems a bit more
> difficult because now you do not know whether it's the software or the
> hardware.
No kidding. Memo to self, don't do this again.
> Um, other people would put 10GB rather in the "small" category... :-)
hey, it's all I have!
> IMHO you don't necessarily need downtime. You can do a profiler trace
> while people do the slow queries and then have it analyzed.
Well, I've spent quite a lot of time looking at waitstats and I have
run the profiler but as I say I don't want to spend time drilling down
on queries to find I have some gross problem with the overall setup, so
I've started with the slow components, and I'll work in from there. It
does seem like there is a general system-wide performance issue, so if
I start digging into the profiler i might not see the wood for the
trees. But I will try it soon.
What i've found is really missing from online resources is very broad
estimates for what is "normal". Obviously this varies a lot depending
on hardware and db design but i'm prepared to bet that there are
milions of installations of a similar nature; single db server with a
small ;-) database but I can't find any stats for what is normal. For
example I read in one article some things about correcting the perfmon
stats for disk IO for a RAID config.
http://www.microsoft.com/technet/pro.../tsprfprb.mspx
and it talks about number of "I/os per disk" and other articles talk
about "i/os per spindle".. but what is typical 1, 10, 100? and where
will performance problems start? I'm not asking for an answer to this
specific question, just ighlighting my general ignorance on these
things.. ;-)
> Actually, this was my first question when I read your message: what are
> the memory settings for the instance in question? What do you mean by
> "other things"? Does the DB use 80MB or not?
the memory settings are the out of the box ones, i.e., the max memory
usage is 2147483647MB which is obviously way more than we have. I have
seen the sql server running at what is "normal" for this machine, i.e,.
1.7 Gb but since a restart (to move the page file) the task manager
shows approx 80 Mb. However, the total memory free (seen from task
manager) is only 150 Mb so something must be using up the rest (and
there are no other big apps running). I assume that it is really SQL
server and this is just being misreported. is this a problem or should
it be a issue with Microsoft?
thanks a lot for your interest, I know my problem is vague and hard to
diagnose.
sam
|||On 27.09.2006 19:04, sam.m.gardiner wrote:
> Robert Klemme wrote:
>
> No kidding. Memo to self, don't do this again.
Good.
> and it talks about number of "I/os per disk" and other articles talk
> about "i/os per spindle".. but what is typical 1, 10, 100? and where
> will performance problems start? I'm not asking for an answer to this
> specific question, just ighlighting my general ignorance on these
> things.. ;-)
There is only one rule: there is no general rule. Every DB and
application is different and there are no general values (other than
maybe for the demo databases - but I do not know whether someone
actually uses them for benchmarking).
> the memory settings are the out of the box ones, i.e., the max memory
> usage is 2147483647MB which is obviously way more than we have. I have
> seen the sql server running at what is "normal" for this machine, i.e,.
> 1.7 Gb but since a restart (to move the page file) the task manager
> shows approx 80 Mb. However, the total memory free (seen from task
> manager) is only 150 Mb so something must be using up the rest (and
> there are no other big apps running). I assume that it is really SQL
> server and this is just being misreported. is this a problem or should
> it be a issue with Microsoft?
Then start with the obvious and change the memory setting in a way that
SQL Server won't use more than 80~90% of your physical memory. You know
what happens if it tries to use more mem, do you...?
Cheers
robert
|||OK so we changed the max memory setting to 3000 MB (we have 4GB) but
sql server still reports that it is using ~100 Mb. There are no other
large memory users, and performance monitor says the same thing
(although, I admit this isn't really extra evidence).. So, if no one
has any more advice on the subject I think I might have to go the MS
issue route, this may not be a problem but it's worrying me enough to
want to find out what is going on. AWE is still enabled, is it possible
that this is causing misreporting of memory in taskmanager?
also, hyperthreading being switched off hasn't dramatically improved
performance (given that there were other maintenance steps like index
rebuild etc on the weekend) but I don't see the 80% CXPACKET waits that
were occurring, so I feel that this must be a little better.
The other thing that I noticed on friday is that someone (not me,
*really*) has installed Symantec antivirus on the machine. how likely
is it that this is causing a problem? I've switched it off for now, and
we'll see what happens.
thanks for your help,
sam
|||On 02.10.2006 13:38, sam.m.gardiner wrote:
> OK so we changed the max memory setting to 3000 MB (we have 4GB) but
> sql server still reports that it is using ~100 Mb. There are no other
> large memory users, and performance monitor says the same thing
> (although, I admit this isn't really extra evidence).. So, if no one
> has any more advice on the subject I think I might have to go the MS
> issue route, this may not be a problem but it's worrying me enough to
> want to find out what is going on. AWE is still enabled, is it possible
> that this is causing misreporting of memory in taskmanager?
Maybe SQL Server then does not need the memory although if you access
large volumes of data I'd expect memory consumption to go up.
> also, hyperthreading being switched off hasn't dramatically improved
> performance (given that there were other maintenance steps like index
> rebuild etc on the weekend) but I don't see the 80% CXPACKET waits that
> were occurring, so I feel that this must be a little better.
> The other thing that I noticed on friday is that someone (not me,
> *really*) has installed Symantec antivirus on the machine. how likely
> is it that this is causing a problem? I've switched it off for now, and
> we'll see what happens.
You should at least exclude the directory tree where DB files reside.
But if other people have access to this machine and do actually install
software there then anything can happen. This is probably something you
want to change.
Regards
robert
sql
performance problems after upgrade to sql server 2005 sp1
we have simultaneously upgraded our server hardware and changed from
sql server 2000 to sql server 2005 and found that we have some
performance problems.
The database is of medium size (about 10Gb) and is a mix of reporting
and business transaction processing. We use a mix of 2-tier and 3-tier
components, there are about 20 users (most access through 3-tier, so
don't touch thedb directly).
The feeling -- user perception and measurment of top-line component
performance (i.e., "that big report used to run in 3 minutes and now it
takes 6") -- that things are slower than they were. They certainly
aren't faster for most operations and given that we have jumped up
quite a lot in hardware seems a little disappointing. The machine is a
hyperthreaded, dual xeon proc dell with separate RAID disks for log
and data and 4gb of ram. the pagefile is on a separate disk.
Things have improved with doing some maintenance like update stats and
reorganise indexes, but we don't have sufficient downtime during the
week to rebuild indexes.
So, is the solution to attack the areas of worst performance or is
there some general, server-wide thing that I am missing? Should I try
and find downtime to do the database tuning wizard or did people not
need to?
One of the few things that have jumped out of analysis of the wait
types is that CXPACKET can account for up to 80% of the wait time (see
sql below). Googling on that led me to
http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx
which is linked to a fair bit. We do have a hyperthreaded box, but do
people have any strong opinions on hyperthreading? Did turning it off
work for you and what kind of improvement did you see?
Finally, one oddity, is that in task manager, Sql server shows that it
is using only 80 MB (sic, I really mean Megabytes) and we have 4Gig
available. Looking at other things, it seems like most of the memory is
being used and the 80Mb is a mistake.
any help appreaciated,
samOn 27.09.2006 17:10, sam.m.gardiner wrote:
> we have simultaneously upgraded our server hardware and changed from
> sql server 2000 to sql server 2005 and found that we have some
> performance problems.
This makes determining causes of your performance problems a bit more
difficult because now you do not know whether it's the software or the
hardware.
> The database is of medium size (about 10Gb) and is a mix of reporting
> and business transaction processing. We use a mix of 2-tier and 3-tier
> components, there are about 20 users (most access through 3-tier, so
> don't touch thedb directly).
Um, other people would put 10GB rather in the "small" category... :-)
> So, is the solution to attack the areas of worst performance or is
> there some general, server-wide thing that I am missing? Should I try
> and find downtime to do the database tuning wizard or did people not
> need to?
IMHO you don't necessarily need downtime. You can do a profiler trace
while people do the slow queries and then have it analyzed.
> One of the few things that have jumped out of analysis of the wait
> types is that CXPACKET can account for up to 80% of the wait time (see
> sql below). Googling on that led me to
> http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx
> which is linked to a fair bit. We do have a hyperthreaded box, but do
> people have any strong opinions on hyperthreading? Did turning it off
> work for you and what kind of improvement did you see?
Not exactly what you asked for but: I know that for MaxDB you must not
count hyperthreading kernels but real kernels, i.e. a one CPU machine
with HT counts as 1.
Maybe try also maxdop = 1 to see how that changes things:
http://www.sqlblogs.com/top/ng/group~2/~138495~__CXPACKET-and-max-degree-of-parallelism/index.aspx
> Finally, one oddity, is that in task manager, Sql server shows that it
> is using only 80 MB (sic, I really mean Megabytes) and we have 4Gig
> available. Looking at other things, it seems like most of the memory is
> being used and the 80Mb is a mistake.
Actually, this was my first question when I read your message: what are
the memory settings for the instance in question? What do you mean by
"other things"? Does the DB use 80MB or not?
Kind regards
robert|||There is something very wrong with your memory - possibly there is another
bottleneck.
What I normally do is to run profiler for an hour or so, then save it in a
sql table, and place indexes on cpu, reads, writes, duration. I then query
by top 100 for duration, cpu, reads, write like this
select top 100 textdata from trace order by duration desc.
This way the most expensive operations come to the top.
I then sum common operations and again order them by duration, cpu, etc. You
have to parse the statements to reduce them to the proc names to do this.
You then have an idea of which procs to improve.
Note that Microsoft has spent 5 or more years adding features to this
product and improving performance of it. They have added a lot of code to
the query engine. Some plain vanilla operations might under some
circumstances show no improvement or take longer as instead of having to go
through 2000 program statements as was the case in SQL 2000, it might now
have to go through 3000 program statement to issue the same query plan. You
might be bumping into one of these cases.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"sam.m.gardiner" <sam.m.gardiner@.gmail.com> wrote in message
news:1159369843.899959.259710@.k70g2000cwa.googlegroups.com...
> we have simultaneously upgraded our server hardware and changed from
> sql server 2000 to sql server 2005 and found that we have some
> performance problems.
> The database is of medium size (about 10Gb) and is a mix of reporting
> and business transaction processing. We use a mix of 2-tier and 3-tier
> components, there are about 20 users (most access through 3-tier, so
> don't touch thedb directly).
> The feeling -- user perception and measurment of top-line component
> performance (i.e., "that big report used to run in 3 minutes and now it
> takes 6") -- that things are slower than they were. They certainly
> aren't faster for most operations and given that we have jumped up
> quite a lot in hardware seems a little disappointing. The machine is a
> hyperthreaded, dual xeon proc dell with separate RAID disks for log
> and data and 4gb of ram. the pagefile is on a separate disk.
> Things have improved with doing some maintenance like update stats and
> reorganise indexes, but we don't have sufficient downtime during the
> week to rebuild indexes.
> So, is the solution to attack the areas of worst performance or is
> there some general, server-wide thing that I am missing? Should I try
> and find downtime to do the database tuning wizard or did people not
> need to?
> One of the few things that have jumped out of analysis of the wait
> types is that CXPACKET can account for up to 80% of the wait time (see
> sql below). Googling on that led me to
> http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx
> which is linked to a fair bit. We do have a hyperthreaded box, but do
> people have any strong opinions on hyperthreading? Did turning it off
> work for you and what kind of improvement did you see?
> Finally, one oddity, is that in task manager, Sql server shows that it
> is using only 80 MB (sic, I really mean Megabytes) and we have 4Gig
> available. Looking at other things, it seems like most of the memory is
> being used and the 80Mb is a mistake.
> any help appreaciated,
> sam
>|||Robert Klemme wrote:
> This makes determining causes of your performance problems a bit more
> difficult because now you do not know whether it's the software or the
> hardware.
No kidding. Memo to self, don't do this again.
> Um, other people would put 10GB rather in the "small" category... :-)
hey, it's all I have!
> IMHO you don't necessarily need downtime. You can do a profiler trace
> while people do the slow queries and then have it analyzed.
Well, I've spent quite a lot of time looking at waitstats and I have
run the profiler but as I say I don't want to spend time drilling down
on queries to find I have some gross problem with the overall setup, so
I've started with the slow components, and I'll work in from there. It
does seem like there is a general system-wide performance issue, so if
I start digging into the profiler i might not see the wood for the
trees. But I will try it soon.
What i've found is really missing from online resources is very broad
estimates for what is "normal". Obviously this varies a lot depending
on hardware and db design but i'm prepared to bet that there are
milions of installations of a similar nature; single db server with a
small ;-) database but I can't find any stats for what is normal. For
example I read in one article some things about correcting the perfmon
stats for disk IO for a RAID config.
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
and it talks about number of "I/os per disk" and other articles talk
about "i/os per spindle".. but what is typical 1, 10, 100? and where
will performance problems start? I'm not asking for an answer to this
specific question, just ighlighting my general ignorance on these
things.. ;-)
> Actually, this was my first question when I read your message: what are
> the memory settings for the instance in question? What do you mean by
> "other things"? Does the DB use 80MB or not?
the memory settings are the out of the box ones, i.e., the max memory
usage is 2147483647MB which is obviously way more than we have. I have
seen the sql server running at what is "normal" for this machine, i.e,.
1.7 Gb but since a restart (to move the page file) the task manager
shows approx 80 Mb. However, the total memory free (seen from task
manager) is only 150 Mb so something must be using up the rest (and
there are no other big apps running). I assume that it is really SQL
server and this is just being misreported. is this a problem or should
it be a issue with Microsoft?
thanks a lot for your interest, I know my problem is vague and hard to
diagnose.
sam|||On 27.09.2006 19:04, sam.m.gardiner wrote:
> Robert Klemme wrote:
>> This makes determining causes of your performance problems a bit more
>> difficult because now you do not know whether it's the software or the
>> hardware.
> No kidding. Memo to self, don't do this again.
Good.
> and it talks about number of "I/os per disk" and other articles talk
> about "i/os per spindle".. but what is typical 1, 10, 100? and where
> will performance problems start? I'm not asking for an answer to this
> specific question, just ighlighting my general ignorance on these
> things.. ;-)
There is only one rule: there is no general rule. Every DB and
application is different and there are no general values (other than
maybe for the demo databases - but I do not know whether someone
actually uses them for benchmarking).
> the memory settings are the out of the box ones, i.e., the max memory
> usage is 2147483647MB which is obviously way more than we have. I have
> seen the sql server running at what is "normal" for this machine, i.e,.
> 1.7 Gb but since a restart (to move the page file) the task manager
> shows approx 80 Mb. However, the total memory free (seen from task
> manager) is only 150 Mb so something must be using up the rest (and
> there are no other big apps running). I assume that it is really SQL
> server and this is just being misreported. is this a problem or should
> it be a issue with Microsoft?
Then start with the obvious and change the memory setting in a way that
SQL Server won't use more than 80~90% of your physical memory. You know
what happens if it tries to use more mem, do you...?
Cheers
robert|||OK so we changed the max memory setting to 3000 MB (we have 4GB) but
sql server still reports that it is using ~100 Mb. There are no other
large memory users, and performance monitor says the same thing
(although, I admit this isn't really extra evidence).. So, if no one
has any more advice on the subject I think I might have to go the MS
issue route, this may not be a problem but it's worrying me enough to
want to find out what is going on. AWE is still enabled, is it possible
that this is causing misreporting of memory in taskmanager?
also, hyperthreading being switched off hasn't dramatically improved
performance (given that there were other maintenance steps like index
rebuild etc on the weekend) but I don't see the 80% CXPACKET waits that
were occurring, so I feel that this must be a little better.
The other thing that I noticed on friday is that someone (not me,
*really*) has installed Symantec antivirus on the machine. how likely
is it that this is causing a problem? I've switched it off for now, and
we'll see what happens.
thanks for your help,
sam|||On 02.10.2006 13:38, sam.m.gardiner wrote:
> OK so we changed the max memory setting to 3000 MB (we have 4GB) but
> sql server still reports that it is using ~100 Mb. There are no other
> large memory users, and performance monitor says the same thing
> (although, I admit this isn't really extra evidence).. So, if no one
> has any more advice on the subject I think I might have to go the MS
> issue route, this may not be a problem but it's worrying me enough to
> want to find out what is going on. AWE is still enabled, is it possible
> that this is causing misreporting of memory in taskmanager?
Maybe SQL Server then does not need the memory although if you access
large volumes of data I'd expect memory consumption to go up.
> also, hyperthreading being switched off hasn't dramatically improved
> performance (given that there were other maintenance steps like index
> rebuild etc on the weekend) but I don't see the 80% CXPACKET waits that
> were occurring, so I feel that this must be a little better.
> The other thing that I noticed on friday is that someone (not me,
> *really*) has installed Symantec antivirus on the machine. how likely
> is it that this is causing a problem? I've switched it off for now, and
> we'll see what happens.
You should at least exclude the directory tree where DB files reside.
But if other people have access to this machine and do actually install
software there then anything can happen. This is probably something you
want to change.
Regards
robert
sql server 2000 to sql server 2005 and found that we have some
performance problems.
The database is of medium size (about 10Gb) and is a mix of reporting
and business transaction processing. We use a mix of 2-tier and 3-tier
components, there are about 20 users (most access through 3-tier, so
don't touch thedb directly).
The feeling -- user perception and measurment of top-line component
performance (i.e., "that big report used to run in 3 minutes and now it
takes 6") -- that things are slower than they were. They certainly
aren't faster for most operations and given that we have jumped up
quite a lot in hardware seems a little disappointing. The machine is a
hyperthreaded, dual xeon proc dell with separate RAID disks for log
and data and 4gb of ram. the pagefile is on a separate disk.
Things have improved with doing some maintenance like update stats and
reorganise indexes, but we don't have sufficient downtime during the
week to rebuild indexes.
So, is the solution to attack the areas of worst performance or is
there some general, server-wide thing that I am missing? Should I try
and find downtime to do the database tuning wizard or did people not
need to?
One of the few things that have jumped out of analysis of the wait
types is that CXPACKET can account for up to 80% of the wait time (see
sql below). Googling on that led me to
http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx
which is linked to a fair bit. We do have a hyperthreaded box, but do
people have any strong opinions on hyperthreading? Did turning it off
work for you and what kind of improvement did you see?
Finally, one oddity, is that in task manager, Sql server shows that it
is using only 80 MB (sic, I really mean Megabytes) and we have 4Gig
available. Looking at other things, it seems like most of the memory is
being used and the 80Mb is a mistake.
any help appreaciated,
samOn 27.09.2006 17:10, sam.m.gardiner wrote:
> we have simultaneously upgraded our server hardware and changed from
> sql server 2000 to sql server 2005 and found that we have some
> performance problems.
This makes determining causes of your performance problems a bit more
difficult because now you do not know whether it's the software or the
hardware.
> The database is of medium size (about 10Gb) and is a mix of reporting
> and business transaction processing. We use a mix of 2-tier and 3-tier
> components, there are about 20 users (most access through 3-tier, so
> don't touch thedb directly).
Um, other people would put 10GB rather in the "small" category... :-)
> So, is the solution to attack the areas of worst performance or is
> there some general, server-wide thing that I am missing? Should I try
> and find downtime to do the database tuning wizard or did people not
> need to?
IMHO you don't necessarily need downtime. You can do a profiler trace
while people do the slow queries and then have it analyzed.
> One of the few things that have jumped out of analysis of the wait
> types is that CXPACKET can account for up to 80% of the wait time (see
> sql below). Googling on that led me to
> http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx
> which is linked to a fair bit. We do have a hyperthreaded box, but do
> people have any strong opinions on hyperthreading? Did turning it off
> work for you and what kind of improvement did you see?
Not exactly what you asked for but: I know that for MaxDB you must not
count hyperthreading kernels but real kernels, i.e. a one CPU machine
with HT counts as 1.
Maybe try also maxdop = 1 to see how that changes things:
http://www.sqlblogs.com/top/ng/group~2/~138495~__CXPACKET-and-max-degree-of-parallelism/index.aspx
> Finally, one oddity, is that in task manager, Sql server shows that it
> is using only 80 MB (sic, I really mean Megabytes) and we have 4Gig
> available. Looking at other things, it seems like most of the memory is
> being used and the 80Mb is a mistake.
Actually, this was my first question when I read your message: what are
the memory settings for the instance in question? What do you mean by
"other things"? Does the DB use 80MB or not?
Kind regards
robert|||There is something very wrong with your memory - possibly there is another
bottleneck.
What I normally do is to run profiler for an hour or so, then save it in a
sql table, and place indexes on cpu, reads, writes, duration. I then query
by top 100 for duration, cpu, reads, write like this
select top 100 textdata from trace order by duration desc.
This way the most expensive operations come to the top.
I then sum common operations and again order them by duration, cpu, etc. You
have to parse the statements to reduce them to the proc names to do this.
You then have an idea of which procs to improve.
Note that Microsoft has spent 5 or more years adding features to this
product and improving performance of it. They have added a lot of code to
the query engine. Some plain vanilla operations might under some
circumstances show no improvement or take longer as instead of having to go
through 2000 program statements as was the case in SQL 2000, it might now
have to go through 3000 program statement to issue the same query plan. You
might be bumping into one of these cases.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"sam.m.gardiner" <sam.m.gardiner@.gmail.com> wrote in message
news:1159369843.899959.259710@.k70g2000cwa.googlegroups.com...
> we have simultaneously upgraded our server hardware and changed from
> sql server 2000 to sql server 2005 and found that we have some
> performance problems.
> The database is of medium size (about 10Gb) and is a mix of reporting
> and business transaction processing. We use a mix of 2-tier and 3-tier
> components, there are about 20 users (most access through 3-tier, so
> don't touch thedb directly).
> The feeling -- user perception and measurment of top-line component
> performance (i.e., "that big report used to run in 3 minutes and now it
> takes 6") -- that things are slower than they were. They certainly
> aren't faster for most operations and given that we have jumped up
> quite a lot in hardware seems a little disappointing. The machine is a
> hyperthreaded, dual xeon proc dell with separate RAID disks for log
> and data and 4gb of ram. the pagefile is on a separate disk.
> Things have improved with doing some maintenance like update stats and
> reorganise indexes, but we don't have sufficient downtime during the
> week to rebuild indexes.
> So, is the solution to attack the areas of worst performance or is
> there some general, server-wide thing that I am missing? Should I try
> and find downtime to do the database tuning wizard or did people not
> need to?
> One of the few things that have jumped out of analysis of the wait
> types is that CXPACKET can account for up to 80% of the wait time (see
> sql below). Googling on that led me to
> http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx
> which is linked to a fair bit. We do have a hyperthreaded box, but do
> people have any strong opinions on hyperthreading? Did turning it off
> work for you and what kind of improvement did you see?
> Finally, one oddity, is that in task manager, Sql server shows that it
> is using only 80 MB (sic, I really mean Megabytes) and we have 4Gig
> available. Looking at other things, it seems like most of the memory is
> being used and the 80Mb is a mistake.
> any help appreaciated,
> sam
>|||Robert Klemme wrote:
> This makes determining causes of your performance problems a bit more
> difficult because now you do not know whether it's the software or the
> hardware.
No kidding. Memo to self, don't do this again.
> Um, other people would put 10GB rather in the "small" category... :-)
hey, it's all I have!
> IMHO you don't necessarily need downtime. You can do a profiler trace
> while people do the slow queries and then have it analyzed.
Well, I've spent quite a lot of time looking at waitstats and I have
run the profiler but as I say I don't want to spend time drilling down
on queries to find I have some gross problem with the overall setup, so
I've started with the slow components, and I'll work in from there. It
does seem like there is a general system-wide performance issue, so if
I start digging into the profiler i might not see the wood for the
trees. But I will try it soon.
What i've found is really missing from online resources is very broad
estimates for what is "normal". Obviously this varies a lot depending
on hardware and db design but i'm prepared to bet that there are
milions of installations of a similar nature; single db server with a
small ;-) database but I can't find any stats for what is normal. For
example I read in one article some things about correcting the perfmon
stats for disk IO for a RAID config.
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
and it talks about number of "I/os per disk" and other articles talk
about "i/os per spindle".. but what is typical 1, 10, 100? and where
will performance problems start? I'm not asking for an answer to this
specific question, just ighlighting my general ignorance on these
things.. ;-)
> Actually, this was my first question when I read your message: what are
> the memory settings for the instance in question? What do you mean by
> "other things"? Does the DB use 80MB or not?
the memory settings are the out of the box ones, i.e., the max memory
usage is 2147483647MB which is obviously way more than we have. I have
seen the sql server running at what is "normal" for this machine, i.e,.
1.7 Gb but since a restart (to move the page file) the task manager
shows approx 80 Mb. However, the total memory free (seen from task
manager) is only 150 Mb so something must be using up the rest (and
there are no other big apps running). I assume that it is really SQL
server and this is just being misreported. is this a problem or should
it be a issue with Microsoft?
thanks a lot for your interest, I know my problem is vague and hard to
diagnose.
sam|||On 27.09.2006 19:04, sam.m.gardiner wrote:
> Robert Klemme wrote:
>> This makes determining causes of your performance problems a bit more
>> difficult because now you do not know whether it's the software or the
>> hardware.
> No kidding. Memo to self, don't do this again.
Good.
> and it talks about number of "I/os per disk" and other articles talk
> about "i/os per spindle".. but what is typical 1, 10, 100? and where
> will performance problems start? I'm not asking for an answer to this
> specific question, just ighlighting my general ignorance on these
> things.. ;-)
There is only one rule: there is no general rule. Every DB and
application is different and there are no general values (other than
maybe for the demo databases - but I do not know whether someone
actually uses them for benchmarking).
> the memory settings are the out of the box ones, i.e., the max memory
> usage is 2147483647MB which is obviously way more than we have. I have
> seen the sql server running at what is "normal" for this machine, i.e,.
> 1.7 Gb but since a restart (to move the page file) the task manager
> shows approx 80 Mb. However, the total memory free (seen from task
> manager) is only 150 Mb so something must be using up the rest (and
> there are no other big apps running). I assume that it is really SQL
> server and this is just being misreported. is this a problem or should
> it be a issue with Microsoft?
Then start with the obvious and change the memory setting in a way that
SQL Server won't use more than 80~90% of your physical memory. You know
what happens if it tries to use more mem, do you...?
Cheers
robert|||OK so we changed the max memory setting to 3000 MB (we have 4GB) but
sql server still reports that it is using ~100 Mb. There are no other
large memory users, and performance monitor says the same thing
(although, I admit this isn't really extra evidence).. So, if no one
has any more advice on the subject I think I might have to go the MS
issue route, this may not be a problem but it's worrying me enough to
want to find out what is going on. AWE is still enabled, is it possible
that this is causing misreporting of memory in taskmanager?
also, hyperthreading being switched off hasn't dramatically improved
performance (given that there were other maintenance steps like index
rebuild etc on the weekend) but I don't see the 80% CXPACKET waits that
were occurring, so I feel that this must be a little better.
The other thing that I noticed on friday is that someone (not me,
*really*) has installed Symantec antivirus on the machine. how likely
is it that this is causing a problem? I've switched it off for now, and
we'll see what happens.
thanks for your help,
sam|||On 02.10.2006 13:38, sam.m.gardiner wrote:
> OK so we changed the max memory setting to 3000 MB (we have 4GB) but
> sql server still reports that it is using ~100 Mb. There are no other
> large memory users, and performance monitor says the same thing
> (although, I admit this isn't really extra evidence).. So, if no one
> has any more advice on the subject I think I might have to go the MS
> issue route, this may not be a problem but it's worrying me enough to
> want to find out what is going on. AWE is still enabled, is it possible
> that this is causing misreporting of memory in taskmanager?
Maybe SQL Server then does not need the memory although if you access
large volumes of data I'd expect memory consumption to go up.
> also, hyperthreading being switched off hasn't dramatically improved
> performance (given that there were other maintenance steps like index
> rebuild etc on the weekend) but I don't see the 80% CXPACKET waits that
> were occurring, so I feel that this must be a little better.
> The other thing that I noticed on friday is that someone (not me,
> *really*) has installed Symantec antivirus on the machine. how likely
> is it that this is causing a problem? I've switched it off for now, and
> we'll see what happens.
You should at least exclude the directory tree where DB files reside.
But if other people have access to this machine and do actually install
software there then anything can happen. This is probably something you
want to change.
Regards
robert
Tuesday, March 20, 2012
Performance Problem on SQL Server 2000 SP 4 on Win2003
Hello,
we migrate an application from old to new hardware. In this step we migrate
also from SQL Server 2000 SP 2 on a Windows 2000 Server to SQL Server 2000
SP 4 on a Windows 2003 Server. Because of the new hardware almost every job
on the database ist much faster (up to 3 and 4 times) than before except one
job.
This job is recalculating values in the database. The job starts a stored
procedure which opens a transaction. In this transaction there is a loop
over a large amount of entries. For each entry another stored procedure is
called to recalculate the values. This stored procedure is also covered in a
transaction. The inner stored procedures does some insertions and updates.
On the old system the recalculations are done on a constant rate. On the new
system the recalculation rate degrades on a factor of 10 % every minute. So
the complete job lasts almost four times as long on the new system, although
the beginning rate of the calculations is much higher on the new system. The
problem is that this job now takes about 8 hours to complete so we cannot
reconfigure our system during the night any more.
We did a lot of testing and changes about this issue like database
maintenance (recalculation of indices, reorganisation of indices etc.) and
changes of the configuration (enlarge the transaction log files of the
database and the temp.db).
The issue occure on different systems with different configurations and is
not hardware dependent. It seems to be a problem in the combination of SQL
Server 2000 and Windows 2003.
Are there any suggestions about this issue?
Best Regards
Harald Gehringhttp://support.microsoft.com/kb/899...2852&sid=global
Please check the stored procedure / TSQL to check if you are running into
the above issue.
we migrate an application from old to new hardware. In this step we migrate
also from SQL Server 2000 SP 2 on a Windows 2000 Server to SQL Server 2000
SP 4 on a Windows 2003 Server. Because of the new hardware almost every job
on the database ist much faster (up to 3 and 4 times) than before except one
job.
This job is recalculating values in the database. The job starts a stored
procedure which opens a transaction. In this transaction there is a loop
over a large amount of entries. For each entry another stored procedure is
called to recalculate the values. This stored procedure is also covered in a
transaction. The inner stored procedures does some insertions and updates.
On the old system the recalculations are done on a constant rate. On the new
system the recalculation rate degrades on a factor of 10 % every minute. So
the complete job lasts almost four times as long on the new system, although
the beginning rate of the calculations is much higher on the new system. The
problem is that this job now takes about 8 hours to complete so we cannot
reconfigure our system during the night any more.
We did a lot of testing and changes about this issue like database
maintenance (recalculation of indices, reorganisation of indices etc.) and
changes of the configuration (enlarge the transaction log files of the
database and the temp.db).
The issue occure on different systems with different configurations and is
not hardware dependent. It seems to be a problem in the combination of SQL
Server 2000 and Windows 2003.
Are there any suggestions about this issue?
Best Regards
Harald Gehringhttp://support.microsoft.com/kb/899...2852&sid=global
Please check the stored procedure / TSQL to check if you are running into
the above issue.
Performance Problem on SQL Server 2000 SP 4 on Win2003
Hello,
we migrate an application from old to new hardware. In this step we migrate
also from SQL Server 2000 SP 2 on a Windows 2000 Server to SQL Server 2000
SP 4 on a Windows 2003 Server. Because of the new hardware almost every job
on the database ist much faster (up to 3 and 4 times) than before except one
job.
This job is recalculating values in the database. The job starts a stored
procedure which opens a transaction. In this transaction there is a loop
over a large amount of entries. For each entry another stored procedure is
called to recalculate the values. This stored procedure is also covered in a
transaction. The inner stored procedures does some insertions and updates.
On the old system the recalculations are done on a constant rate. On the new
system the recalculation rate degrades on a factor of 10 % every minute. So
the complete job lasts almost four times as long on the new system, although
the beginning rate of the calculations is much higher on the new system. The
problem is that this job now takes about 8 hours to complete so we cannot
reconfigure our system during the night any more.
We did a lot of testing and changes about this issue like database
maintenance (recalculation of indices, reorganisation of indices etc.) and
changes of the configuration (enlarge the transaction log files of the
database and the temp.db).
The issue occure on different systems with different configurations and is
not hardware dependent. It seems to be a problem in the combination of SQL
Server 2000 and Windows 2003.
Are there any suggestions about this issue?
Best Regards
Harald Gehringhttp://support.microsoft.com/kb/899976?spid=2852&sid=global
Please check the stored procedure / TSQL to check if you are running into
the above issue.
we migrate an application from old to new hardware. In this step we migrate
also from SQL Server 2000 SP 2 on a Windows 2000 Server to SQL Server 2000
SP 4 on a Windows 2003 Server. Because of the new hardware almost every job
on the database ist much faster (up to 3 and 4 times) than before except one
job.
This job is recalculating values in the database. The job starts a stored
procedure which opens a transaction. In this transaction there is a loop
over a large amount of entries. For each entry another stored procedure is
called to recalculate the values. This stored procedure is also covered in a
transaction. The inner stored procedures does some insertions and updates.
On the old system the recalculations are done on a constant rate. On the new
system the recalculation rate degrades on a factor of 10 % every minute. So
the complete job lasts almost four times as long on the new system, although
the beginning rate of the calculations is much higher on the new system. The
problem is that this job now takes about 8 hours to complete so we cannot
reconfigure our system during the night any more.
We did a lot of testing and changes about this issue like database
maintenance (recalculation of indices, reorganisation of indices etc.) and
changes of the configuration (enlarge the transaction log files of the
database and the temp.db).
The issue occure on different systems with different configurations and is
not hardware dependent. It seems to be a problem in the combination of SQL
Server 2000 and Windows 2003.
Are there any suggestions about this issue?
Best Regards
Harald Gehringhttp://support.microsoft.com/kb/899976?spid=2852&sid=global
Please check the stored procedure / TSQL to check if you are running into
the above issue.
Wednesday, March 7, 2012
Performance of Hardware
Hey all,
I have 2 servers that I am interested in SQL Server 2000. One is a
single proc, Intel 2.53Ghz 533FSB with 1GB of dual-channel memory. This
server is running SBS2k3 running as a domain controller and Exchange. The
other is a dual proc AMD Athlon MP with 1500's in them. This server has
768MB and is running Windows 2003 Enterprise with RRAS acting as the gateway
to the internet, DNS, and DHCP. My question is which is the lesser evil? I
want to try out Microsoft CRM 1.2 and trying to balance out the workload.
Any help would be much appriciated.
MichaelMichael,
Some questions to point you down the right track:
What system has the better disc subsystem?
Check perfmon for current CPU loadings etc?
Mike John
"Michael Sainz" <mike@.twofatfrogs(remove).com> wrote in message =news:efCVtdA1DHA.1272@.TK2MSFTNGP12.phx.gbl...
> Hey all,
> I have 2 servers that I am interested in SQL Server 2000. One is a
> single proc, Intel 2.53Ghz 533FSB with 1GB of dual-channel memory. =This
> server is running SBS2k3 running as a domain controller and Exchange. =The
> other is a dual proc AMD Athlon MP with 1500's in them. This server =has
> 768MB and is running Windows 2003 Enterprise with RRAS acting as the =gateway
> to the internet, DNS, and DHCP. My question is which is the lesser =evil? I
> want to try out Microsoft CRM 1.2 and trying to balance out the =workload.
> Any help would be much appriciated.
> > Michael
> >|||John,
The Intel system (DC, Exchange2k3) has a single 80GB SATA Drive. The AMD
has dual 80GB in a mirrored array (hardware). In terms of performance and
load...there are only 3 clients ever connected so I dont think this is an
issue at all, but I just want to make sure and that is why i'm posting.
Thanks!
"Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
news:ug7sctJ1DHA.3468@.TK2MSFTNGP11.phx.gbl...
Michael,
Some questions to point you down the right track:
What system has the better disc subsystem?
Check perfmon for current CPU loadings etc?
Mike John
"Michael Sainz" <mike@.twofatfrogs(remove).com> wrote in message
news:efCVtdA1DHA.1272@.TK2MSFTNGP12.phx.gbl...
> Hey all,
> I have 2 servers that I am interested in SQL Server 2000. One is a
> single proc, Intel 2.53Ghz 533FSB with 1GB of dual-channel memory. This
> server is running SBS2k3 running as a domain controller and Exchange. The
> other is a dual proc AMD Athlon MP with 1500's in them. This server has
> 768MB and is running Windows 2003 Enterprise with RRAS acting as the
gateway
> to the internet, DNS, and DHCP. My question is which is the lesser evil? I
> want to try out Microsoft CRM 1.2 and trying to balance out the workload.
> Any help would be much appriciated.
> Michael
>
I have 2 servers that I am interested in SQL Server 2000. One is a
single proc, Intel 2.53Ghz 533FSB with 1GB of dual-channel memory. This
server is running SBS2k3 running as a domain controller and Exchange. The
other is a dual proc AMD Athlon MP with 1500's in them. This server has
768MB and is running Windows 2003 Enterprise with RRAS acting as the gateway
to the internet, DNS, and DHCP. My question is which is the lesser evil? I
want to try out Microsoft CRM 1.2 and trying to balance out the workload.
Any help would be much appriciated.
MichaelMichael,
Some questions to point you down the right track:
What system has the better disc subsystem?
Check perfmon for current CPU loadings etc?
Mike John
"Michael Sainz" <mike@.twofatfrogs(remove).com> wrote in message =news:efCVtdA1DHA.1272@.TK2MSFTNGP12.phx.gbl...
> Hey all,
> I have 2 servers that I am interested in SQL Server 2000. One is a
> single proc, Intel 2.53Ghz 533FSB with 1GB of dual-channel memory. =This
> server is running SBS2k3 running as a domain controller and Exchange. =The
> other is a dual proc AMD Athlon MP with 1500's in them. This server =has
> 768MB and is running Windows 2003 Enterprise with RRAS acting as the =gateway
> to the internet, DNS, and DHCP. My question is which is the lesser =evil? I
> want to try out Microsoft CRM 1.2 and trying to balance out the =workload.
> Any help would be much appriciated.
> > Michael
> >|||John,
The Intel system (DC, Exchange2k3) has a single 80GB SATA Drive. The AMD
has dual 80GB in a mirrored array (hardware). In terms of performance and
load...there are only 3 clients ever connected so I dont think this is an
issue at all, but I just want to make sure and that is why i'm posting.
Thanks!
"Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
news:ug7sctJ1DHA.3468@.TK2MSFTNGP11.phx.gbl...
Michael,
Some questions to point you down the right track:
What system has the better disc subsystem?
Check perfmon for current CPU loadings etc?
Mike John
"Michael Sainz" <mike@.twofatfrogs(remove).com> wrote in message
news:efCVtdA1DHA.1272@.TK2MSFTNGP12.phx.gbl...
> Hey all,
> I have 2 servers that I am interested in SQL Server 2000. One is a
> single proc, Intel 2.53Ghz 533FSB with 1GB of dual-channel memory. This
> server is running SBS2k3 running as a domain controller and Exchange. The
> other is a dual proc AMD Athlon MP with 1500's in them. This server has
> 768MB and is running Windows 2003 Enterprise with RRAS acting as the
gateway
> to the internet, DNS, and DHCP. My question is which is the lesser evil? I
> want to try out Microsoft CRM 1.2 and trying to balance out the workload.
> Any help would be much appriciated.
> Michael
>
Saturday, February 25, 2012
Performance Monitoring
I ran performance monitoring on one of my server that is
performing slow in order to determine if there is a memory
or hardware bottleneck and came up with the following
statistics. Please help diagnose this and let me know if
we need to increase memory or configure something in sql
server. This is an average of statistics collected
during peak periods 19 times. Thanks for your help. I
already suggested that ram needs to be added to the
server, but I need more assured technical justification
as I am new to this. Thx.
PROCESSOR TIME
max 36.42%
Average 9.53%
System
Processor Queue Length (max) 1
MEMORY
Pages/sec 121
Available Mbytes 156
Page Faults/sec 735
total physical memory 1986302
total sql server memory - mb 1589485
Processes page fault - Sql Server 73
Maximum workspace memory 1081725
DISK
Disk Reads per second 244
Disk Writes per second 99
Average Disk Queue length 4
SQL SERVER GENERAL STATISTICS
# connections 15
SQL Server Buffer Manager cache hit ratio 94
Hardware Info
Hard drive :
C' drive total hd space = 33.9G space
available = 20.2 G
D' drive total hd space = 273G space
available = 272G
E' drive total hd space = 273G space
available = 172 G
F' drive total hd space = 273G space
available = 127 G
F' drive total hd space = 819G space
available = 431 G
CPU = 2.80 GHz
RAM = 2.09G
Operating System = Windows 2000 SP4
Sql Server 2000 sp3aUnfortunatly its a bit hard to determine whether your
server is running slow or not, because there is no point
of reference. Probably the best way would be to run it
once on a weekly basis and get the average.
You may want to monitor network trafic, read writes and
memory (can anyone think of more ?), personally though
7/10 its the network thats slow and not SQL.
Be that as it may there are some options that may speed
you SQL Server up, if you feel its underperforming.
Firstly have a look at the DBCC SHOWCONTIG command. This
will show how dragmented your indexes are, if they are
defragmented then run DBCC DBREINDEX.
Secondly set up a profile trace for performance
enhancement. Allow it to run for at least a couple of days
then run the Index Tuning wizard. This will put in some
indexes for you based upon the queries your users have
been making.
Finally mark you store procedures for re-compulation in
case the execution plan is out of date.
If you would like any further help my email in reverse
order is moc.liamtoh@.em_yrewolf_elttil
J
>--Original Message--
>I ran performance monitoring on one of my server that is
>performing slow in order to determine if there is a
memory
>or hardware bottleneck and came up with the following
>statistics. Please help diagnose this and let me know if
>we need to increase memory or configure something in sql
>server. This is an average of statistics collected
>during peak periods 19 times. Thanks for your help. I
>already suggested that ram needs to be added to the
>server, but I need more assured technical justification
>as I am new to this. Thx.
>PROCESSOR TIME
>max 36.42%
>Average 9.53%
>System
>Processor Queue Length (max) 1
>MEMORY
>Pages/sec 121
>Available Mbytes 156
>Page Faults/sec 735
>total physical memory 1986302
>total sql server memory - mb 1589485
>Processes page fault - Sql Server 73
>Maximum workspace memory 1081725
>DISK
>Disk Reads per second 244
>Disk Writes per second 99
>Average Disk Queue length 4
>SQL SERVER GENERAL STATISTICS
># connections 15
>SQL Server Buffer Manager cache hit ratio 94
>Hardware Info
>Hard drive :
>C' drive total hd space = 33.9G space
>available = 20.2 G
>D' drive total hd space = 273G space
>available = 272G
>E' drive total hd space = 273G space
>available = 172 G
>F' drive total hd space = 273G space
>available = 127 G
>F' drive total hd space = 819G space
>available = 431 G
>CPU = 2.80 GHz
>RAM = 2.09G
>Operating System = Windows 2000 SP4
>Sql Server 2000 sp3a
>.
>|||Julie,
Thank you so much for response you gave to my performance
problems.
The statistics I sent in the Microsoft newsgroup is an
average of every thing I log for 9 days now. Would you
rather I send you what I collected today? The network may
notbe the issue because I have an identical server with
more memory that returns stuff quicker. I will try the
trace profiler also. I reindex the indexes weekly - but I
will still run dbcc showcontig. Finally, how do you
recompile stored procedures? Thank you.
Jasmine
>--Original Message--
>Unfortunatly its a bit hard to determine whether your
>server is running slow or not, because there is no point
>of reference. Probably the best way would be to run it
>once on a weekly basis and get the average.
>You may want to monitor network trafic, read writes and
>memory (can anyone think of more ?), personally though
>7/10 its the network thats slow and not SQL.
>Be that as it may there are some options that may speed
>you SQL Server up, if you feel its underperforming.
>Firstly have a look at the DBCC SHOWCONTIG command. This
>will show how dragmented your indexes are, if they are
>defragmented then run DBCC DBREINDEX.
>Secondly set up a profile trace for performance
>enhancement. Allow it to run for at least a couple of
days
>then run the Index Tuning wizard. This will put in some
>indexes for you based upon the queries your users have
>been making.
>Finally mark you store procedures for re-compulation in
>case the execution plan is out of date.
>If you would like any further help my email in reverse
>order is moc.liamtoh@.em_yrewolf_elttil
>J
>>--Original Message--
>>I ran performance monitoring on one of my server that is
>>performing slow in order to determine if there is a
>memory
>>or hardware bottleneck and came up with the following
>>statistics. Please help diagnose this and let me know
if
>>we need to increase memory or configure something in sql
>>server. This is an average of statistics collected
>>during peak periods 19 times. Thanks for your help. I
>>already suggested that ram needs to be added to the
>>server, but I need more assured technical justification
>>as I am new to this. Thx.
>>PROCESSOR TIME
>>max 36.42%
>>Average 9.53%
>>System
>>Processor Queue Length (max) 1
>>MEMORY
>>Pages/sec 121
>>Available Mbytes 156
>>Page Faults/sec 735
>>total physical memory 1986302
>>total sql server memory - mb 1589485
>>Processes page fault - Sql Server 73
>>Maximum workspace memory 1081725
>>DISK
>>Disk Reads per second 244
>>Disk Writes per second 99
>>Average Disk Queue length 4
>>SQL SERVER GENERAL STATISTICS
>># connections 15
>>SQL Server Buffer Manager cache hit ratio 94
>>Hardware Info
>>Hard drive :
>>C' drive total hd space = 33.9G space
>>available = 20.2 G
>>D' drive total hd space = 273G space
>>available = 272G
>>E' drive total hd space = 273G space
>>available = 172 G
>>F' drive total hd space = 273G space
>>available = 127 G
>>F' drive total hd space = 819G space
>>available = 431 G
>>CPU = 2.80 GHz
>>RAM = 2.09G
>>Operating System = Windows 2000 SP4
>>Sql Server 2000 sp3a
>>.
>.
>|||also check out the kb below for a more general overview of how to
troubleshoot performance issues
http://support.microsoft.com/default.aspx?scid=kb;en-us;298475
Andy.
"Jasmine Gwendle" <anonymous@.discussions.microsoft.com> wrote in message
news:173c901c448ab$17949f00$a001280a@.phx.gbl...
> I ran performance monitoring on one of my server that is
> performing slow in order to determine if there is a memory
> or hardware bottleneck and came up with the following
> statistics. Please help diagnose this and let me know if
> we need to increase memory or configure something in sql
> server. This is an average of statistics collected
> during peak periods 19 times. Thanks for your help. I
> already suggested that ram needs to be added to the
> server, but I need more assured technical justification
> as I am new to this. Thx.
> PROCESSOR TIME
> max 36.42%
> Average 9.53%
> System
> Processor Queue Length (max) 1
> MEMORY
> Pages/sec 121
> Available Mbytes 156
> Page Faults/sec 735
> total physical memory 1986302
> total sql server memory - mb 1589485
> Processes page fault - Sql Server 73
> Maximum workspace memory 1081725
> DISK
> Disk Reads per second 244
> Disk Writes per second 99
> Average Disk Queue length 4
> SQL SERVER GENERAL STATISTICS
> # connections 15
> SQL Server Buffer Manager cache hit ratio 94
> Hardware Info
> Hard drive :
> C' drive total hd space = 33.9G space
> available = 20.2 G
> D' drive total hd space = 273G space
> available = 272G
> E' drive total hd space = 273G space
> available = 172 G
> F' drive total hd space = 273G space
> available = 127 G
> F' drive total hd space = 819G space
> available = 431 G
> CPU = 2.80 GHz
> RAM = 2.09G
> Operating System = Windows 2000 SP4
> Sql Server 2000 sp3a
>|||Hi Jasmine
Firstly the store procedures
EXEC sp_recompile <store procedure name>
Personally I create a cursor with
select name from sysobjects where xtype = 'P' and status
> 0
That gets out all the sp for your database, then I add
the EXEC sp_recompile
As for the performance could you give me somthing like
Mondays 9am to 5pm Average, Tuesdays average 9am to 5pm
ect.
You can also configure the performance monitor to sent
you pop-ups when for instance network is at 100%.
Other things.
1 Is there anything else running on your server?
2 Is your memory set up the same
3 Do you have autoclose turned off your dbs
4 Is the problem saving, loading or both
J
>--Original Message--
>Julie,
>Thank you so much for response you gave to my
performance
>problems.
>The statistics I sent in the Microsoft newsgroup is an
>average of every thing I log for 9 days now. Would you
>rather I send you what I collected today? The network
may
>notbe the issue because I have an identical server with
>more memory that returns stuff quicker. I will try the
>trace profiler also. I reindex the indexes weekly - but
I
>will still run dbcc showcontig. Finally, how do you
>recompile stored procedures? Thank you.
>Jasmine
>
>>--Original Message--
>>Unfortunatly its a bit hard to determine whether your
>>server is running slow or not, because there is no
point
>>of reference. Probably the best way would be to run it
>>once on a weekly basis and get the average.
>>You may want to monitor network trafic, read writes and
>>memory (can anyone think of more ?), personally though
>>7/10 its the network thats slow and not SQL.
>>Be that as it may there are some options that may speed
>>you SQL Server up, if you feel its underperforming.
>>Firstly have a look at the DBCC SHOWCONTIG command.
This
>>will show how dragmented your indexes are, if they are
>>defragmented then run DBCC DBREINDEX.
>>Secondly set up a profile trace for performance
>>enhancement. Allow it to run for at least a couple of
>days
>>then run the Index Tuning wizard. This will put in some
>>indexes for you based upon the queries your users have
>>been making.
>>Finally mark you store procedures for re-compulation in
>>case the execution plan is out of date.
>>If you would like any further help my email in reverse
>>order is moc.liamtoh@.em_yrewolf_elttil
>>J
>>--Original Message--
>>I ran performance monitoring on one of my server that
is
>>performing slow in order to determine if there is a
>>memory
>>or hardware bottleneck and came up with the following
>>statistics. Please help diagnose this and let me know
>if
>>we need to increase memory or configure something in
sql
>>server. This is an average of statistics collected
>>during peak periods 19 times. Thanks for your help.
I
>>already suggested that ram needs to be added to the
>>server, but I need more assured technical justification
>>as I am new to this. Thx.
>>PROCESSOR TIME
>>max 36.42%
>>Average 9.53%
>>System
>>Processor Queue Length (max) 1
>>MEMORY
>>Pages/sec 121
>>Available Mbytes 156
>>Page Faults/sec 735
>>total physical memory 1986302
>>total sql server memory - mb 1589485
>>Processes page fault - Sql Server 73
>>Maximum workspace memory 1081725
>>DISK
>>Disk Reads per second 244
>>Disk Writes per second 99
>>Average Disk Queue length 4
>>SQL SERVER GENERAL STATISTICS
>># connections 15
>>SQL Server Buffer Manager cache hit ratio 94
>>Hardware Info
>>Hard drive :
>>C' drive total hd space = 33.9G space
>>available = 20.2 G
>>D' drive total hd space = 273G space
>>available = 272G
>>E' drive total hd space = 273G space
>>available = 172 G
>>F' drive total hd space = 273G space
>>available = 127 G
>>F' drive total hd space = 819G space
>>available = 431 G
>>CPU = 2.80 GHz
>>RAM = 2.09G
>>Operating System = Windows 2000 SP4
>>Sql Server 2000 sp3a
>>.
>>.
>.
>
performing slow in order to determine if there is a memory
or hardware bottleneck and came up with the following
statistics. Please help diagnose this and let me know if
we need to increase memory or configure something in sql
server. This is an average of statistics collected
during peak periods 19 times. Thanks for your help. I
already suggested that ram needs to be added to the
server, but I need more assured technical justification
as I am new to this. Thx.
PROCESSOR TIME
max 36.42%
Average 9.53%
System
Processor Queue Length (max) 1
MEMORY
Pages/sec 121
Available Mbytes 156
Page Faults/sec 735
total physical memory 1986302
total sql server memory - mb 1589485
Processes page fault - Sql Server 73
Maximum workspace memory 1081725
DISK
Disk Reads per second 244
Disk Writes per second 99
Average Disk Queue length 4
SQL SERVER GENERAL STATISTICS
# connections 15
SQL Server Buffer Manager cache hit ratio 94
Hardware Info
Hard drive :
C' drive total hd space = 33.9G space
available = 20.2 G
D' drive total hd space = 273G space
available = 272G
E' drive total hd space = 273G space
available = 172 G
F' drive total hd space = 273G space
available = 127 G
F' drive total hd space = 819G space
available = 431 G
CPU = 2.80 GHz
RAM = 2.09G
Operating System = Windows 2000 SP4
Sql Server 2000 sp3aUnfortunatly its a bit hard to determine whether your
server is running slow or not, because there is no point
of reference. Probably the best way would be to run it
once on a weekly basis and get the average.
You may want to monitor network trafic, read writes and
memory (can anyone think of more ?), personally though
7/10 its the network thats slow and not SQL.
Be that as it may there are some options that may speed
you SQL Server up, if you feel its underperforming.
Firstly have a look at the DBCC SHOWCONTIG command. This
will show how dragmented your indexes are, if they are
defragmented then run DBCC DBREINDEX.
Secondly set up a profile trace for performance
enhancement. Allow it to run for at least a couple of days
then run the Index Tuning wizard. This will put in some
indexes for you based upon the queries your users have
been making.
Finally mark you store procedures for re-compulation in
case the execution plan is out of date.
If you would like any further help my email in reverse
order is moc.liamtoh@.em_yrewolf_elttil
J
>--Original Message--
>I ran performance monitoring on one of my server that is
>performing slow in order to determine if there is a
memory
>or hardware bottleneck and came up with the following
>statistics. Please help diagnose this and let me know if
>we need to increase memory or configure something in sql
>server. This is an average of statistics collected
>during peak periods 19 times. Thanks for your help. I
>already suggested that ram needs to be added to the
>server, but I need more assured technical justification
>as I am new to this. Thx.
>PROCESSOR TIME
>max 36.42%
>Average 9.53%
>System
>Processor Queue Length (max) 1
>MEMORY
>Pages/sec 121
>Available Mbytes 156
>Page Faults/sec 735
>total physical memory 1986302
>total sql server memory - mb 1589485
>Processes page fault - Sql Server 73
>Maximum workspace memory 1081725
>DISK
>Disk Reads per second 244
>Disk Writes per second 99
>Average Disk Queue length 4
>SQL SERVER GENERAL STATISTICS
># connections 15
>SQL Server Buffer Manager cache hit ratio 94
>Hardware Info
>Hard drive :
>C' drive total hd space = 33.9G space
>available = 20.2 G
>D' drive total hd space = 273G space
>available = 272G
>E' drive total hd space = 273G space
>available = 172 G
>F' drive total hd space = 273G space
>available = 127 G
>F' drive total hd space = 819G space
>available = 431 G
>CPU = 2.80 GHz
>RAM = 2.09G
>Operating System = Windows 2000 SP4
>Sql Server 2000 sp3a
>.
>|||Julie,
Thank you so much for response you gave to my performance
problems.
The statistics I sent in the Microsoft newsgroup is an
average of every thing I log for 9 days now. Would you
rather I send you what I collected today? The network may
notbe the issue because I have an identical server with
more memory that returns stuff quicker. I will try the
trace profiler also. I reindex the indexes weekly - but I
will still run dbcc showcontig. Finally, how do you
recompile stored procedures? Thank you.
Jasmine
>--Original Message--
>Unfortunatly its a bit hard to determine whether your
>server is running slow or not, because there is no point
>of reference. Probably the best way would be to run it
>once on a weekly basis and get the average.
>You may want to monitor network trafic, read writes and
>memory (can anyone think of more ?), personally though
>7/10 its the network thats slow and not SQL.
>Be that as it may there are some options that may speed
>you SQL Server up, if you feel its underperforming.
>Firstly have a look at the DBCC SHOWCONTIG command. This
>will show how dragmented your indexes are, if they are
>defragmented then run DBCC DBREINDEX.
>Secondly set up a profile trace for performance
>enhancement. Allow it to run for at least a couple of
days
>then run the Index Tuning wizard. This will put in some
>indexes for you based upon the queries your users have
>been making.
>Finally mark you store procedures for re-compulation in
>case the execution plan is out of date.
>If you would like any further help my email in reverse
>order is moc.liamtoh@.em_yrewolf_elttil
>J
>>--Original Message--
>>I ran performance monitoring on one of my server that is
>>performing slow in order to determine if there is a
>memory
>>or hardware bottleneck and came up with the following
>>statistics. Please help diagnose this and let me know
if
>>we need to increase memory or configure something in sql
>>server. This is an average of statistics collected
>>during peak periods 19 times. Thanks for your help. I
>>already suggested that ram needs to be added to the
>>server, but I need more assured technical justification
>>as I am new to this. Thx.
>>PROCESSOR TIME
>>max 36.42%
>>Average 9.53%
>>System
>>Processor Queue Length (max) 1
>>MEMORY
>>Pages/sec 121
>>Available Mbytes 156
>>Page Faults/sec 735
>>total physical memory 1986302
>>total sql server memory - mb 1589485
>>Processes page fault - Sql Server 73
>>Maximum workspace memory 1081725
>>DISK
>>Disk Reads per second 244
>>Disk Writes per second 99
>>Average Disk Queue length 4
>>SQL SERVER GENERAL STATISTICS
>># connections 15
>>SQL Server Buffer Manager cache hit ratio 94
>>Hardware Info
>>Hard drive :
>>C' drive total hd space = 33.9G space
>>available = 20.2 G
>>D' drive total hd space = 273G space
>>available = 272G
>>E' drive total hd space = 273G space
>>available = 172 G
>>F' drive total hd space = 273G space
>>available = 127 G
>>F' drive total hd space = 819G space
>>available = 431 G
>>CPU = 2.80 GHz
>>RAM = 2.09G
>>Operating System = Windows 2000 SP4
>>Sql Server 2000 sp3a
>>.
>.
>|||also check out the kb below for a more general overview of how to
troubleshoot performance issues
http://support.microsoft.com/default.aspx?scid=kb;en-us;298475
Andy.
"Jasmine Gwendle" <anonymous@.discussions.microsoft.com> wrote in message
news:173c901c448ab$17949f00$a001280a@.phx.gbl...
> I ran performance monitoring on one of my server that is
> performing slow in order to determine if there is a memory
> or hardware bottleneck and came up with the following
> statistics. Please help diagnose this and let me know if
> we need to increase memory or configure something in sql
> server. This is an average of statistics collected
> during peak periods 19 times. Thanks for your help. I
> already suggested that ram needs to be added to the
> server, but I need more assured technical justification
> as I am new to this. Thx.
> PROCESSOR TIME
> max 36.42%
> Average 9.53%
> System
> Processor Queue Length (max) 1
> MEMORY
> Pages/sec 121
> Available Mbytes 156
> Page Faults/sec 735
> total physical memory 1986302
> total sql server memory - mb 1589485
> Processes page fault - Sql Server 73
> Maximum workspace memory 1081725
> DISK
> Disk Reads per second 244
> Disk Writes per second 99
> Average Disk Queue length 4
> SQL SERVER GENERAL STATISTICS
> # connections 15
> SQL Server Buffer Manager cache hit ratio 94
> Hardware Info
> Hard drive :
> C' drive total hd space = 33.9G space
> available = 20.2 G
> D' drive total hd space = 273G space
> available = 272G
> E' drive total hd space = 273G space
> available = 172 G
> F' drive total hd space = 273G space
> available = 127 G
> F' drive total hd space = 819G space
> available = 431 G
> CPU = 2.80 GHz
> RAM = 2.09G
> Operating System = Windows 2000 SP4
> Sql Server 2000 sp3a
>|||Hi Jasmine
Firstly the store procedures
EXEC sp_recompile <store procedure name>
Personally I create a cursor with
select name from sysobjects where xtype = 'P' and status
> 0
That gets out all the sp for your database, then I add
the EXEC sp_recompile
As for the performance could you give me somthing like
Mondays 9am to 5pm Average, Tuesdays average 9am to 5pm
ect.
You can also configure the performance monitor to sent
you pop-ups when for instance network is at 100%.
Other things.
1 Is there anything else running on your server?
2 Is your memory set up the same
3 Do you have autoclose turned off your dbs
4 Is the problem saving, loading or both
J
>--Original Message--
>Julie,
>Thank you so much for response you gave to my
performance
>problems.
>The statistics I sent in the Microsoft newsgroup is an
>average of every thing I log for 9 days now. Would you
>rather I send you what I collected today? The network
may
>notbe the issue because I have an identical server with
>more memory that returns stuff quicker. I will try the
>trace profiler also. I reindex the indexes weekly - but
I
>will still run dbcc showcontig. Finally, how do you
>recompile stored procedures? Thank you.
>Jasmine
>
>>--Original Message--
>>Unfortunatly its a bit hard to determine whether your
>>server is running slow or not, because there is no
point
>>of reference. Probably the best way would be to run it
>>once on a weekly basis and get the average.
>>You may want to monitor network trafic, read writes and
>>memory (can anyone think of more ?), personally though
>>7/10 its the network thats slow and not SQL.
>>Be that as it may there are some options that may speed
>>you SQL Server up, if you feel its underperforming.
>>Firstly have a look at the DBCC SHOWCONTIG command.
This
>>will show how dragmented your indexes are, if they are
>>defragmented then run DBCC DBREINDEX.
>>Secondly set up a profile trace for performance
>>enhancement. Allow it to run for at least a couple of
>days
>>then run the Index Tuning wizard. This will put in some
>>indexes for you based upon the queries your users have
>>been making.
>>Finally mark you store procedures for re-compulation in
>>case the execution plan is out of date.
>>If you would like any further help my email in reverse
>>order is moc.liamtoh@.em_yrewolf_elttil
>>J
>>--Original Message--
>>I ran performance monitoring on one of my server that
is
>>performing slow in order to determine if there is a
>>memory
>>or hardware bottleneck and came up with the following
>>statistics. Please help diagnose this and let me know
>if
>>we need to increase memory or configure something in
sql
>>server. This is an average of statistics collected
>>during peak periods 19 times. Thanks for your help.
I
>>already suggested that ram needs to be added to the
>>server, but I need more assured technical justification
>>as I am new to this. Thx.
>>PROCESSOR TIME
>>max 36.42%
>>Average 9.53%
>>System
>>Processor Queue Length (max) 1
>>MEMORY
>>Pages/sec 121
>>Available Mbytes 156
>>Page Faults/sec 735
>>total physical memory 1986302
>>total sql server memory - mb 1589485
>>Processes page fault - Sql Server 73
>>Maximum workspace memory 1081725
>>DISK
>>Disk Reads per second 244
>>Disk Writes per second 99
>>Average Disk Queue length 4
>>SQL SERVER GENERAL STATISTICS
>># connections 15
>>SQL Server Buffer Manager cache hit ratio 94
>>Hardware Info
>>Hard drive :
>>C' drive total hd space = 33.9G space
>>available = 20.2 G
>>D' drive total hd space = 273G space
>>available = 272G
>>E' drive total hd space = 273G space
>>available = 172 G
>>F' drive total hd space = 273G space
>>available = 127 G
>>F' drive total hd space = 819G space
>>available = 431 G
>>CPU = 2.80 GHz
>>RAM = 2.09G
>>Operating System = Windows 2000 SP4
>>Sql Server 2000 sp3a
>>.
>>.
>.
>
Monday, February 20, 2012
Performance Monitoring
I ran performance monitoring on one of my server that is
performing slow in order to determine if there is a memory
or hardware bottleneck and came up with the following
statistics. Please help diagnose this and let me know if
we need to increase memory or configure something in sql
server. This is an average of statistics collected
during peak periods 19 times. Thanks for your help. I
already suggested that ram needs to be added to the
server, but I need more assured technical justification
as I am new to this. Thx.
PROCESSOR TIME
max36.42%
Average9.53%
System
Processor Queue Length (max)1
MEMORY
Pages/sec121
Available Mbytes 156
Page Faults/sec735
total physical memory1986302
total sql server memory - mb1589485
Processes page fault - Sql Server 73
Maximum workspace memory 1081725
DISK
Disk Reads per second244
Disk Writes per second99
Average Disk Queue length 4
SQL SERVER GENERAL STATISTICS
# connections 15
SQL Server Buffer Manager cache hit ratio 94
Hardware Info
Hard drive :
C' drive total hd space = 33.9G space
available = 20.2 G
D' drive total hd space = 273G space
available = 272G
E' drive total hd space = 273G space
available = 172 G
F' drive total hd space = 273G space
available = 127 G
F' drive total hd space = 819G space
available = 431 G
CPU = 2.80 GHz
RAM = 2.09G
Operating System = Windows 2000 SP4
Sql Server 2000 sp3a
also check out the kb below for a more general overview of how to
troubleshoot performance issues
http://support.microsoft.com/default...b;en-us;298475
Andy.
"Jasmine Gwendle" <anonymous@.discussions.microsoft.com> wrote in message
news:173c901c448ab$17949f00$a001280a@.phx.gbl...
> I ran performance monitoring on one of my server that is
> performing slow in order to determine if there is a memory
> or hardware bottleneck and came up with the following
> statistics. Please help diagnose this and let me know if
> we need to increase memory or configure something in sql
> server. This is an average of statistics collected
> during peak periods 19 times. Thanks for your help. I
> already suggested that ram needs to be added to the
> server, but I need more assured technical justification
> as I am new to this. Thx.
> PROCESSOR TIME
> max 36.42%
> Average 9.53%
> System
> Processor Queue Length (max) 1
> MEMORY
> Pages/sec 121
> Available Mbytes 156
> Page Faults/sec 735
> total physical memory 1986302
> total sql server memory - mb 1589485
> Processes page fault - Sql Server 73
> Maximum workspace memory 1081725
> DISK
> Disk Reads per second 244
> Disk Writes per second 99
> Average Disk Queue length 4
> SQL SERVER GENERAL STATISTICS
> # connections 15
> SQL Server Buffer Manager cache hit ratio 94
> Hardware Info
> Hard drive :
> C' drive total hd space = 33.9G space
> available = 20.2 G
> D' drive total hd space = 273G space
> available = 272G
> E' drive total hd space = 273G space
> available = 172 G
> F' drive total hd space = 273G space
> available = 127 G
> F' drive total hd space = 819G space
> available = 431 G
> CPU = 2.80 GHz
> RAM = 2.09G
> Operating System = Windows 2000 SP4
> Sql Server 2000 sp3a
>
performing slow in order to determine if there is a memory
or hardware bottleneck and came up with the following
statistics. Please help diagnose this and let me know if
we need to increase memory or configure something in sql
server. This is an average of statistics collected
during peak periods 19 times. Thanks for your help. I
already suggested that ram needs to be added to the
server, but I need more assured technical justification
as I am new to this. Thx.
PROCESSOR TIME
max36.42%
Average9.53%
System
Processor Queue Length (max)1
MEMORY
Pages/sec121
Available Mbytes 156
Page Faults/sec735
total physical memory1986302
total sql server memory - mb1589485
Processes page fault - Sql Server 73
Maximum workspace memory 1081725
DISK
Disk Reads per second244
Disk Writes per second99
Average Disk Queue length 4
SQL SERVER GENERAL STATISTICS
# connections 15
SQL Server Buffer Manager cache hit ratio 94
Hardware Info
Hard drive :
C' drive total hd space = 33.9G space
available = 20.2 G
D' drive total hd space = 273G space
available = 272G
E' drive total hd space = 273G space
available = 172 G
F' drive total hd space = 273G space
available = 127 G
F' drive total hd space = 819G space
available = 431 G
CPU = 2.80 GHz
RAM = 2.09G
Operating System = Windows 2000 SP4
Sql Server 2000 sp3a
also check out the kb below for a more general overview of how to
troubleshoot performance issues
http://support.microsoft.com/default...b;en-us;298475
Andy.
"Jasmine Gwendle" <anonymous@.discussions.microsoft.com> wrote in message
news:173c901c448ab$17949f00$a001280a@.phx.gbl...
> I ran performance monitoring on one of my server that is
> performing slow in order to determine if there is a memory
> or hardware bottleneck and came up with the following
> statistics. Please help diagnose this and let me know if
> we need to increase memory or configure something in sql
> server. This is an average of statistics collected
> during peak periods 19 times. Thanks for your help. I
> already suggested that ram needs to be added to the
> server, but I need more assured technical justification
> as I am new to this. Thx.
> PROCESSOR TIME
> max 36.42%
> Average 9.53%
> System
> Processor Queue Length (max) 1
> MEMORY
> Pages/sec 121
> Available Mbytes 156
> Page Faults/sec 735
> total physical memory 1986302
> total sql server memory - mb 1589485
> Processes page fault - Sql Server 73
> Maximum workspace memory 1081725
> DISK
> Disk Reads per second 244
> Disk Writes per second 99
> Average Disk Queue length 4
> SQL SERVER GENERAL STATISTICS
> # connections 15
> SQL Server Buffer Manager cache hit ratio 94
> Hardware Info
> Hard drive :
> C' drive total hd space = 33.9G space
> available = 20.2 G
> D' drive total hd space = 273G space
> available = 272G
> E' drive total hd space = 273G space
> available = 172 G
> F' drive total hd space = 273G space
> available = 127 G
> F' drive total hd space = 819G space
> available = 431 G
> CPU = 2.80 GHz
> RAM = 2.09G
> Operating System = Windows 2000 SP4
> Sql Server 2000 sp3a
>
Performance Monitoring
I ran performance monitoring on one of my server that is
performing slow in order to determine if there is a memory
or hardware bottleneck and came up with the following
statistics. Please help diagnose this and let me know if
we need to increase memory or configure something in sql
server. This is an average of statistics collected
during peak periods 19 times. Thanks for your help. I
already suggested that ram needs to be added to the
server, but I need more assured technical justification
as I am new to this. Thx.
PROCESSOR TIME
max 36.42%
Average 9.53%
System
Processor Queue Length (max) 1
MEMORY
Pages/sec 121
Available Mbytes 156
Page Faults/sec 735
total physical memory 1986302
total sql server memory - mb 1589485
Processes page fault - Sql Server 73
Maximum workspace memory 1081725
DISK
Disk Reads per second 244
Disk Writes per second 99
Average Disk Queue length 4
SQL SERVER GENERAL STATISTICS
# connections 15
SQL Server Buffer Manager cache hit ratio 94
Hardware Info
Hard drive :
C' drive total hd space = 33.9G space
available = 20.2 G
D' drive total hd space = 273G space
available = 272G
E' drive total hd space = 273G space
available = 172 G
F' drive total hd space = 273G space
available = 127 G
F' drive total hd space = 819G space
available = 431 G
CPU = 2.80 GHz
RAM = 2.09G
Operating System = Windows 2000 SP4
Sql Server 2000 sp3aalso check out the kb below for a more general overview of how to
troubleshoot performance issues
http://support.microsoft.com/defaul...kb;en-us;298475
Andy.
"Jasmine Gwendle" <anonymous@.discussions.microsoft.com> wrote in message
news:173c901c448ab$17949f00$a001280a@.phx
.gbl...
> I ran performance monitoring on one of my server that is
> performing slow in order to determine if there is a memory
> or hardware bottleneck and came up with the following
> statistics. Please help diagnose this and let me know if
> we need to increase memory or configure something in sql
> server. This is an average of statistics collected
> during peak periods 19 times. Thanks for your help. I
> already suggested that ram needs to be added to the
> server, but I need more assured technical justification
> as I am new to this. Thx.
> PROCESSOR TIME
> max 36.42%
> Average 9.53%
> System
> Processor Queue Length (max) 1
> MEMORY
> Pages/sec 121
> Available Mbytes 156
> Page Faults/sec 735
> total physical memory 1986302
> total sql server memory - mb 1589485
> Processes page fault - Sql Server 73
> Maximum workspace memory 1081725
> DISK
> Disk Reads per second 244
> Disk Writes per second 99
> Average Disk Queue length 4
> SQL SERVER GENERAL STATISTICS
> # connections 15
> SQL Server Buffer Manager cache hit ratio 94
> hardware Info
> Hard drive :
> C' drive total hd space = 33.9G space
> available = 20.2 G
> D' drive total hd space = 273G space
> available = 272G
> E' drive total hd space = 273G space
> available = 172 G
> F' drive total hd space = 273G space
> available = 127 G
> F' drive total hd space = 819G space
> available = 431 G
> CPU = 2.80 GHz
> RAM = 2.09G
> Operating System = Windows 2000 SP4
> Sql Server 2000 sp3a
>
performing slow in order to determine if there is a memory
or hardware bottleneck and came up with the following
statistics. Please help diagnose this and let me know if
we need to increase memory or configure something in sql
server. This is an average of statistics collected
during peak periods 19 times. Thanks for your help. I
already suggested that ram needs to be added to the
server, but I need more assured technical justification
as I am new to this. Thx.
PROCESSOR TIME
max 36.42%
Average 9.53%
System
Processor Queue Length (max) 1
MEMORY
Pages/sec 121
Available Mbytes 156
Page Faults/sec 735
total physical memory 1986302
total sql server memory - mb 1589485
Processes page fault - Sql Server 73
Maximum workspace memory 1081725
DISK
Disk Reads per second 244
Disk Writes per second 99
Average Disk Queue length 4
SQL SERVER GENERAL STATISTICS
# connections 15
SQL Server Buffer Manager cache hit ratio 94
Hardware Info
Hard drive :
C' drive total hd space = 33.9G space
available = 20.2 G
D' drive total hd space = 273G space
available = 272G
E' drive total hd space = 273G space
available = 172 G
F' drive total hd space = 273G space
available = 127 G
F' drive total hd space = 819G space
available = 431 G
CPU = 2.80 GHz
RAM = 2.09G
Operating System = Windows 2000 SP4
Sql Server 2000 sp3aalso check out the kb below for a more general overview of how to
troubleshoot performance issues
http://support.microsoft.com/defaul...kb;en-us;298475
Andy.
"Jasmine Gwendle" <anonymous@.discussions.microsoft.com> wrote in message
news:173c901c448ab$17949f00$a001280a@.phx
.gbl...
> I ran performance monitoring on one of my server that is
> performing slow in order to determine if there is a memory
> or hardware bottleneck and came up with the following
> statistics. Please help diagnose this and let me know if
> we need to increase memory or configure something in sql
> server. This is an average of statistics collected
> during peak periods 19 times. Thanks for your help. I
> already suggested that ram needs to be added to the
> server, but I need more assured technical justification
> as I am new to this. Thx.
> PROCESSOR TIME
> max 36.42%
> Average 9.53%
> System
> Processor Queue Length (max) 1
> MEMORY
> Pages/sec 121
> Available Mbytes 156
> Page Faults/sec 735
> total physical memory 1986302
> total sql server memory - mb 1589485
> Processes page fault - Sql Server 73
> Maximum workspace memory 1081725
> DISK
> Disk Reads per second 244
> Disk Writes per second 99
> Average Disk Queue length 4
> SQL SERVER GENERAL STATISTICS
> # connections 15
> SQL Server Buffer Manager cache hit ratio 94
> hardware Info
> Hard drive :
> C' drive total hd space = 33.9G space
> available = 20.2 G
> D' drive total hd space = 273G space
> available = 272G
> E' drive total hd space = 273G space
> available = 172 G
> F' drive total hd space = 273G space
> available = 127 G
> F' drive total hd space = 819G space
> available = 431 G
> CPU = 2.80 GHz
> RAM = 2.09G
> Operating System = Windows 2000 SP4
> Sql Server 2000 sp3a
>
Subscribe to:
Posts (Atom)