The only time I can think of where my character was explicitly an orphan as part of their backstory was in my first campaign ever, where the DM made that backstory element part of the game's basic conceit (the entire party had grown up in the same orphanage). Though after playing that character across like three different campaigns, it ultimately turned out that I wasn't an orphan because my parents were dead, it was because my parents were the lords of the Fae (literally Oberon and Titania) and they wanted me to grow up with a mortal perspective so they dumped me in an orphanage somewhere with a gaggle of half-siblings.

Since then, my characters have had parents who were uninvolved (half-dragon paladin who grew up in a monastery), far away (they lived in a tribe while I was out doing mercenary stuff), or just never came up (most of the time), but I've never specifically cited having dead parents as part of my backstory. More often than not I see the "dead family" motivation as an excuse to not characterize them and allow the PC to exist in a vacuum. That's the boring part, really. PCs who just sort of exist with no real background to speak of.

It's definitely possible to make the dead family thing work. Frank Castle losing his family became his driving factor, but it's made clearly apparent that the incident scarred him and defined his outlook, it didn't just sever his roots. The people who raised Luke Skywalker had to die for him to leave home, and while it didn't seem to bother him too much it was clear he was frustrated and dissatisfied where he was and it was hardly the end of his family's impact on his life.

The dead family thing can work. The problem only comes in when it's used as a cop-out because the player doesn't want to be a part of the world they're exploring.