The situation is ridiculous. I've played games that are simple reimplementations of DOS games (not via an emulator, but using SDL) that use 20% of a modern 2GHz CPU, while the original ran on a 12MhZ machine.
I remember a blog post full of very specific cases that compiler don't optimize at all because the case could never appear in a performance sensitive code, but you could meet this situation in many code.
There is a ratio benefit/cost in performance inspection, both human workload and compiler code maintenance/performance. You want to focus on what matter and on big code base where every performance curves are flat, you could think everything has been done.
To answer the question:
>Is there any hope of actually getting the promised performance out of modern hardware without having a CPU/assembly expert on your team?
The answer is: it's complicated. Let's assume you want absolute maximum performance, so you use a uni-kernel design (quite a few super computers basically do this). Even without the overhead of virtual memory (minimal on modern hardware), the removal of multi-tasking (expensive but mitigatable) on a single core; you would still have to deal with all the complex things you need to do to run modern hardware. Modern hardware is complex, it's complex because we want things to be fast or have high bandwidth (usually both).
Ultimately you can usually get 90% percent of what the hardware can do with a good compiler, decent software architecture, and tuning to encourage vectorization; probably 95% with a great compiler. Even in user mode, even in Windows. So at the end of the day it's about what's more important: Using as few cycles of a CPU, or as few cycles of your time implementing it. I think the re-implementers chose the latter as they chose to let SDL follow its default FPS instead of tuning it downwards.