SQL Problems with Hyper-Threading | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL Problems with Hyper-Threading

Hi,
Has anyone had any problems with SQL 2000 and Hyper-Threading? I’ve read conflicting reports online that SQL 2000 is capable of using HTT however others say that this configuration is suicide. Any thoughts from anyone who has experience in HTT would be greatly appreciated! CitiPhil
It ddepends on your particular application. On some of the applications I have seen that enabling HT increased performance and for others, it was an performance killer. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
We use it all the time and have never had any issues. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
I’ve never seen any problems with it either. The only time you don’t want to use it is if your OS can’t recognize all the drives it would produce. SQL Server really takes advantage of the additional parallelism available by hyper-threading. The only thing you will want to do is monitor. Make sure you don’t have some query just going crazy with parallelism and causing problems. As long as you do this, I couldn’t imagine not having my hyper-threading on (we didn’t for a long time). Just make sure you are on the latest service packs for everything. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Hyperthreading is an interesting new technology that has the potential to yield good performance gains on a number of applications. However, it is important to understand that hyperthreading does NOT guarantee the same performance as an existing machine with two processors. The purpose of hyperthreading is to allow the CPU to remain idle less – however, it doesn’t guarantee the throughput of the system (disk IO, memory bandwidth, etc). However, that may or may not be your performance issue on your query. But as of now no issues at our end too. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
SQL Server with SP3a has full support for HTT:
http://www.microsoft.com/sql/howtobuy/hyperthreading.asp
I’m using 4 way zeon 4’s ( 8 procs with HT ) and used 2 way zeon 4’s previously ( 4 procs with HT ) never had any problems and got a good boost for performance. Only thing I’d say is that if you have poor sql queries that give paralellism problems HT will make it worse!! Had problems with cxpacket locks with HT that were generated by out of date stats.
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by colin leversuch-roberts</i><br /><br />I’m using 4 way zeon 4’s ( 8 procs with HT ) and used 2 way zeon 4’s previously ( 4 procs with HT ) never had any problems and got a good boost for performance. Only thing I’d say is that if you have poor sql queries that give paralellism problems HT will make it worse!! Had problems with cxpacket locks with HT that were generated by out of date stats.<br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br /><img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> You really, really, really need to listen to this. Your whole performance tuning process needs to change when you make an upgrade from an old system to a multi-processor HT server. The paralellism is one of the interesting things to tune that can have large affects on overall performance. There will be areas you will sacrifice a slightly longer runtime (reports) by setting the MAXDOP down so other things can continue to use the width of your multiprocessors. It’s a fun and rewarding change of pace.<br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
]]>