Yep. He sucks late game. That infinitely stacking health? Survivability is for chumps, AMIRITE? 20% armor shred? Your ADC doesn't need that. Two AoE knockups? Oh pls Yas, try harder. 1500 range AoE slow? Not useful for catching enemies at all. AoE % Health bomb? NOPE. Gigantic character model? Naw man, I'm gonna let Jinx take that Blitz grab to the face, who needs her and her silly rockets.
Sion is a late game God. There are few tanks who match Sion's poke, AoE damage, AoE disruption, engagement and re-engagement potential. Not only that, but you can absolutely SHRED their team if they actually manage to take you down, each AA during Glory in Death is fixed at 10% max HP. Your ultimate is on such a low cooldown, sometimes you can even use it twice in one battle. Considering how important mobility is late game, that ultimate is a godsend to any team trying to lock-down objectives after a teamfight.
You must think of Sion as a ranged mage tank, not a melee brawler. He is a back-line bombardier until it's time to bring his disruption to the front-line. Want to win with our favorite undead brute? You need to take Hybrid Penetration runes, use 21 points into the offensive mastery tree, buy Sorcerer's Shoes and Liandry's Torment. Stacking your W passive and building tanky resistance items on that foundation will allow you deal good damage and survive heaps of abuse well into late game. Additionally, This allows him to protect your carries while also providing substantial poke. Don't underestimate the damage a minion-tossed E will do with a magnified Liandry's proc, even lategame.
The biggest caveat to Sion is that his skill shots will make or break him: he needs help landing hard CC and he needs a mobile assassin or long range mage able to follow-up on slippery targets.