I have used JetBrains dotTrace and Redgate ANTS extensively. They are fairly similar in features and price. They both offer useful performance profiling and quite basic memory profiling.
dotTrace integrates with Resharper, which is really convenient, as you can profile the performance of a unit test with one click from the IDE. However, dotTrace often seems to give spurious results (e.g. saying that a method took several years to run)
I prefer the way that ANTS presents the profiling results. It shows you the source code and to the left of each line tells you how long it took to run. dotTrace just has a tree view.
EQATEC profiler is quite basic and requires you to compile special instrumented versions of your assemblies which can then be run in the EQATEC profiler. It is, however, free.
Overall I prefer ANTS for performance profiling, although if you use Resharper then the integration of dotTrace is a killer feature and means it beats ANTS in usability.
The free Microsoft CLR Profiler (.Net framework 2.0 / .Net Framework 4.0) is all you need for .NET memory profiling.
2011 Update:
The Scitech memory profiler has quite a basic UI but lots of useful information, including some information on unmanaged memory which dotTrace and ANTS lack – you might find it useful if you are doing COM interop, but I have yet to find any profiler that makes COM memory issues easy to diagnose – you usually have to break out windbg.exe.
The ANTS profiler has come on in leaps and bounds in the last few years, and its memory profiler has some truly useful features which now pushed it ahead of dotTrace as a package in my estimation. I’m lucky enough to have licenses for both, but if you are going to buy one .Net profiler for both performance and memory, make it ANTS.