What you say is true, Sion's passive is often not very strong or effective, its strengths lie in its surprising damage but it is difficult to pull off because of Sion's lack of mobility. To use it effectively, important skills are orb walking/attack move click, knowing when to use the speed boost, and positioning. You can't always control when and where you die, but thanks to Sion's tankiness and CC, as well as the bonuses you get from the items you build, like extra CC and move speed from things like Randuin's, Rightous Glory, Frozen Mallet, and Zz'Rot Portal, you do have at least some control. Yes, as you said, the passive is most effective usually when your teammates provide assistance and CC, but remember that even though Sion has powerful damage and the ability to duel and overpower enemies, you are playing a tank and your main role in the game is to protect your carries and disrupt the enemy team. If you happen to die in the process your passive may not end up being flashy, but it will surprise you when used correctly. Take advantage of your positioning and work with/for your team. I promise you that if you try to learn a lesson from each, your actions during your passive as well as your opponents reactions, you will learn eventually how to get as much value out of it as possible, and any gripes you have with it will eventually turn into praises for its power.