Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ACALVETT
Starting Member
34 Posts |
 Posted - 2005-09-30 : 05:31:56
|
Hiya,We have quite a few SQL Servers running on servers with hyper threaded enabled processors. We have always left hyper threading on, even on our very busy servers but i've recently begun to question this due to comments i have received.All our servers run SQL 2000 SP3a as "support" for hyper threading was added in SP3aI`m aware of [url]www.microsoft.com/sql/howtobuy/SQLonHTT.doc[/url] but[url]http://groups.google.co.uk/group/microsoft.public.sqlserver.server/browse_frm/thread/99860c74db9d183/af9e69650569f1db?lnk=st&q=hyper+threading+%2B+SQL+performance&rnum=2&hl=en#af9e69650569f1db[/url]has a quote from a SQL MVP stating quote:
The problem with a HT CPU that the 2 simultaneous threads running on the CPU both contend for the *same* L1, L2 and L3 processor cache. During heavy processing, you may actually have the 2 clearing each other's cache. This results in each having to go to main system RAM to work.
I`m still hunting around for more information and am asking about in a few forums to.So... What do you think of Hyper Threading any why? |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-30 : 07:15:29
|
| I can think of two servers that I look after that have very similar volumes of activity. Each week we run a housekeeping task - defrag tables, delete old stale data (several million rows, about the same on both servers), rebuild stats etc. CLIENT-2 is consistently TEN TIMES as fast as CLIENT-1CLIENT 1========Dell PowerEdge 1750Win 2003 - Standard SP12GB RAM2xCPU Xeon 3GHz with hyperthreading (x86 Family 15 Model 2 Stepping 5)PERC LD PERCRAID SCSI Disk 128GB (I presume RAID5, 3 disks)CLIENT 2========Dell PowerEdge 6650Win 2003 - Standard, No SP4GB RAM (No special provision to allocate more than 2GB to SQL)4xCPU Xeon 3GHz with hyperthreading (x86 Family 15 Model 2 Stepping 6)(I'm pretty sure these have the Max on-chip cache)Controller 0 : DELL PERC 4/DC RAID Controller (Channel 1=6 disks RAID5, Channel 2=4 disks, RAID10)Controller 1 : Adaptec AIC-7892 - Ultra160 SCSI (Not sure what this is doing!)The two channels have O/S + LDF and MDFKristen |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-09-30 : 07:18:03
|
| I have heard similar rumblings from other MVP's as well, it seems about 50/50 for turning it on, but everyone does a lot of testing before they make their final decision. There's really no way to know if these issues affect you unless you test and measure.Disclaimer: I personally don't have any experience with hyperthreading, none of the servers I use have it. |
|
|
|
ACALVETT
Starting Member
34 Posts |
Posted - 2005-09-30 : 07:42:45
|
| Yes the more i hunt the more i find its a 50/50 thing and as such its down to individual environments. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-09-30 : 23:55:16
|
| We run hyper-threading on all our environments. I think most issues I've seen people have are resolved by setting max degree of parallelism to the physical number of processors. We haven't seen any issues, although we did have to perforamance tune the first few weeks we had it on. Since then, we've bee fine.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
Hyperthreading - Good or Bad?