One of my favorite games that involve 'cover' is probably the Army of Two series. All the games seemed to utilize cover really well, and if you were turtled down behind cover, enemies weren't afraid to flank you.
Plus the Aggro system in AoT made the cover system even better in my eyes.
Funny you mentioned Max Payne 3 also. I just picked it up a few weeks ago. I only beat a few missions so far, but overall it's not too shabby!