There was a time when I loved the handling of my 3-series with sport suspension. Now, I just want something that makes my toddler less pukey on these shitty northeastern roads.
To take two examples I shopped for last year, the cargo space for a Ford Escape is equal to that of a Prius v, but the Escape costs significantly more for equivalent features and even with "decent" fuel economy it burns twice as much gas. I wanted something that could tow a trailer but ended up not going with any of the small SUVs I looked at, because they just couldn't hold a whole lot, and they weren't worth the premium.
I think people may overestimate the cargo space of small SUVs, partly because the shape hits our volume-estimation biases, and partly because people "know" that SUV means large cargo capacity. Big ones will haul a lot, but small ones are mostly just funny-shaped cars.
Your speculation about fear is spot on. I've lost count of how many times I've heard people say that they want an SUV because it's safer and because they can see better, even though the first is typically untrue and the second is a pretty small effect (not to mention that they're basically ruining it for everybody else).
I used to share your disdain for SUVs until I dated someone who drove one. They can be really useful for carrying cargo and the smaller ones get decent fuel economy.
That said, I think a lot of SUV buyers are motivated by fear. They feel that collisions are inevitable so they want to drive a big armored box. They want 4WD even here in the Southern USA where it snows 2 days per year. I think a lot of them have kids and it's one more part of the protective worrying parent mentality.
Personally I can't stand the handling of tall vehicles.