Compilation/sec is more on production where recompilation / sec is zero

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

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

|||

Thanks Greg for those two articles. I have already read those I am looking for specific article on reasons for compilation.