I know this is a few days old but I wanted to bump this to add my two cents. As far as the "best" years for the Northstar, IMHO, anything after the 2000 design change is superior to pre 2000's in every area except possibly the exhaust port and valve size. Even taking this into consideration, GM was still able to get 300HP out of the L37 with lower compression and smaller valve size. Both transverse engines have significantly smaller cams than 93-99 engines helping emissions and fuel economy. In fact the L37 cam is darn near the size of the 93-99 LD8 cam and makes 25 HP more. That's efficiency ! I imagine the post 2000 L37 has a torque curve similar to the old LD8 making it a stronger motor that is more "streetable" than the old L37 and the LD8 is definitely stronger in the lower RPMs than it used to be. For individuals also looking for more power, the larger exhaust valve from and LH2 and a compression bump back to 10.5:1 will increase power by 20-25HP minimum.
If you are looking for performance reasons, the overall "best" (my) vote goes for 2004-2005 and with 2006 and up getting honorable mention but specs and design changes are hard to find. I like post 2004 because the thread pitch on the head bolts was changed and virtually eliminates head gasket issues.
In answer to your thoughts on head bolt failure, it should be noted that almost all head gasket failures can be traced to the customer rather than a design flaw. Failing to use premium fuel in the early versions, changing oil when the stupid oil monitor tells you ( instead of at the proper mileage interval ), and put-putting around all contributed to scary combustion chamber events with damaging cylinder pressure. ( that pressure has to go somewhere if the valves aren't open yet, head gasket is the path of least resistance). Coolant also breaks down over time and needs to be maintained as well. Regardless, consumers fail to take responsibillity for their part and Cadillacs are expensive cars so GM had to bite the bullet on warranties. Out of warranty repairs were expensive so the engine got a bad rap publicly. I currently have 150K original miles on my 05 LD8 with zero problems.
Since the P07 PCM (04-05) is now tunable ( Madtuner.com), it is possible to do DIY mods and have it tuned properly. 2006 and up, and all LH2 engines, have the excellent E67 PCM which has been tunable for years. Virtual all mods would have to be DIY.
Headers are cake for any serious hotrodder , however, the crossover pipe remains the limiting factor for exhaust related power increases. The diameter of the pipe ( it's a LOT smaller than it looks when you pull the heat shield off ) is too small for a 300HP engine. This means a fair amount of power was left on the table, regardless of popular mythology that the factory combination was "maxed out " . Like many before me, I am trying to figure out some form of DIY solution but their really isn't much wiggle room. Running the maximum size pipe that will fit between the block and the transaxle, combined with high tech coating and modern ( and very thin) heat shielding materials like the turbo tuners use, would allow the pipe to make contact with the block and transaxle without fear of unsafe heat transfer. Technology has come a long way since 2000 and light years ahead of 1993 when this pipe was originally conceived.
Reground cams are certainly a reality now, with modern CNC machines and heat treating processes, there should be no worries about early lobe failure. Now if I could just get one of the companies on the internet that advertise the service to respond. ( Please don't reccommed a popular Northstar performance company, they don't "like" the 2000 up engine, don't seem to understand the difference between a lifter and a hydraulic lash adjuster and quoted me almost double what their website lists for this service. They also offer head porting but can't seem to provide before and after flow numbers which makes it hard to drop a large amount of cash for an unknown. If they want to work with ancient technology that leaves a minimum of 25 HP on the table just from factory efficiency upgrades, that's their prerogative.)
That's it, that's my two cents, hope it helps.