HYPER-THREADING configuration hints

Hi
We have a SQL 2000 ENTERPRISE, running on four Hyper-threaded processors, 8
GIG of Memory, Windows SERVER 2003, connected to EMC.
Does anybody have any general rules for configuration for such a server
(it's going to be OLTP) Should we set the affinity to only use the "REAL"
processors. Should we allow all processors to be used for parallelism or
just the "REAL" ones. Anything else we need to consider?
Right now during load testing we are seeing a lot of CXPACKET waits.
Thanks
Paul
Try setting your MAXDOP to use just 4 processors first and see how that
goes.
Andrew J. Kelly SQL MVP
"Paul" <xx@.nospam.com> wrote in message
news:%23AGSkbP0EHA.1264@.TK2MSFTNGP12.phx.gbl...
> Hi
> We have a SQL 2000 ENTERPRISE, running on four Hyper-threaded processors,
> 8
> GIG of Memory, Windows SERVER 2003, connected to EMC.
> Does anybody have any general rules for configuration for such a server
> (it's going to be OLTP) Should we set the affinity to only use the "REAL"
> processors. Should we allow all processors to be used for parallelism or
> just the "REAL" ones. Anything else we need to consider?
> Right now during load testing we are seeing a lot of CXPACKET waits.
> Thanks
> Paul
>
|||I have used the number of physical processors minus 1 as a stating point. In
you case I would start with 3 and work down from there, testing after each
change. Your mileage will vary based on the number of concurrent users and
the types of queries they are executing.
Rob
"Paul" wrote:

> Hi
> We have a SQL 2000 ENTERPRISE, running on four Hyper-threaded processors, 8
> GIG of Memory, Windows SERVER 2003, connected to EMC.
> Does anybody have any general rules for configuration for such a server
> (it's going to be OLTP) Should we set the affinity to only use the "REAL"
> processors. Should we allow all processors to be used for parallelism or
> just the "REAL" ones. Anything else we need to consider?
> Right now during load testing we are seeing a lot of CXPACKET waits.
> Thanks
> Paul
>
>

HYPER-THREADING configuration hints

Hi
We have a SQL 2000 ENTERPRISE, running on four Hyper-threaded processors, 8
GIG of Memory, Windows SERVER 2003, connected to EMC.
Does anybody have any general rules for configuration for such a server
(it's going to be OLTP) Should we set the affinity to only use the "REAL"
processors. Should we allow all processors to be used for parallelism or
just the "REAL" ones. Anything else we need to consider?
Right now during load testing we are seeing a lot of CXPACKET waits.
Thanks
PaulTry setting your MAXDOP to use just 4 processors first and see how that
goes.
Andrew J. Kelly SQL MVP
"Paul" <xx@.nospam.com> wrote in message
news:%23AGSkbP0EHA.1264@.TK2MSFTNGP12.phx.gbl...
> Hi
> We have a SQL 2000 ENTERPRISE, running on four Hyper-threaded processors,
> 8
> GIG of Memory, Windows SERVER 2003, connected to EMC.
> Does anybody have any general rules for configuration for such a server
> (it's going to be OLTP) Should we set the affinity to only use the "REAL"
> processors. Should we allow all processors to be used for parallelism or
> just the "REAL" ones. Anything else we need to consider?
> Right now during load testing we are seeing a lot of CXPACKET waits.
> Thanks
> Paul
>|||I have used the number of physical processors minus 1 as a stating point. In
you case I would start with 3 and work down from there, testing after each
change. Your mileage will vary based on the number of concurrent users and
the types of queries they are executing.
Rob
"Paul" wrote:

> Hi
> We have a SQL 2000 ENTERPRISE, running on four Hyper-threaded processors,
8
> GIG of Memory, Windows SERVER 2003, connected to EMC.
> Does anybody have any general rules for configuration for such a server
> (it's going to be OLTP) Should we set the affinity to only use the "REAL"
> processors. Should we allow all processors to be used for parallelism or
> just the "REAL" ones. Anything else we need to consider?
> Right now during load testing we are seeing a lot of CXPACKET waits.
> Thanks
> Paul
>
>

Hyper Threading for a cluster envirnoment

Hi
We are running SQL server in a cluster environment and Hyper threading are
enabled in that servers. The application and Database servers are
different. The appliactions stores 2 MB size of acrobat files to the table
which contains more than 600000 records . We are finding some kind of
issues with this servers.
Couple of SQL DBAs mentioning that , Hyder threading needs to disable in
that server .Is hyper threading cause any issue related to SQL Server
envirnoment.
Please reply
Regards
Swami
ganesh2012@.hotmail.com
Hi
I see no reason why you should. HT processors act like there are 2
processors installed.
What issues are you having?
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Swami" <Swami@.discussions.microsoft.com> wrote in message
news:54181388-7DEA-46D4-AA5E-D183BE5846E5@.microsoft.com...
> Hi
> We are running SQL server in a cluster environment and Hyper threading
are
> enabled in that servers. The application and Database servers are
> different. The appliactions stores 2 MB size of acrobat files to the
table
> which contains more than 600000 records . We are finding some kind of
> issues with this servers.
> Couple of SQL DBAs mentioning that , Hyder threading needs to disable in
> that server .Is hyper threading cause any issue related to SQL Server
> envirnoment.
> Please reply
> Regards
> Swami
> ganesh2012@.hotmail.com
>
|||In general, hyperthreading is a good thing. There are some strange
behaviors on 8 or more processor clusters but these only happen under some
very specific conditions. I do find that you will need to lower the
maximium degree of parallelism to the actual physical processor count to
avoid oversaturating a system. Please reply with more details on exactly
what your symptoms are so someone here can give you a better answer.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Swami" <Swami@.discussions.microsoft.com> wrote in message
news:54181388-7DEA-46D4-AA5E-D183BE5846E5@.microsoft.com...
> Hi
> We are running SQL server in a cluster environment and Hyper threading
are
> enabled in that servers. The application and Database servers are
> different. The appliactions stores 2 MB size of acrobat files to the
table
> which contains more than 600000 records . We are finding some kind of
> issues with this servers.
> Couple of SQL DBAs mentioning that , Hyder threading needs to disable in
> that server .Is hyper threading cause any issue related to SQL Server
> envirnoment.
> Please reply
> Regards
> Swami
> ganesh2012@.hotmail.com
>
|||Geoff N. Hiten wrote:

> In general, hyperthreading is a good thing.

> There are some strange
> behaviors on 8 or more processor clusters but these only happen under some
> very specific conditions.
Could jou post a link to some info on this?
Hans
|||Here is one I actually oberved. It is a very unusual set of circumstances
that caused the problem.
FIX: Intermittent query slowdowns and corresponding high CPU utilization
http://support.microsoft.com/default.aspx/kb/835864
Here was another fix earlier to handle some other synchronization
performance issues.
FIX: Intense SQL Server Activity Results in Spinloop Wait in SQL Server 2000
Service Pack 2
http://support.microsoft.com/default...b;en-us;818763
I am pretty sure SP2 had something in it for Hyperthreading, most likely
counting the processors correctly for licensing restrictions.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Hans de Bruin" <hans@.invalid.invalid> wrote in message
news:1107960610.727258@.draco.amc.uva.nl...[vbcol=seagreen]
> Geoff N. Hiten wrote:
>
some
> Could jou post a link to some info on this?
> --
> Hans