Process Piglet is a simple tool which monitors the memory use of running processes. New programs are tracked for 30 seconds to access their average requirements, and you'll then be alerted if that application starts to consume significantly more RAM.
At a minimum, you can just launch the program and use your PC as normal. Process Piglet adds an icon to your system tray and starts watching memory usage patterns. If there's a major change then a warning dialog appears naming the guilty process, and giving you a few stats ("process IEXPLORE.EXE has increased its memory use from 86mb to 316mb"). You also get a link which will try to forcibly restart the process, perhaps useful if there's a memory leak.
As a bonus, Process Piglet also displays a list of the most RAM-hungry processes. Right-click one of these and you're able to close or restart it, search for the process online, or open its folder for further investigations.
One problem here is that some programs regularly see a large increase in RAM use (a media player opening an HD video), and these can generate false alarms. You can reduce the chance of this by setting a "minimum RAM use" figure for alerts, for example telling Process Piglet to ignore processes using less than 100MB. Alternatively, adding a program to the Exclusion list means it won't be monitored at all.
Process Piglet also allows you to turn off the alerts entirely, and have any memory issues recorded in its logs. This could be the best option for many people, as it means there won't be any unnecessary warnings, but you'll still get a list of RAM-hogging programs to review later.