It's an interesting question, and i think one angle on it that doesn't get talked about is relative scale of story and setting.
If you take the original three Star Wars movies, the story in those pretty much encompasses the setting. It's Light vs. Dark for the fate of everything we have met on screen, and it comes to a climax that defines the fate of that story's universe. (One of the things that doesn't quite work for me about the Star Wars expanded universe is that if your fundamental story template is "small band of heroic rebels taking on overwhelmingly larger evil and defeating it", having to keep saying either "oops, the evil wasn't defeated after all" ot "oh look, here's a new and even bigger evil" gets a mite silly.)
On the other hand, in the DF, at least up as afar as Changes, Harry is a fairly small player in a big complex setting, dealing with different threats, but on a scale where there is always clearly a much bigger picture beyond what he's aware of and engaging with. His story may for all we know be headed for a Star-Wars scale of saving the entire setting, but it hasn't got there yet, and thus far the DV has always had new and interesting things to throw at him that don't feel contrived.
The more setting you establish from the get-go beyond your protagonist's horizon, the more room you have to draw new complications from later on. The more significant what your protagonist does is to the fate of the universe, the harder, IMO, it is to go on from there unless you want to change the shape of the story entirely. (One of the things I like about how the DF avoids power bloat is changing the scale of the challenges; in SK and DM both, Harry is averting worst-case catastrophes that could bring down civilisation and might exterminate humanity; rather than try to escalate from that BR gives him a much smaller-scale but more personal and emotionally weighted conflict. Likewise going from the celluloidy action-adventure of the end of DB to the challenge of personal maturity and taking responsibility for an apprentice Harry engages with in PG.)
As for the pacing of how much your protagonist wins or loses during the story (I do not think in terms of heroes, all that much; unreservedly sympathetic characters are dull, and a conflict is much more interesting to me if there are elements on both sides to sympathise with) what I personally find most compelling is the incidental victories along the way that shortly turn out to not actually be victories; because the hero has, for example, triumphantly defeated the troll king, and only then finds out that the troll king had ancient treasures in his vault that his dark wizard advisor knows how to use to endanger the world, but had not actually been able to use while the troll king was alive because the troll king was in the way.
Also, establish your characters as smart and competent from the get-go. Use what knowledge you already have; if you're a martial artist, you can probably write martial arts better than most people. if you're a programmer, you can probably get the computer details right. Harry being smug about his ability to finally cast a decent veil works because at that point we've had ten books of him gradually getting better at magic in general, and ten years in which he has put lots of time and effort into learning his craft and we've seen a goodly chunk of the process. All of that sort of build-up is there to earn your Crowning Moments of Awesome; without it they just fail into deus ex machina.
The thing about authors who explicitly think in terms of making everything worse for the hero is that the longer that goes on, the narrower the space between continuing to plausibly challenge the hero and not breaking them irrevocably becomes, and there are only so many times a hero can slog through 99% Of What It Would Take To Break Me That I Just Barely Survive before it starts to feel contrived.
Oh, and giving your protagonist Phenomenal Cosmic Power can work - I'd point at Mike Carey's Lucifer as a very good example of a character who is the second most powerful being in existence getting 75 issues of impressively tense adventures. It's just really tricky.
