At the moment I have to say Project Reality for Battlefield 2, it is great in my opinion it turns BF2 into a Operation Flashpoint game with more things to do. Saves BF2 from itself as I the original game feels very unrealistic.
I've also being playing Fistful of Frags on the source engine, it is not the next big thing but the Western theme, slower guns, and chase the gold all work really well and make it a lot of fun to play.
Historically the two that stick out for me are Counterstrike as it was a revelation to play in rounds rather than respawn, and Desert Combat which just added so much to Battlefield 1942. I also played Vampire Slayer when it first came out, the weapons were so silly.