Brian has lots of sound clips of his various offerings linked in the exhaust section that should give you a pretty good idea of sound. You can also go over to Miata.net and do some searching and you'll find 20,000 threads on mufflers and which ones sound the best. The only thing you wont find over there is any consensus, since everyone has their own particular taste and tolerance for noise.
You will find that Brian's Roadstersport options are very popular over there, though. They're very high quality, and priced very reasonably compared to some of the other options out there. Compare something like a Borla muffler to the Q, and you'll be shocked at the difference. And the much cheaper Q is higher quality!
If you are really looking to maximize power, a completely cat-less setup will give you the biggest gains, but of course be illegal in all 50 states (and kind of smelly, too). There's just no way of getting around the fact that cats always add some small amount of restriction to the exhaust, and that putting one in the header requires the primaries to be shorter than ideal for max power. A longer-tube cat-less header with a catted mid-pipe is going to be the next best option when it comes to maximum power. The Roadstersport Max Power header has been the champ for making power in any back-to-back testing so far. The newest PPE long-tube header hasn't actually been tested on a 2.0L motor yet, but it should give the Max Power a pretty good run for its money. The downside is that the PPE header itself isn't stainless, even though it's ceramic coated, and it's quite a bit trickier to install. The Roadstersport catted header is also a big improvement over the OEM one, but will give up a little bit of power vs. the longer-primary catless options. But it's easier to install, and more likely to get you past the smog police.
As far as mufflers go, most of the difference is in sound. They all flow better than the OEM one, and will make a little more power. Straight through mufflers like the Roadstersport Race Single and Street Single have the lowest restriction, will make the most power, and will be the loudest. Dual outlet mufflers like the Roadstersport II, Q, and Super-Q may give up 1 or 2 hp vs. the straight-through options, but they're significantly quieter.
If you have the budget available, and you're definitely changing out the header, then a tune is where you will really see the nice power gains. Headers increase the exhaust flow enough that the factory tune doesn't have enough self-adjustment range to maximize their effect. You will be perfectly safe running the factory tune with an aftermarket header and will see some nice gains, but there's even more power available just waiting to be un-locked with a more aggressive tune.