I run it occasionally to clean stuff out and I would reccomend it to others, especially if you are having random popup ads or your computer is behaving a little more strange lately.
Lots of the free software you download comes with extra programs that help the software author get a little money even though he is giving his software away. These install along with the main program and run in the background, usually collecting statistics on what sites you visit or popping up ads occasionally. These extra programs are labeled spyware because of their stealthy nature (if you are notified at all it is usually at the bottom of the legal agreement you ignored when you installed the software) and their tendancy to send information to companies about your web surfing habits.
Spybot will remove these, along with common advertising cookies and maybe a few other things. Some of this spyware tightly integrates with Windows networking components and removing it can cause problems, while I’ve never encountered a problem running Spybot or the like I know some have.
A related program is Ad Aware. While these products mostly overlap in their capabilities, it is best to run both to be complete.
Both Spybot and AdAware are updated regularaly, like your antivirus program. You’d do well to keep up to date with these as well.