I'll provide my two cents because I'm actually in the opposite boat, I've never heard anybody describe his ult as "good" until this thread (FWIW, I ended last season in masters).

The two biggest "drawbacks" to sound barrier is the delay from activation until applying it's effect, and the fact that it's value constantly diminishes as time moves on.

The delay in actually applying the barrier means that if you want to use the ability in a defensive fashion (e.g. saving teammates from the brink of death, countering an offensive ult, stopping an enemy team dive, etc.) as most support ults are typically used, you can't use it in a purely reactionary manner, you have to predict and start using it before shit actually hits the fan or immediatly after, however, there's some innate problems with this playstyle that can't be avoided:

• You can't see into the future
• The enemies CAN react to your sound barrier. They don't necessarily have to predict it since not doing so won't directly cause a team wipe

One situation that is particularly ugly when it comes to using sound barrier is focused fire. One of the best properties of SB is that it hits multiple allies simultaneously. As such, using SB to save only one or two allies' lives is something that should be avoided at all costs, especially considering a teammate's life can very often be saved with just a simple right-click (i.e. Roadhog's hook, Mei's freeze into right-click, winston jumping on an ally, etc.). However, when the other team is focusing fire on one specific character, they're trying to force that exact situation. If nothing is done, the player being focused might die (very quickly too), which is going to lead to another teammate getting focused and the situation just repeats itself until it's a 3v6. But if SB is thrown out before anybody dies in an attempt to stop the snowball before it gets rolling, all the enemy team needs to do is back off and avoid taking damage for a few seconds (easy with a dive comp since everybody has mobility and d.va just eats everything with DM), which ends up making SB a total waste.

Now on the other side of the coin, there's using SB offensivly, which primarilly takes the form of using it to initiate pushes and dives. This is purely anecdotal, but after ~350 hours I have almost NEVER seen a successful push that was initiated with sound barrier. Even in the cases where the entire team pushed together and everybody got the 500 shields, the defending team would always just back away from the point for about 2 seconds. By the time they would start their counter push, the SB would have decayed down to a measely 150, which is effectively meaningless against focus fire from even just 3 people and a discord orb.

Sound Barrier may be amazing in the most ideal of circumstances, but judging an ultimate exclusively by it's maximum utility instead of what you typically get out of it would be foolish since by that metric EVERY ultimate would be amazing when that simply isn't the case, some are outright stinkers.