Geek Answers Handbook

Temp tables in the stored procedure will recompile the execution plan

If I have Temp Tables created in a stored procedure definition, then dropping them when I am done with them will lead to an recompilation of the execution plan?

For stored procedures every time you call it? Any personal experience? Any explanation please?

As and when temporary tables are discarded at the end of each call, the execution plan becomes invalid. Whether SQL Server supports the execution plan and reuse on the next call, or recompiles it every time it is called.

+7
sql-server sql-server-2008 stored-procedures sql-server-2008-r2
source share
3 answers

Deleting a temporary table does not matter. If a table is created (both permanent and temporary), all instructions after this statement are recompiled (even if they are not related to the table). Invoking executables using EXEC arent recompiled. This is because SQL Server can create a plan after creating the objects. (In this case, the temporary table.)

You can track the recompilation using advanced events and its sql_statement_recompile or SQL Trace / SQL Server Profiler SQL: StmtRecompile.

  • The application begins to be executed. SP: StmtStarting or SQL: StmtStarting raised
  • The statement is recompiled. SQL: StmtRecompile raised. SP: StmtStarting or SQL: StmtStarting rises again
  • Application completed. SP: StmtCompleted or SQL: StmtCompleted raised

Not the whole procedure is recompiled, but only individual statements.

+5
source share

Generally speaking, any DDL in your store procedure will result in recompilation, and then if you use the create and drop table instructions, you will get recompilation. It can be mitigated by including DDL as the first operator in the store procedure, but you must test it and see it with your own eyes on your server.

If the data set that you must enter in the temporary table is small and you do not need non-unique indexes, you should try to use table variables instead. You should not include too many rows in the table variable, because they do not have statistics, Sql Server always “thinks” that they have only one record, and the query plan may be slightly more optimal (but it is going to avoid recompilation due to creation temporary table).

-2
source share

Temporary tables may cause recompilation. This only happens because they are processed like regular tables using the SQL Server Engine. When the tables (which contain the main queries) have changed significantly, SQL Server detects this change (using automatic update statistics) and notes that the dependent queries are recompiled so that the next execution can create an optimal execution plan.

After the temp table or queries that depend on the temp table are changed, the query engine will not be able to execute the same caching plan because it will not consider the query.

It should be noted that table variables do not inherently cause recompilation. In some situations, this may be the best choice.

For more information on recompiling the temp table, see http://sqlserverplanet.com/optimization/temp-table-recompiles .

-3
source share

More articles:


All Articles