If the PC has a dual core CPU and plenty of RAM (1GB+ for XP or 2GB+ for 7), Microsoft Security Essentials seems to perform quite well. However, from my experience, it does run a bit sluggish on Netbooks and older PCs, especially if they lack on RAM.
As far as virus detection goes, no virus protection package is 100% effective and unfortunately I've seen many machines infected even with Microsoft Security Essentials running and completely unaware that there is a problem. Between this and its RAM usage, I seldom recommend this free AV package.
My favourite free AV at the moment is Avast free edition and it seems to be pretty effective. Since it launched version 5, it is also the lightest package I'm aware of and will comfortably run on Windows XP with 512MB RAM or Windows 7 with 1GB RAM. It also doesn't seem to hog the CPU and was the fastest out of 20 AVs tested going by AV Comparitives (news story):
avast! clocked in with the fastest scanning speed out of all 20 tested programs. Its throughput rate was 30% faster than Symantec/Norton or AVG. avast! was nearly three times faster than Microsoft Security Essentials, which had the second slowest scanning speed of all tested programs.
I've Avast free running in Windows XP mode and the following gives an idea of the RAM usage:
I've Microsoft Security Essentials running in another Windows XP Virtual PC. Notice the RAM usage:
The above screenshots were taken after idling ~5 minutes after a fresh boot with automatic updates disabled.