For a start, SUVs don't go off road. They are road vehicles. We had off-roaders for a long time in the form of Land Rovers etc. We called them 4x4s and they weren't really comfortable to drive on the road.
I like the idea of having the right vehicle for the right purpose. If I had to travel to work by car (which I've done before and don't want to do again), I would like to drive a Mercedes "Smart" or VW Up, or similar. These are crap, consumable, plastic cars, but they do the job just fine. For the weekends? A sports car or hot hatch would be fun. For long trips and camping? An SUV would be great.
SUVs and the much higher number of HGVs now also tear up the roads, which isn't great for your sports car. You'll have a better chance in an SUV yourself. If you can't beat 'em, join 'em.
The problem is that, in the UK at least, it's really only possible to run one car for various reasons (even if you could afford the depreciation on multiple cars). So the SUV becomes the default. Also I just want a reliable car. I don't want it to be a project and constant worry that my investment is going to fall apart. That means Honda or Toyota. But if I have to choose between the regular hatchback or SUV, I might as well go SUV because it will be more fun. There's the Civic Type-R. I owned one, but I don't really want one of the newer ones (I don't need 300+ bhp in a hatchback).
Unfortunately most people can't afford the "right vehicle for right purpose" approach they they pick the compromise that will be sort of a jack of all trades and master of none but cover pretty much all the needs. This comes with some drawbacks (larger outside dimensions, larger fuel consumption, less maneuverable, etc.) and some advantages (larger inside dimensions, more likely to fare better in a crash due to the added weight and ride height).
Normally I'd say that if you have a specific driving need only 1% of the time, rent rather than try to tack the feature onto your car's requirements. There's no point in driving around with those drawbacks 99% of the time for that 1% when you drive with the advantage. But something like the safety advantage is there all the time, unless the manufacturer really screwed up someplace else.
The biggest problem and misunderstanding lies with the CUV/CSUV segment. They are somehow viewed as "SUVs" but actually have almost all the disadvantages of a SUV with almost none of the advantages. Compared to the equivalent hatchback they sport a slightly larger body that increases drag and fuel consumption, and decreases maneuverability, bigger wheels and raised axles that just eat up more interior space, and probably have little to no safety benefit because the added weight contributes almost nothing to this (a Mercedes GLA weighs ~100Kg more than a normal A-class hatchback).
Growing up my family of five were able to comfortably fit all of us plus any luggage/gear in an early 80s subcompact hatchback.
We used to pack up our 5 member family and suitcases for 3 weeks at the seaside into a 2. generation Voklswagen Golf https://en.wikipedia.org/wiki/Volkswagen_Golf_Mk2
Us and every other family. I suppose the average american uses a 18-wheel cargo truck to go to vacation.
“On average, a professional water delivery service will charge anywhere from $175 to $380 per truckload., and the average truck will hold 6,000 gallons.” - https://www.howmuchisit.org/pool-water-delivery-cost/
Could buy a lot of those for the price of an SUV, especially sharing the price between all the people.
Inb4 the tournament is held on a remote mountain top inaccessible to tankers and hatchbacks alike and only accessible to 4x4s so there’s literally no other choice.
That easily fits in most cars unless we're talking about Smart's or sports cars.
It became kind of fashion thingie for those who don't understand cars, or basic physics. Soccer moms love them, since they often don't have good driving skills, it puts them higher so they have better overview of the road (while blocking any view of those behind). Pollution, higher maintenance costs, much higher danger of car rolling during accident (and break your neck) be damned.
Then there is category of these tiny 'SUV' (I think proper term is crossover), which are just bigger shopping carts, being a higher above ground. So from driving perspective, the worst combination - small, badly maneuverable car.
We still have big wagons for those who are numerous / carry around a lot of things. These I prefer - they drive well, trunk space is enormous. Compared to most SUV which look big on the outside, but not so much inside.
/rant