This seems to be hot topic right now among my peers here locally and even abroad ~ To Recompile or Not to Recompile that is the question at hand.

I’ve been associated with companies in the past where utilizing this option is almost a standard; then I’ve been with companies to where it was never used at all. What is my personal preference?  I don’t use the option all of the time but I do have some use for it in my daily checks of the systems I monitor.

My two real world examples I just ran into not long ago are some simple ones but thought I would share……..

• I kept on having some issues with a stored proc and I knew that what I was troubleshooting was off kilter and wasn’t adding up in my head on what I was seeing in some of the results. In diving into the query of course I saw the recompile option which of course told me that anything in my plan cache I should just throw out the window……I love using my DMV’s and what not; however on a recompile each time a proc is used puts a damper on my assessments at times. There is a trade off when using the recompile option to improve performance, just something to keep in mind.
• One aspect I do use the recompile option for is my diagnostic queries of my environments. Prime example: I’m a fan of Glenn Berry’s Diagnostic scripts he’s put together with some of my own customized ones. When running these, I personally, don’t care how they are stored in the plan cache etc.

I recently had the opportunity to sit in on a good session about this by Kendra Little over at Brent Ozar PLF ( good group of guys and if you haven’t ever checked them out I implore you to do so; they have some great items they discuss and free weekly training ). Kendra goes through some real world examples in her demo and it would behoove you to give it a look.

As I said earlier I’d love to hear some of your experiences with the option and the pros and cons you may have on it. If you have time drop me a line.