Superior Firepower requires patience and complete knowledge on every map that you play on. If you do not know your way around the maps of Doom II, I recommend that you play through on the lowest difficulty first so you can discover all of the secret areas and weapon caches.
Your best friend for this achievement is Doom II's save feature, which allows you to save at any time. I suggest you do this often. Every time you pass a difficult part, or gain full health, save your game. Even if you smell that your about to walk into a trap, save your game. It saves you much frustration of having to start the whole level over again from the beginning, with way less ammo.
Let monsters come to you, most of the time they are hiding in close quarters. Fight them on your terms, not theirs.
Always remember that there is no shame in running away to regroup. Always remember where the health packs are that you might pass so you can run to them in a time of need, they can save you from a painful death.
On Ultra-Violence difficulty, every monster is deadly in their own right. You will need every pick-up you can find to make it through this, so make sure you find everything.
Finally, here is a guide (not made by me, I don't take credit for it) that will help you find the secret areas in the game. (Does not include the "No Rest for the Living" campaign.)http://www.gamers.org/games/doom2/secrets/
Hopefully my solution helps people unlock this achievement. Good luck!