When does a stored procedure recompile
I mucked around with this the better half of a day. I wanted to know if sql server was recompiling our sprocs.
These links explain it all (you can use Profiler to see this):
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243586