Hi Team,
Our production Environment is SQL server 2000 with SP3. I noticed lot of compilation/sec (where recompilation/sec is closed to ZERO). I know every stored procedure will get compiled initially then it may get recompiled for lot many reasons & I have very good understanding of reasons for recompilation & lot of document on that. Where as I am trying to understand what causes for compilation.
I noticed we are not using DBO prefix while calling the stored procedure & also recommended using sp_executesql instead of exec. Is there anything else I need to check
Is there any document on what causes for compilation other than creating the stored procedure. Once again I am not seeing recompilations.
Any help will be really appreciated.
Thanks
DJ Pallerla

Compilation/sec is more on production where recompilation / sec is zero
Chappers23
Thanks Greg for those two articles. I have already read those I am looking for specific article on reasons for compilation.
Brian Noyes
Try these two articles, which you may or may not have already read.
http://support.microsoft.com/ id=263889
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx