Processor usage

[Anonymous_7]

We are testing on a DL740 with 4 physical Xeon processors, Win2k3 and SQL2K. I know the OS is hardcoded to use CPU 0 - so in all of our tests this processor shows a little higher utilisation but I will ignore that when I explain the following.

Can anyone explain why the following happens?

Test1 - allow SQL to use all processors. CPU 0,2,4,6 show no performance. CPU 1,3,5,7 are heavily utilised. This equates to both logical processors on every second Xeon.

Test2 - disable 1,3,5,7. CPU 0,2,4,6 are now used heavily. Two Xeons are now disabled.

Test3 - disable 4,5,6,7 (i.e. all the second logical CPUs on each Xeon). CPU 0,1,2,3 are now share the load.

Test4 - disable 0,4 (i.e. first physical processor). All other CPU's now share the load.

Test5 - Disable CPU 0 (i.e. first logical processor). All other CPU now share the load.

Test6 - repeat of test1 with exactly the same results!

Any ideas very much appreciated.

[Rune J. Winje]

That you have 8 processors (logical) shows that you have hyperthreading activated in the BIOS.

Both the OS (2003 is OK) and the application (SQL2K) have to utilize the added CPUs effectively to see any gains.

SQL2K needs SP3 to fully utilize Hyperthreading with 8 (logical) CPUs. With pre-SP3 it only supports 4 (logical or physical) CPU's (for the standard Ed of SQL)
http://www.microsoft.com/sql/howtobuy/hyperthreading.asp

Another consideration is performance. SQL does not scale significantly better beyond 4 CPU's. Disabling HTT may be a better option depending on the application/usage.
http://www.microsoft.com/windows2000/server/evaluation/performance/reports/hyperthread.asp

I think I've seen docs describing that SQL2K scales well up to 3-4 CPU's but not so good beyond...


Cheers,
Rune

[Anonymous_7]

Thank you for your comments.

When you say SQL scales well upto 3 or 4 CPUs are you meaning physical or logical?

Obviously since we have purchased hyperthreading processors and HT compliant W2k3 and S2k, disabling HT would be an odd choice.

My original question is why are we seeing the patterns described?

As part of our scale-up testing we are seeing better results with 4 than 8 physical processors! This has to do with the extra load of reloading the processor cache each time CPU threads are switched to different CPUs. Any ideas why that happens or what we can do to stop it?