All the imperial guard tanks are amazing for their points value, the ork and chaos super heavy are okay for how much they cost, the tau super heavy is terrible, the tyranid and space marine ones are good, the eldar and dark eldar one is amazing, and the necron ones are generally average across the board, but just like a landraider they still die to melta guns. I want to stress that point, EVERY SINGLE SUPER HEAVY WILL DIE TO MELTA GUNS, on average taking about 6-9 melta shots to kill the ground based ones, and autocannons will glance all but the imperial guard tanks to death.
D-weapons are not as broken as people think. Sure, each weapon that has them will kill 1-2 units a turn, but considering how much these units cost, they need to kill that many to be cost effective.
The average non-deathstar unit is about 200pts, so that means for the most part every super heavy will need to kill a unit a turn, consistently, for two and a half turns to earn its points back. Thats a major accomplishment if one of those survives past round two against a smart player.
Another fun fact is your opponent gets an amazing warlord trait table if you use a super heavy, which has all sorts of options that make super heavies cry.
I believe with all my heart that tournaments need to use escalation, why? because many armies can get a 2+ re-rollable invuln unit in thier army, which is virtually unkillable with most armies, but d-weapons kill it without a sweat, sure we trade one evil for another, but superheavies can at least be killed, quite easily.
So in conclusion, D-weapons are overrated because the points cost of the model they are on is ridiculous, super heavies die to melta guns and being glanced to death,
tournaments should allow escalation, and they are generally all overpriced and not worth their points, this includes the revenent titan. The closest ones to being cost effective are the guard tanks, but even then would you rather have one baneblade, or a squad of leman russes?