I review AV software for a major computer magazine. I have a test zoo with thousands of viruses, both current and old.
My current preferred AV product is ESET's NOD32, which will set you back about $35 a year. It is extremely lightweight, very fast, doesn't slow down AH to any significant degree, and it has a nice interface too. It is currently what I recommend to my paying clients.
Despite what is said here, I have no problem with Norton Antivirus 2008. It is also fast and lean, but not as much so as NOD32. I recommend avoiding things like Norton Internet Security, Norton 360, or anything other than the basic Norton AV 2008 package. Norton's Kernel Patch is symantec's attempt at helping detect and remove rootkits, and it actually does an effective job.
Kaspersky is also excellent, but I haven't tested it in more than a year.
For free products, Avria's Antivirus Free is pretty good too, though it has an annoying nag screen that you can disable by googling it. I'm sorry, it says it is free to use for noncommercial purposes - it doesn't say it is adware, which is what I consider the annoying popup to be.
AVG is only fair. I have personally seen two rootkits and one virus get by it when the user was still using IE6 in 2007, and one of them is someone I consider pretty good at technology. I consider it minimally acceptable if you only use Firefox or Opera to surf, but since Avira is also free, I say get it.
-Llama