存储过程的编译问题

Question

大家都说存储过程可以预编译，但如果是 存储过程执行的是动态SQL，那还有预编译的优势么，是不是每次都要重新编译啊？而且如果存储过程包括创建临时表，那是不是每次也要重新编译

Answer 1

存储过程和执行计划
在 SQL Server 6.5 版及更早的版本中，存储过程是对执行计划进行部分预编译的方法。在创建存储过程的同时，系统表内也存储了一个部分编译好的执行计划。执行存储过程比执行 SQL 语句更有效，因为 SQL Server 不必从头到尾编译执行计划，而只须优化该过程所存储的计划。同时，在 SQL Server 过程高速缓存中保留了完全编译好的存储过程执行计划，这意味着后面执行的存储过程可以使用预先编译好的执行计划。SQL Server 2000 和 SQL Server 7.0 版在语句处理上做了许多修改，将存储过程的许多性能优点扩展到所有 SQL 语句。SQL Server 2000 和 SQL Server 7.0 在创建存储过程时不保存部分编译的计划。与任何其它的 Transact-SQL 语句一样，存储过程也在执行时进行编译。SQL Server 2000 和 SQL Server 7.0 在过程高速缓存内保留所有 SQL 语句的执行计划，而不只是存储过程的执行计划。数据库引擎使用一种高效的算法，将新的 Transact-SQL 语句与现有执行计划的 Transact-SQL 语句进行比较。如果数据库引擎确定新的 Transact-SQL 语句与现有执行计划的 Transact-SQL 语句相匹配，就重新使用这个计划。这样就将执行计划的重复使用性扩展到了所有 SQL 语句，从而减少了预编译存储过程的相对性能优势。SQL Server 2000 和 SQL Server 7.0 版提供了新的处理 SQL 语句的可选方法。有关更多信息，请参见查询处理器构架。临时存储过程
SQL Server 2000 还支持临时存储过程，这些过程与临时表一样，在连接断开时自动被除去。临时存储过程存储在 tempdb 内，它们在连接到 SQL Server 以前的版本时很有用。如果应用程序生成需要多次执行的动态 Transact-SQL 语句，就可以使用临时存储过程。无须每次重新编译 Transact-SQL 语句，而可以创建临时存储过程，在第一次执行时编译该过程，然后多次执行预先编译好的计划。不过，大量使用临时存储过程会导致在 tempdb 内争夺系统表。SQL Server 2000 和 SQL Server 7.0 的两个功能消除了使用临时存储过程的需要： 可重新使用前面 SQL 语句的执行计划。该功能与新的系统存储过程 sp_executesql 结合使用时尤为强大。
本来就支持用于 OLE DB 和 ODBC 的准备/执行模型，无须使用任何存储过程。 
有关临时存储过程的其它用法的更多信息，请参见执行计划的高速缓存和重新使用。 

Answer 2

存储过程中使用动态语句，不会导致过程的重编译。查询优化器会在过程的执行计划外生成动态语句的执行计划，供过程调用。
存储过程中单单创建临时表，也不会导致重编译；只有在过程中间填充/修改临时表，可能会导致重编译（当然有一定的限定，可以参考联机丛书）。lz 可以自己做实验来验证。
-- 清空过程缓存
dbcc freeproccache;
-- 查看过程缓存，usecounts 为缓存的执行计划被使用的次数
SELECT cacheobjtype, objtype, usecounts, [sql] FROM sys.syscacheobjects;