Whoa, busy thread today!
FWIW, my opinion on virus scanners is outdated. As I mentioned, I don't use any of them personally. Disclaimers aside, what I have always heard is that Norton corporate edition is some of the best AV software you can get... while Norton home edition is some of the worst. I have had similar circumstances with customers using the free Norton, where I installed AVG and discovered a small handful of viruses that Norton missed.
My experience with Avast is limited, but when I have seen it on customer machines, what I noticed is that it seems to ask you about things all the time. AVG just runs in the background, takes care of things, and leaves you alone unless it finds something. The only notices I remember from AVG were every year or two, I'd get a pop-up that a new version was available for download.
As for resource hogs -- I think ALL virus scanners are that way to some degree. When you're trying to scan every single file on your computer, its going to take time, and its going to make heavy use of your processor. That's why I generally set AV scanning for 3am - it doesn't affect most people (except addy!).
Now about AVG itself... It is still one of the better-rated virus scanners, and the home edition has always been free. As with many other programs, when you go to download, make sure you select the free edition without all the extra toolbar additions and such. A LOT of companies are packaging browser toolbars with their free software these days, and those toolbars are one of the big reasons your computers are getting infected. TOOLBARS ARE EVIL! I do know there are some other virus scanners that are better than AVG, however its been some time since I did any research to determine what was available for free, and what version operated smoothly without getting in your way.
Regarding Spybot Search&Destroy... yeah it can be a bit of a resource hog, but it does a very good job. As JohnHuff mentioned above, do yourself a favor and run Firefox, or chrome, or anything else beside Internet Exploder (typo intentional). If you absolutely must run IE, Spybot has an immunize screen which will plug a huge number of holes in IE and make it somewhat less likely to get infected. Keep in mind if you have any privacy concerns that IE will gladly share every bit of personal information that Windows knows about you to each and every website that requests it. This is their idea of providing a more user-friendly experience...