When articles like this come up (i.e. "this feature sucks at scale") I shake my head.
First, why weren't they testing the feature at scale?
But more importantly, it brings up the KISS principle yet again. All these niche features might solve one (miniscule) class of problem but there are so many of these little specialty features! As a result SQL Server is becoming more bloated all the time with a large chunk of features that are more death trap than savior, performance wise.
At the same time the basic engine (the old CRUD statements) tend to have a lot of attention poured on them, meaning they tend to be blazingly fast. It's the same old same old, developers want to write new flashy features that are interesting and fun and tend not to want to polish the old stuff.
The result is a big messy glob of features that half-work, piled on top of the core engine.
I have always said a few well chosen tools are worth a toolbox full of junk. Keep it simple. Avoid flashy new gimmicks. I mean, a 50% speed increase simply from shifting from table variables to temp files? Really?
A lot of SQL Server features are like that, half-baked or incredibly narrow in scope. The SQL Server team needs to focus on elegance, not a laundry list.