Turning on DEP for all programs is a good idea. I've never had a problem with it, stuff that borks up with it must be really rare, but common enough for it not to be on by default.
Some other good ideas are use EMET to turn on additional exploit protection for critical applications (web browser, PDF viewer, media player, office, etc.) and enable SEHOP system wide, it's at: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c6f0a6ee-05ac-4eb6-acd0-362559fd2f04 - I also have a link in my sig that has some tips on securing your system that I put together, if you want to take a look. (assuming you are running Vista or Win 7).
I turn it on for all programs, but I have run into a few that won't work with it on. IIRC, one of them was Out of the Park Baseball 10. If there's an issue you would get an error message related to "BEX," so make an exception for DEP if that comes up.
Left 4 Dead 2 was very crashy for me on my previous rig (haven't bothered to try it on my new rig). One of the commonly suggested solutions was to exclude it from DEP. I think that helped, but it was hard to tell because it still crashed sometimes.