Jeff Atwood's goal was always "performance is a feature," and that means rendering your pages faster than anybody else's. With that in mind, you're always going to have technical hurdles to overcome on any platform - because you want to get faster results than anybody else is getting.
I remember when I first got involved, and Jeff told me something along the lines of, "This slow query runs in ~500ms, and we want it to run in ~50." That statement alone is a huge leap over what a lot of RDBMS users say - usually when people refer to a slow query, their unit of measurement is whole seconds, not milliseconds. They were serious about performance from the get-go.
> Luckily they were able to get the Microsoft SQL team engaged to get them through this.
Nothing against Microsoft - I love 'em dearly and make a great living with their tools - but Stack's success is much more due to their own internal team's dedication to performance. When we open bug requests with Microsoft, the time to resolution is typically measured in weeks or months. During that time, Stack Exchange's team has to come up with creative, impressive workarounds. They're the sharpest SQL Server users I know.
> Finally, their team was world class. BrentOzar, SamSaffron, MarcGravel (and others) are highly respected members of the SQL & .NET community.
Awww, shucks, but I'm not brown-nosing Stack when I say that their current team is ridiculously good. Their Site Reliability Engineers know more about SQL Server than most career DBAs I know.
> I'm not sure many teams would pursue this architecture if they knew the effort (and luck) involved.
It sounds like you're implying that other architectures are faster by default and with less effort, and I would disagree there. I haven't seen any big sites (big being top-100-in-the-world[1] type stuff) where the persistence layer was set-it-and-forget-it. Scale - especially millisecond performance at scale - is seriously hard work on all platforms.