13 March,2013 by Jack Vamvas
Place CREATE TEMP TABLE , DECLARE TABLE variables and ALTER TABLE data definition lamguage (DDL) statements at the top of any stored procedure or function. The causes the stored procedure to recompile in one batch.
Avoid placing these statements in multiple places , as a recompile is forced on each occasion. Keep it to one recompilation by placing the CREATE TEMP TABLE , DECLARE TABLE , ALTER TABLE statements at the stored procedure beginning.
Excessive recompilations are CPU cycle expensive. Therefore minimizing recompilations will assist in maintaining SQL Server performance. Read more on recompilation troubleshooting SQL Server - How to improve Execution Plan Reuse - SQL Server DBA
Use these two event classes to trace and identify recompilation at a stored procedure and stored procedure level: