Performance impact: hyperthreading for OLTP queries -- II

Greg;

All the cores and all the logical processors when HT was enabled are fully loaded with this particular workload. In one of the previous posts about a sign of the times, I did mention that with the TPC-C read-only transacions unchanged, I was not able to drive the processors to their full capacity with SQL2008 R2 RTM. I still can't with SQL2008 R2 SP1. In that case, I did see every other logical processors relatively idle, although not completely idle. And when that happened, the %Privileged Time was very high. For instance, the total %Processor Time may be 20% and the %Privileged Time could reach 18%, which is extraordinarily high. I never got a chance to try out the affinity configuration with the unchanged workload, but probably should have and may give it a try when I get time. I did try lightweight poolings, and that did not help. Also note that the problem only happened with a relatively high core count.

Greg;

My tests do not involve any disk I/Os. I only use the read-only transactions and cache all the data in memory. I'm interested in evaluating the host server and including the storage component only help comfound the investigation. This is also documented in my previous posts. But perhaps I should do a post on why the storage component should be eliminated from these tests so that it is 100% clear.

Greg;

I didn't answer your question in my previous reply wrt TPC-C and TPC-E. The way I configure the tests, I'd venture to say that it probably wouldn't matter too much, but that's just a guess and I'm not totaly sure until I do some actually comparison. But the reason for the guess is that when all the data is cached, it's just a bunch of page lookups/scans. For two reasons, I have sticked with TPC-C. First, I'm not doing real official TPC-C benchmarking. I use it for some very specific purposes and most times, I configure it to help evaluate server hardware. For that, it has worked really well for me. And because of that, I've accumulated a lot of data that I can use for comparison purposes, making it harder to switch to a different one and start all over again.

The second reason is just that I'm not as familiar with TPC-E, not in terms of what it is, but in terms of not having developed an intimate feel for it. That's actually pretty important in testing. Say, with my version of TPC-C, I can easily and quickly tell if the results do not look right, and so on, and therefore and can quickly correct whatever I might be doing wrong.

Yes, I think I'm doing myself a service by keeping the SQL test workload relatively simple (while relatively scalable within a limit) when evaluating hardware. It makes it easy to verify that whatever change I see in the test result is not a result of a change deeply buried somewhere in the workload itself (e.g. a minor plan change). It's a tool and must be trustworthy. If I can't keep the workload constant or need to put in a lot of effort to keep it constant, then it's not worth it for what I want to accomplish.