Depends on the game, but in intense multiplayer tourneys, I find it can hurt you. I was big in F.E.A.R. along time ago, and was in tourney, and that game had lots of hiding spots with shadows and lights you could shoot out to darken the area to hide in, and first round I got toasted, thinking I was hiding in spots that the other player couldn't see. After that round, did some adjusting to my settings with shadows off, and adjusted the lighting and all those dark spots all of a sudden weren't dark anymore and had the same light as the rest of the room, so I knew why I was able to be seen.
I play Battlefield Play for free, and on Max detail, when someone throws a smoke grenade, you can't see anything. On low detail, there is no smoke and you can get mowed down.
For single player modes, I like everything turned up to get the full experience. For multiplayer, I tend to turn some things down, getting max FPS and distance viewing without stupid effect like smoke or flickering lights getting in my way, because as pretty as it can look, it can cost you in a multiplayer match and you have no idea what settings they are running on or have tweaked to have an advantage. That is the one benefit to console gaming multiplayer, you know everyone has the same settings turned on because they can't change them.