Intel Xeon 5600 (Westmere-EP) and AMD Magny-Cours Performance Update

Very Important: Get a decent QA system. I would say that the 5345 is a decent processor and not due for replacement. But if you do not currently have a QA system, then by all means get the latest 2-way system, example X5650, so that you do have a QA system.

Next: a 1 second query today is not a small query, its 2-3 Billion CPU-cycles per core. Small is a query that runs in 10milli-sec. It is important to specify both the average CPU and duration (elapsed time) per query. If your average query consumes 1 CPU-sec, then each core can do 86,400 per day, assuming uniform traffic, all at max load. On an 8-core system, thats 691,200 per day, not 20-30mil. So I suppose you meant 1-sec duration and the CPU is less. Presumably more than 30X less to support 20-30M per day.

Anyways, 30M queries per day means 1.25M per hour. Assuming you have peak hours, your load might actually need to hit 3.6M per hour, or 1000 per sec. If you have a single core system, the average query cost would need to be 1 milli-sec, but on a 12-core system, you could afford 12-ms average.

Assuming a range of query costs, a few might benefit from a parallel query plan, possbily maxdop 2 or 4. I would run tests on a QA system, find out which could generate a parallel plan, then explicitly allow these to have parallel plan, leaving everything else on non-parallel.

Forcing each connection to a specific core can help if your average CPU cost is well below 1 milli-sec. Above that I would not bother. Besides, this is a really complicated subject, that is best left to experts.

so what you need to do is figure out the average CPU per SQL call, ie, the top level stored procedure called from the client/app server. the average duration (1 sec) has no meaning, other than its kind of a long time on modern systems.

unless your average cpu per RPC is well under 1 milli-second (my estimate is under 0.3 ms), there will not be much gain in employing thread affinity.

what makes you think they are assumptions?

You should have read enough of my work by now to know I do waste time with worthless crap.

As far I know, I am the only one to have published quantitative analysis on high-call volume chatty apps, NUMA and affinity tuning. If you think my data above is not correct, test it out for your self.

For reference, the TPC-C and TPC-E benchmark queries average on the order of 0.5 CPU-ms with the Xeon 5500\5600\7500 series (based on physical core CPU-s, not logical). On the 2-way systems, there is some benefit with port-thread affinity alignment. On the bigger systems the benefit becomes more substantial.

In SAP type applications, where the average call is less than 0.1 CPU-ms, port-affinity alignment is absolutely crucial, resulting in negative scaling on hard NUMA systems without this.

A few years ago, you did a performance eval of an 8-way(+?) Intel hard NUMA system, versus 4-way Opteron dual-core (soft-NUMA) system, find almost no scaling beyond 4-way(?). Published benchmarks showed moderate scaling. The benchmarks used port-affinity tuning and you did not. The benchmark test you were using was on the order of 0.5-1.0 CPU-ms per call, which really needed the affinity tuning on NUMA systems. If the average call were 1000 CPU-ms, then it would not have mattered.