Has anyone had experience with the new criteria added to recompile stored procedure execution plans in SQL7/2000? I'm working on an App originally developed in SQL 6.5 with a specific process of using #temp tables to allow for maximum speed and minimal user concurrency issues. The process runs fine for SQL 6.5 sites but in SQL7.0/2000 it is exponentially slower. We're finding that every procedure is being recompiled every time it is run, it never re-uses a cached plan. It is most likely due to the use of #temp tables but the process is too complex to be re-written at this time. Are there any options short of recoding all procedures to use the 'KEEPFIXED PLAN' (Q276220) query hint on every SQL statement?? I've gone through Q243586 from Technet also and not found any painless way to restore the 6.5 behavior. The process was coded with the #temp tables created on the highest outer level procedure due to lack of deferred name resolution in SQL 6.5. This is now causing a problem because the nested procedures are referencing #temp tables that were created outside the procedure, a condition which seems to force a procedure recompile every time it runs and it kills the response time of this time-sensitive process. Any advice on preventing the continuous procedure recompiles would be appreciated! Thanks.
-Chris
-Chris
