On my old machine I used NOD32 because it's very light on resources, and middle of the road on effectiveness.
However, I swear by F-Secure. It offers very good protection against unknown threats and is fairly light on resource usage.
There's a magazine dedicated to AV called Virus Bulletin. They run exhaustive tests using off the shelf software vs. in-the-wild pests. Sophos is rated highest in the latest VB100 review I think and Kaspersky also got an award. F-Secure received an award earlier this year and has received them in the past.
When I settled on F-Secure a while back, it was probably the best AV around. But now they use a different core engine and other AVs are evolving as well, so it's very hard to say at any given time what the best AV is. It is however usually easy to decide on the worst: Norton and McAfee

historically the free ones were always crap too but maybe that's changed...