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