Some social stigmas are good. They represent a reasonably accurate consensus opinion about the harmfulness of certain behaviors. Felonious harm.
Some social stigmas are neutral. They relate to behaviors that are marginally harmful but not urgently threatening. Their expression is mild enough that the infringement upon liberty is balanced out. Much of this behavior will consist of self harm, malicious mischief or public nuisance.
Some social stigmas are bad. They demonize behavior that is not harmful except in the sense that it provokes a reaction from those who hold the stigma. They often suppress natural and healthy human appetites. They waste resources that could be employed to mitigate real harm. I would cite things like the majority of recreational drug use, the sexual expression of consenting adults, most of what is considered explicit media content. Many other examples.
Each set has its exceptions of course but I think the broad categories stand.
All stigma is bad in the sense that it over generalizes, minimizes agency and frequently hits the wrong target. It’s unreliable as either a deterrent or motivator. It’s capricious and tends to be culturally relative. It’s often the first resort of relatively uneducated idealogues and angry mobs.
My intuition is that, despite its liabilities social stigma is good on balance. Peer shaming is necessary for our moral development. Collective standards and penalties are the correct lever for most interactions. We should only rely on institutions when ordinary community resources are failing dramatically. Combined with thoughtful, positive motivation they help us to get along.
But I could be wrong.