CODEWITHSUNDEEP
sql-serversql-server-2008stored-proceduressql-server-2008-r2
M.Ali
M.Ali

Reputation: 69534

Temp Tables in a Stored Procedure will it cause recompilation of execution plan

If I have Temp Tables being created in a stored procedure's definition and then dropping them when I am done with them will it result in recompilation of execution plan?

For stored procedures every time its called? Any personal Experience? Any explanation please?

As when the temp tables are dropped at the end of every call, the execution plan becomes invalid. Does SQL Server still keep hold of the execution plan and reuse on next call or does it recompile it every time its called.

Upvotes: 8

Views: 7752

Answers (3)

chaitanya.moguluri
chaitanya.moguluri

Reputation: 240

The temp tables can cause recompilation. It happens just because they are treated like the regular tables by the SQL Server Engine. When the tables (in which underlying queries rely on) change significantly, SQL Server detects this change(using auto update statistics) and marks the dependent queries to be recompiled so that the next execution can create an optimal execution plan.

Once the temp table or the queries relying on the temp table changes the Query engine will not be able to execute the same cached plan as it would not accommodate the query.

It should be noted that table variables inherently do not cause recompilation. In some situations these may be a better choice.

See http://sqlserverplanet.com/optimization/temp-table-recompiles for further information on temp table recompilation.

Upvotes: -3

Santiago Regojo
Santiago Regojo

Reputation: 405

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

If the dataset you have to put in the temporal table is small and you don't need non-unique indexes, you should try to use table variables instead. It's not a good idea to put too many rows in a table variable because they dont have statistics, Sql Server allways "thinks" they have only one record and the query plan could be a little bit far than optimal one(but it is going to avoid the recompilations due to temporal table creation).

Upvotes: -2

E.K.
E.K.

Reputation: 419

Dropping of a temporary table doesn't matter. If a table is created (either permanent or temporary), all statement after that statement are recompiled (even if they don’t refer to the table). Calls to executable objects using EXEC aren’t recompiled. That's because SQL Server can create the plan after the objects are created. (In this case, the temp. table.)

You can monitor recompilation using Extended Events and its sql_statement_recompile or SQL Trace / SQL Server Profiler SQL:StmtRecompile.

  1. A statement starts to execute. SP:StmtStarting or SQL:StmtStarting is raised
  2. The statement is recompiled. SQL:StmtRecompile is raised. SP:StmtStarting or SQL:StmtStarting is raised again
  3. The statement is finished. SP:StmtCompleted or SQL:StmtCompleted is raised

Not the whole procedure is recompiled but only individual statements.

Upvotes: 6

Related Questions