Local SQL Queries slow on Dual CPU/hyperthreading hardware

We've discovered that under some circumstances running SQL queries locally
are significantly slower than when running the same payload over the
network, but only when hyperthreading is switched on using the same physical
hardware. Please also not that in these tests, I've set SQL server to on run
on 1 CPU only, and set with a parallelism of 1 (which should be irrelevant
when running on only one CPU).
A pretty simple payload can be used to reproduce the problem - as long as
there are enough lines! In fact you don't need to run the query at all -
simply parsing it in SQL query analyzer is enough.
For example, I've used:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
1;A]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[A]
GO
create table [dbo].[A] ([A] [int], [B] [int], [C
] [int])
GO
and then I've repeated it 1000 times (one way to create this is to use
Excel...), to produce a 4000 line payload.
Note that I've chosen this just as something that passes the syntax check -
not SELECTing, UPDATEing,
DELETEing or INSERTing anything at all - the table is empty!
Pasting this into SQL Query analyzer, and then hitting the parse button
takes varying amounts of time, as shown below:
When running queries locally:
With hyperthreading on it always runs slow (17s)
With hyperthreading off it always runs fast (<1s).
Networking protocol makes no discernable difference - run this with
named pipes, TCP/IP and Shared memory protocol make no difference.
When running queries remotely (i.e running Query analyzer on a different
machine connected via the network):
Hyperthreading on or off makes no discernable difference to the
performance time.
When connected with named pipes it always runs fast (~2s).
When connected with TCP/IP it takes ~25s.
We've tried this test on both XP and Server 2003 OS's on different
hyperthreading hardware and get very similar results. Initial tests suggest
that something similar may happen on Dual CPU hardware, but we haven't fully
investigated this yet.
The difference between named pipes and TCP/IP over the network I understand,
and am not too worried about. But the fact that I can make LOCAL queries run
much faster by disabling hyperthreading is a real issue for us - we have
services running on the local machine that query and update the database.
Initally we thought that this was a Windows 2003 server issue, but we can
reproduce it on XP. We haven't tried on Win2k, but would have expected to
see it before now if it was so obvious - we have several Win2k dual CPU
machines that have been running the same software on customer sites for the
last 3-4 years.
Thanks a lot!
Stephen Brown
(P.S. I apologise for posting this before, but I wanted to change the
emphasis from my earlier post.)Have you tried simplifying the script? For example, what if you do something
like:
" decalre @.a int
set @.a = 0
set @.a = @.a+1
.. repeat 1000 times
print @.a" ?
This will tell if the problem is related to the SQL script you send or not.
If not related to the SQL script, then the problem is likely to be in the
network layer.
Is your windows 2003 server and Windows XP clean installed? Do you have
anti-virus, network monitoring, etc running? Any special network driver?
Which version of SQL Server?
You can also contact microsoft support to get this problem resolved.
http://www.microsoft.com/sql/support/default.asp
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chillipepper" <chillipepper@.nospam.nospam> wrote in message
news:%23NyYY3YHFHA.3072@.TK2MSFTNGP10.phx.gbl...
> We've discovered that under some circumstances running SQL queries locally
> are significantly slower than when running the same payload over the
> network, but only when hyperthreading is switched on using the same
> physical hardware. Please also not that in these tests, I've set SQL
> server to on run on 1 CPU only, and set with a parallelism of 1 (which
> should be irrelevant when running on only one CPU).
> A pretty simple payload can be used to reproduce the problem - as long as
> there are enough lines! In fact you don't need to run the query at all -
> simply parsing it in SQL query analyzer is enough.
> For example, I've used:
> if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].&
#91;A]')
> and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[A]
> GO
> create table [dbo].[A] ([A] [int], [B] [int], [
;C] [int])
> GO
> and then I've repeated it 1000 times (one way to create this is to use
> Excel...), to produce a 4000 line payload.
> Note that I've chosen this just as something that passes the syntax
> check - not SELECTing, UPDATEing,
> DELETEing or INSERTing anything at all - the table is empty!
> Pasting this into SQL Query analyzer, and then hitting the parse button
> takes varying amounts of time, as shown below:
> When running queries locally:
> With hyperthreading on it always runs slow (17s)
> With hyperthreading off it always runs fast (<1s).
> Networking protocol makes no discernable difference - run this with
> named pipes, TCP/IP and Shared memory protocol make no difference.
> When running queries remotely (i.e running Query analyzer on a different
> machine connected via the network):
> Hyperthreading on or off makes no discernable difference to the
> performance time.
> When connected with named pipes it always runs fast (~2s).
> When connected with TCP/IP it takes ~25s.
> We've tried this test on both XP and Server 2003 OS's on different
> hyperthreading hardware and get very similar results. Initial tests
> suggest that something similar may happen on Dual CPU hardware, but we
> haven't fully investigated this yet.
> The difference between named pipes and TCP/IP over the network I
> understand, and am not too worried about. But the fact that I can make
> LOCAL queries run much faster by disabling hyperthreading is a real issue
> for us - we have services running on the local machine that query and
> update the database. Initally we thought that this was a Windows 2003
> server issue, but we can reproduce it on XP. We haven't tried on Win2k,
> but would have expected to see it before now if it was so obvious - we
> have several Win2k dual CPU machines that have been running the same
> software on customer sites for the last 3-4 years.
> Thanks a lot!
> Stephen Brown
> (P.S. I apologise for posting this before, but I wanted to change the
> emphasis from my earlier post.)
>|||Hi Wei Xiao ,
Thanks for your response. In answer to your questions:
I have tried your suggestion and it DOES make a significant difference -
i.e. the simple set-a-local-variable script runs almost instantaneously both
remotely and locally, irrespective of hyperthreading or not.
The OS's that I have used are both patched and up to date. The XP OS is on a
development machine (i.e. heavily used!), and running e-Trust antivirus. The
2003 OS is on a test machine - the OS has been recently rebuilt, and has no
anti-virus installed. Both machines have intel chipsets - one is using the
onboard intel network, the other using a netgear card. I have run the test
with networking disabled, and it made no difference.
I'm using SQL server SP3a - and see the same behaviour on MSDE, developement
and standard editions.
I have tried all of the usual monitoring tools - perfmon, the network
monitor, SQL profiler etc. Whether running these tools or not makes no
difference. I have looked at disk performance, CPU performance, memory
allocation, network queues etc etc, and nothing seems to be busy at all. All
I have discovered is that when the query runs quickly (i.e. hyperthreading
off, single CPU) the CPU is used heavily (70-80%) for the few seconds that
the query runs - i.e. it is CPU limited (as expected!). When the query runs
slowly, there is virtually no CPU activity at all.
As you have probably gathered, the problem I posted is a bit abstracted from
our real problem - we have encountered unexpected performance issues when
running our existing applications on a new hardware/software platform. After
weeks of head scratching discovered that SQL server was responding slowly
when accessed locally, but normally when accessed remotely.
Stephen
P.S. We have also come across the issue mentioned elsewhere on this
newsgroup - where SQL server slows down dramatically (this may be restricted
to Server 2003), and recovers after a SQL server service restart. It is
beginning to look to us as if there are two separate issues affecting us -
one possibly Server 2003 related, the other dual processor/hyperthreading
related - and unfortunately we have encountered both at the same time,
making it very difficult to determine what is going on!
"wei xiao" <weix@.online.microsoft.com> wrote in message
news:OWz4HkjHFHA.2744@.tk2msftngp13.phx.gbl...
> Have you tried simplifying the script? For example, what if you do
> something like:
> " decalre @.a int
> set @.a = 0
> set @.a = @.a+1
> ... repeat 1000 times
> print @.a" ?
> This will tell if the problem is related to the SQL script you send or
> not. If not related to the SQL script, then the problem is likely to be in
> the network layer.
> Is your windows 2003 server and Windows XP clean installed? Do you have
> anti-virus, network monitoring, etc running? Any special network driver?
> Which version of SQL Server?
> You can also contact microsoft support to get this problem resolved.
> http://www.microsoft.com/sql/support/default.asp
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Chillipepper,
Generally, this kind of performance issues can be caused by various
factors, and it is difficult to locate the root cause in a newsgroup
thread. If the issue still exists after you have used the troubleshooting
steps above, to efficiently troubleshoot a performance issue, we recommend
that you contact Microsoft Customer Service and Support and open a support
incident and work with a dedicated Support Professional.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
A very good white paper that talks about the SQL Server and HTT technology:
Based on my knowledge, the issue might be caused that he enabled
hyperthread but set parallelism to 1. Would you please set parallelism to 5
to see whether it will improve the performance.
Here is two very good white paper that talks about the SQL Server and HTT
technology, hope it helps
Microsoft SQL Server and Intel Hyper-Threading Technology
http://www.microsoft.com/sql/howtobuy/SQLonHTT.doc
Microsoft Windows-Based Servers and Intel Hyper-Threading Technology
http://download.microsoft.com/downl...a9-bb3f-c01f658
d16b1/hyperthreading.doc
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.