So much of this depends on where you ride, and that's why we'll never all agree. If you live where there's a lot of high speed multi-lane roads and your off-roading consists of mostly gravel roads or wide open spaces then big is good. OTOH folks whose riding involves things like boulder-strewn single track, twisty trails through thick woods, deep mud, soft sand, etc. and rarely go on roads other than quiet secondary highways are going to favor small and light unless they are so talented that they can horse a big bike through that stuff.
Dual sports in the 350-650cc range come close to being the perfect compromise but the drawback for many folks is that they are only mediocre at doing things like single track or long freeway trips. That's why we each may need at least 3 bikes: a big long-range powerhouse, a nice light woods-weapon, and then a compromise bike for the times you just want to meander.