HyperThreading and NT Fibers

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 t
he 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
Does 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 the 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 plan was different between my development & production.
It was directly related to differences in hardware, specifically the environments you describe. I wouldn't assume that the execution
plans are the same. I'd run an execution plan in both environments to confirm.
If you find that the execution plans are different (and parallelism is involved), 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...

HyperThreading and NT Fibers

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...

Hyperthetical

Hi,

I am a noob and i have a hyperthetical question

I used to work with C-ASP and am moving to C# - asp.net with ms-SQL server

I have VS 2K5 pro with sql server developer

Just say I have a 30 or 40 table database with 80 colums in each table with various data types for each column

now is there anyway with t-sql to have the program create a "template" or installation file of the database (after i have created teh database in vs2k5 server explorer database connection) ?

What i mean by that is there a way that the probrams a

"create table <<tablename>>"

and builds the SQL for me for the table? if this does not make sense then please tell me and I will try and explain futher..

===

see what i did in the past was create a asp page with the design of the database in SQL and then when i FTP'd it to the server i could just run the page and it would install(create the tables) into the database and i would be able to uninstall(drop) the tables all the same way...

im looking for something similar..i have done it with an XML file, but it was the old way ,,where i had to manually write out the design of the database myself

Hi, fortunately in Management Studio we have a 'Template Explorer' (in the View Menu) which provides various templates including create database/table. You can download Management Studio Express from here:

http://msdn.microsoft.com/vstudio/express/sql/download

|||

Perhaps the use of the word template - i have been sick and my use of words inthe last 48 hours has not been as good as they have been...let me try and explain again.

You have designed a database and normalised this database on paper.

Then you create the database in ms-sql to the design on paper, but the database only exists locally

You would like the database schema in a file so that you can execute the design on the server and it is created exactly the same as locally(similar to a installation file but for the way database is for that solution)

I hope this makes more sense.

|||

pwpaust:

Perhaps the use of the word template **was wrong** - i have been sick and my use of words inthe last 48 hours has not been as good as they have been...let me try and explain again.

small correction

|||Ok that's good Then let's try Script Wizard in Management Studio: right click a database->Tasks->Generate Scripts. This wizard allows you to generate scripts for various database objects with some options, which you can use to create a database with exactly the same schema on another server.|||

hahahaha i just tried that...thats great

I could almost say i love you for answering this post...thanks :)

i appreciate it alot...

|||

if i was to "accidently drop the table" i made the script from

would you be able to point out how i might be able to restore it?

|||If you have dropped a table, I can't find the way to get it back unless there is a backup of the database which was made when the table have not been dropped. However, restore from the backup will bring the entire database to the 'status' when the backup was made.|||

no not a back up..i understand that i meant via that script method you mentioned