Rob van der Meijden

Today I was running some performance tests on a brand new blade server. These are singe processor (can be two) 3Ghz Intel Xeon machines with 3Gb of memory running w2k3 sp1. I was wondering if there was a big difference in performance and server behavior when turning hyperthreading on and off. I heard some rumors that hyperthreading and Sql server 2000 (Sp3a) decreases the performance. I a nutshell the program: 

• Unload data from a 8Gb database (using .Net datareaders, approx 60 million records )
• Convert the data in memory (using a .Net c# program) 
• Write the data to a flat file (using .Net StreamWriter) 
• Load the results into another database (using DTS bulk insert in a separate threat, approx 18 million records) 
• Creating the indexes on the destination table (using T-SQL) 
• Because of the huge number of records and memory limitations the program handled ½ million records each time.  

This program ran with the exact environmental conditions twice, once with hyperthreading and once without hyperthreading … and the results are …

• Hyperthreading off: total run time 65:30 minutes, during step 1-4, approx 45 minutes 100% CPU, during step 5 (last 20 minutes) 30% CPU usage.  
• Hyperthreading on: total run time 62:30 minutes, during step 1-4, approx 40 minutes 60% CPU usage, during step 5 (last 20 minutes) 30% CPU usage.  

So there is not a big advantage in total run time (5%) but there is in CPU usage -> 40%, building the indexes (1 clustered and 3 normal) there was no difference. For this scenario the hyperthreading mode will stay on in case the server has to do something else ... HL2 

When you are running a pre Win2k3 server see Karen’s blog and this is the Microsoft story about SQL server 2000 and hyperthreading.