We've got a customer whose system is performing below average. Example, a bi
lling calculation that takes 13 minutes on my laptop (XP PRO, 2.2 GHz, 1GB R
AM, 4200RPM HD, SQL 2000 SP3) takes 28 minutes on their server (WIN2000, 2-1
.5GHz, 2GB RAM, RAID5 array
, SQL 2000 SP3). We turned on the performance monitors recently and watched
a bill run. The hard drives and RAM were barely used while the 4 processor i
nstances (2 physical = 4 logical with hyperthread) were all consistently abo
ve 90%.
We deduced that the processors were the obvious bottleneck but we are unsure
why a less powerful machine (my laptop) out-performs the more powerful serv
er. The only thing I can think is that the hyper-threading or NT Fiber setti
ng is adversely affecting t
he processors.
Has anyone had any problems with hyperthreading or NT fibers or have any ide
as as to where I might start looking?
Thanks in advance,
DeanDoes the calculation use the same query plan on both systems?
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:EE7A4018-48E7-405C-90F2-2CF0FD54F256@.microsoft.com...
> We've got a customer whose system is performing below average. Example, a
billing calculation that takes 13 minutes on my laptop (XP PRO, 2.2 GHz, 1GB
RAM, 4200RPM HD, SQL 2000 SP3) takes 28 minutes on their server (WIN2000,
2-1.5GHz, 2GB RAM, RAID5 array, SQL 2000 SP3). We turned on the performance
monitors recently and watched a bill run. The hard drives and RAM were
barely used while the 4 processor instances (2 physical = 4 logical with
hyperthread) were all consistently above 90%.
> We deduced that the processors were the obvious bottleneck but we are
unsure why a less powerful machine (my laptop) out-performs the more
powerful server. The only thing I can think is that the hyper-threading or
NT Fiber setting is adversely affecting the processors.
> Has anyone had any problems with hyperthreading or NT fibers or have any
ideas as to where I might start looking?
> Thanks in advance,
> Dean
>|||You can't compare these two systems side by side. For instance your laptop
has a faster processor (even though it only has one) and most likely has
more level 1 cache. The disk in the laptop probably has more cache as well
which can make a big difference over a Raid 5. That said you can still be
running into a multi-processor issue but it is hard to say for sure. Try
running the process with MAXDOP set to 1 and see if that makes a difference.
Are you sure the disk queues are fine? Is there just one RAID 5 and is
everything (data, tempdb, logs) on the same array?
Andrew J. Kelly
SQL Server MVP
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:EE7A4018-48E7-405C-90F2-2CF0FD54F256@.microsoft.com...
> We've got a customer whose system is performing below average. Example, a
billing calculation that takes 13 minutes on my laptop (XP PRO, 2.2 GHz, 1GB
RAM, 4200RPM HD, SQL 2000 SP3) takes 28 minutes on their server (WIN2000,
2-1.5GHz, 2GB RAM, RAID5 array, SQL 2000 SP3). We turned on the performance
monitors recently and watched a bill run. The hard drives and RAM were
barely used while the 4 processor instances (2 physical = 4 logical with
hyperthread) were all consistently above 90%.
> We deduced that the processors were the obvious bottleneck but we are
unsure why a less powerful machine (my laptop) out-performs the more
powerful server. The only thing I can think is that the hyper-threading or
NT Fiber setting is adversely affecting the processors.
> Has anyone had any problems with hyperthreading or NT fibers or have any
ideas as to where I might start looking?
> Thanks in advance,
> Dean
>|||I expect so. On my laptop I just took a copy of their database and re-ran th
e process.|||We monitored the server with performance counters on the processors, RAM and
drives. Yes, it is one big RAID partition but the monitors showed almost no
activity on the disks and very little on the RAM while the processors were
very high.|||Look at the estimated query execution plan on both systems. Also, check the
disk queue length on our data drives. A long queue length may indicate a
bottleneck at the hardware layer.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:601177D1-D700-441B-9607-A15EA29855B4@.microsoft.com...
> I expect so. On my laptop I just took a copy of their database and re-ran
the process.|||I just resolved a performance issue related to the fact that the execution p
lan was different between my development & production.
It was directly related to differences in hardware, specifically the environ
ments you describe. I wouldn't assume that the execution
plans are the same. I'd run an execution plan in both environments to confir
m.
If you find that the execution plans are different (and parallelism is invol
ved), I would suggest looking up the query hint OPTION
(MAXDOP 1) in BOL.
ChrisG|||I would still change the MAXDOP and see what happens.
Andrew J. Kelly
SQL Server MVP
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:827EF5AF-5FC0-43FD-BFD5-BD7D7A89C3D8@.microsoft.com...
> We monitored the server with performance counters on the processors, RAM
and drives. Yes, it is one big RAID partition but the monitors showed almost
no activity on the disks and very little on the RAM while the processors
were very high.|||Exactly, I had this issue when we were doing the migration, taking half time
when we turned that off. Is that a Dell machine?
Yes, you should turn that off.|||I'll try it and see what happens...
) you can have a look at http://msdn2.microsoft.com/en-us/library/ms143441.aspx as well 
...