Its base damage is really low, though, so the S scaling is effectively more like B scaling. The thing can't be infused or buffed, either. It has an interesting weapon art, but I don't really put much stock in the buff ones as they cost a considerable amount of mana, can't really be applied mid-fight, and last like ten seconds. A better one-handed strength weapon is the Dark Sword. It's basically one of the game's top 10 weapons.
Butcher Knife is an okay weapon, but its actual damage doesn't surpass something like the Broadsword, and the knife has an awful 2h moveset as well as unfavorable stats -- starting base damage of 95 is less than the Shortsword, and it's the heaviest 1h axe by quite a margin. If you stick a heavy gem in the Broadsword, it has higher damage, a better moveset, and less than half the weight. Or just go with Dark Sword like everyone else.
If you just look at the damage or stamina costs, heavy weapons are garbage. Their usefulness is in their size and movesets. For instance, most heavy weapons with a ground-slamming attack will stagger everything in an area around the blow even if you didn't hit. You can interrupt enemies outside your reach with this. Also, you can get some serious range out of something like the Zweihander's rolling R1. The ability to reach like 9 feet with a melee weapon that has high damage (unlike spears which generally suck) is very useful.
I do think the mid-sized, faster weapons are ultimately the best ones in DS3, but the numbers make them look more favorable than they really are. Especially in PvE, ultra weapons can just hit shit before it can reach you, and while shield enemies are some of the most irritating to deal with if you're using a straight sword, an UGS will just destroy these as you can simply dance around outside their reach, wait until they respond by lowering their shield to do some lunge attack, and punish with a max-reach smack that staggers almost anything.
For my first playthrough, I looked at the damage numbers and concluded that there was no reason to use anything but a straight sword. Then for my second playthrough, I decided to try heavy weapons and was astonished to find that a lot of the content is way easier when you can one-shot mobs before they can even reach you. With a straight sword or other normal-sized weapon, you get punished really hard for any mistake as your opponent is right up in your face. While the attacks might cost less stamina, you have to spend a lot more blocking/rolling.
One of the ways big weapons truly shine is what's called "hyper-armor," which means you can't be interrupted during some portion of the attack animation. DS3 is otherwise really bad about every fucking attack staggering you. Poise appears to do literally nothing unless your weapon has hyper-armor frames, which only big weapons really have enough of to matter. This is so fucking useful. On my first playthrough, it was driving me absolutely insane how getting gently poked by a hollow with a broken stub of a sword for 3% of my HP would stagger my platemail-wearing knight, interrupting my attack and putting me into a stunlock if there's a few enemies. Then I tried an ultra and I could literally hear Johnny Nash singing "I can see clearly now." It was such a fucking relief and so much more enjoyable after having had to use my shield so much I got trigger finger on my left hand.
That said, some big weapons do just objectively suck and aren't useful for anything other than trying to get a high AR score. The plain Greataxe, for instance, hits hard but has no reach, has a moveset consisting entirely of vertical chops, and still weighs as much as a small car. Weapons like the Greatsword, Zweihander, Great Mace, Spiked Mace and Black Knight's Greataxe are fantastic, though, as long as you're able to keep enemies from surrounding you. Many of the fights that are rough with a lighter weapon are trivial when you can three-shot the fucker from seven feet away and every hit is a guaranteed stagger. It's so good against mobs such as the various knights and other chubbies.
The biggest problem with the ultra weapons is that half of them look so profoundly stupid that I can't live with it. The two I've found to have the best balance of effectiveness vs. acceptable appearance are Zweihander and Great Mace.
