Permanently Caching stored procedures execution plan in memory

[13415]

Hello
We have a weekely batch job which transfer date from SQL server 7.0 to Oracle datbase. This consist of 15 stored procdedures and one master procedure which calls each procedure one-by-one.
We want to pin the execution plan of all these stored procedure to into SQL server memory , so that it cann't be aged out.
Is there any way to do this.
Please help

 

[tlbroadbent]

Why do you want to do that? By permanently, do you mean even after the execution has finished? What do anticipate you will gain from doing that even if it were possible or advisable? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[13415]

Thanks for responding. By permanently caching we can expect performance gain. execution plan will not be generated. it will save time.

thanks

 

[tlbroadbent]

You're "barking up the wrong tree" as we say. Keeping a procedure execution plan in memory will not improve performance that much. Execution plans are usually created in milliseconds, at the most a few seconds. Loading the data probably runs several minutes or hours. You should focus on improving the performance of your SPs and quit worrying about saving milliseconds of compilation time.

Which version of SQL Server are you running? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.