Fantastic article Brian.
I noticed with a proc that I'm running, that it recompiles the proc based on the parameters that are being passed to it. So if you run the proc with param A, it does not recompile for params B, C and D. But the minute you run it with param E it initiates a recompile. And the funny thing is that when you exec the proc again with param A it initiates a recompile once more. I analyzed the SP statements in Profiler and noticed that the one that causes the recompile had a couple more temp tables that it was using based on the data.
So the first thing I did was to bring all the DDL script to the top of the proc. This seemed to result in fixing the problem, the speed improved from 90 secs to 50 secs on the first run, and then using the cached query plan to 2-5 secs depending on rows returned and using param E did not result in a recompile. But only for a while. All of a sudden, a couple of hours later, I noticed it went back to its old behaviour. One thing I noticed was that one of the other DBAs had changed the number of processors that was being used by SQL Server from 4 to 3. Resetting this did not change anything. Is there anything that could have been reset at a database level that might have started the problems with this proc again??? Could something have changed with tempdb or any of the dboptions?
Thanks for any suggestions