Hardware

For dual CPU motherboards, of which there are a few on the market mainly targeting the server-oriented clientele, I was wondering how this works in practice. Will the two CPUs be detected automatically not only by the BIOS, but also by the operating system? In other words, will the user see one logical instance of the computer or does the user need to switch somehow between the two instances?
What is the real use of dual-CPU motherboards, given that there are already 16-core CPUs on the market?

Boards with multiple CPU's happened first, I remember dealing with two-CPU Pentium III boards... Multiple-core CPU's are just an enhancement of this older idea. The BIOS for a multiple-CPU board naturally supports multiple CPU's. Intel's single-core "Hyperthreading" CPU also acted like multiple CPU's, though in reality they weren't quite.

Software and hardware-wise, AMD built a solid standard for this when they introduced 64-bit x86 processors. They were designed to scale much farther. Either/or should be no problem for a modern OS.

16 cores competing for memory at once would be quite a logjam! The advantage of multiple CPU chips, rather than multiple cores in one chip, is that each CPU can have its own data bus. Some AMD chips even have their own built in memory controllers, making multiple sets of wholly-independent RAM possible(joined by Hypertransport when necessary). CPU's hold each other up less while working.

This takes more hardware and more complex hardware, so costs more. Just ramping up RAM speed higher and higher is an alternate solution.

That makes sense. With the advent of GPUs, a lot of the computational effort and memory requirements has already been offloaded from the CPU.
RAM is relatively cheap compared to its speed and capacity, so favoring an investment in RAM over hard disk space makes dual CPU boards even more attractive.

For gamers, yes. For pure mathematics, maybe. For servers, not so much. GPU's are poor at branching.

Yes, I didn't mention that the intent is to look into more flops per watt for a compute cluster.

GPU's are only good at a select few tasks. CPU's are much faster for general purpose computing. Not to mention that only certain specilized applications are even capable of doing GPGPU work.

As for multi socket PC's, they've been around since the early 1990's. There are dual socket 486's even. Nothing new. Not really needed on the desktop these days though, with the new multi-core chips.

Most desktop users can't utilize more than 4 cores right now anyhow, desktop software simply isn't written to take advantage of it. A 16 core CPU on the desktop is a waste, unless you're doing a massive amount of code compiling, or some kind of specialized scientific work. But that would be more of a "workstation" use case than a "desktop" one.

That is exactly the idea. We need raw unadulterated computing power all coming from a single piece of retail circuitry. A RAM only computer that will run data intensive simulations for years on end, with daily monitoring of results and monthly data uploads.
We have already developed expertise in multi-threaded C++ and are looking to foray into CUDA and pyCUDA. About 40% of our computers already run in RAM only with a USB stick for startup and data.