I've gotta join the Alyx-bashing waggon here, at the begining of episode 1 she is given infinate ammo and will only fire where you illuminate (some may say that makes her quite valuble but thats what I'm getting at), this made me think, and was further proved by listening to the dev commentaries, that they threw this feature in to get use out of her and to somehow make you attach to her (it just struck me as a bad excuse to leave you with nothing but the shoddy grav gun for the first section of the game). Throughout she is often involved in what Valve call 'gates' (pre-programmed barriers to keep the player from moving on until they've sat through that almightily important 5 minute dialogue) in Half Life 2 particularly I often connected her appearance with yet another unessercary pause in the already broken up action.
Now, I would say that a pretty good female support character would have to be Isabella from Dead Rising, okay, so she is initially hostile (name me a heroine from Devil May Cry who isn't) but:
1. In-game she can actually hold her own against zombies and her AI is refreshingly smarter than other survivors for some reason.
2. In terms of the plot she is more or less central, she even creates a zombie cure for you (sorry for the spoilers guys, not that there was much to spoil) so her importance is in fact more than just being a crowbarred in love interest who has terrible things happen to her.