Depends on the game, and on some games what I'm doing in them.
MMOs in general make me sleepy. Especially when I used to play Ragnarok, and when I used to do tedious raiding on WoW (I remember applying for a top guild and fell asleep during the raid). Occasionally on these games I'd be in an alert and very organized mood, usually multi-tasking quests on WoW or planning builds for Ragnarok.
Counter-strike Source gives me a huge adrenaline rush, especially when I'm on a winning streak. My constant disconnects from the internet (which seem to be triggered by games by the increase in occurance), and slight lag spikes whenever I turn a corner to shoot someone raise my stress levels A LOT. Despite the anoyances, it's a really fun game to me.
Halo makes me happy, especially with mates. I do better than my mates and usually do really well regardless. You win some you lose some, so I don't really get angry when a good player comes along. I love the range of things you can do in terms of combat and you never know how it'll play out.