locked
When do #temp and @temp tables cause recompiles? RRS feed

  • Question

  • In olden days any temp table creation would cause a recompile of the entire SP.

    However, I found that was already not the case even as early as SQL 2000, and I'm wondering what is the case in SQL 2008 and later.

    Can anyone point me at a fairly current discussion of this?

    My situation is that I have some existing and rather long (1000-3000 lines) SPs that use a lot of temp tables.  They *are* doing a lot of recompiles.  This is unfortunate because this is on an OLTP-ish system, and we want response times as fast as possible, and by my estimate the recompiles alone run about one second.  I'm not clear exactly what the current rules are.  Would using @temp tables avoid all recompilations (at some other semantic costs)?  I know they avoid statistics-triggered recompilations.

    Thanks.

    Josh

    ps - for extra credit, does anyone have a way of estimating all the time the instance is spending on compilations?  perfmon offers counts, but not time.  I can calculate it from a full profiler trace, but it seems to require both compilation and statement start/end times to compute and that kind of trace is very expensive.  We can get it in SSMS with "set statistics time on" and adding up all the messages, but I'd like to get it monitoring the production box.


    • Edited by JRStern Tuesday, March 19, 2013 5:23 PM
    Tuesday, March 19, 2013 5:20 PM

Answers

  • Hello again Josh!

    Well, i'm trying to understand better what you want and i found some interesting links about the topic:

    http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx

    http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/18/11-0-temporary-tables-table-variables-and-recompiles.aspx

    I hope this help!

    Regards,


    André CR

    • Marked as answer by JRStern Wednesday, March 20, 2013 6:12 PM
    Wednesday, March 20, 2013 1:12 PM

All replies

  • Hello Josh!

    I found some helpful information for this case:

    "...

    Recompilation threshold is calculated as follows for temporary tables: n is the cardinality of the temporary table when the query plan is compiled.

     If n < 6, Recompilation threshold = 6.

     If 6 <= n <= 500, Recompilation threshold = 500.

     If n > 500, Recompilation threshold = 500 + 0.20 * n.

     For table variables recompilation thresholds do not exist. Therefore, recompilations do not happen because of changes in cardinalities of table variables.

    ..."

    I extract this text from the link below:

    http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/18/11-0-temporary-tables-table-variables-and-recompiles.aspx

    Regards,

    André CR

    http://sqlmagu.blogspot.com.br/

    Tuesday, March 19, 2013 6:01 PM
  • I know about the stats recompiles but there are also DDL/temp table change recompiles that I need some info on.

    I guess it's likely that @table variables never cause recompiles?

    But I'd still like to see the rules on when #temps do and don't cause recompiles just by creating and dropping #tables.  In olden days any create or drop was thought to cause recompiles, and even since SQL2005 it seemed likely that at least the statements that reference them, would need recompiles.  But the recompile events don't fire, but maybe I'm missing some compile (rather than re-compile) events?  Or if the creates and drops are NOT inside of conditional code, then they don't ever cause recompiles (unless the entire SP recompiles)?  Stuff like that.

    Thanks.

    Josh

    Wednesday, March 20, 2013 12:25 AM
  • Hello again Josh!

    Well, i'm trying to understand better what you want and i found some interesting links about the topic:

    http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx

    http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/18/11-0-temporary-tables-table-variables-and-recompiles.aspx

    I hope this help!

    Regards,


    André CR

    • Marked as answer by JRStern Wednesday, March 20, 2013 6:12 PM
    Wednesday, March 20, 2013 1:12 PM
  • http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx

    http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/18/11-0-temporary-tables-table-variables-and-recompiles.aspx

    Andre,

    Thanks, that Paul White article helps me understand - if not fix my actual problem.

    But his discussion of the caching of #temp tables so that their plans too can be cached, is probably all I can ask.

    (what it mostly seems to do is suggest we do more option (recompile) basically un-caching the plan, and he informs us we may also need to do our own explicit update statistics to even get that to work as intuitively expected.  ugh.  still, if that's what it is, we need to do it, or at least understand it.)

    Josh

    Wednesday, March 20, 2013 6:14 PM
  • No problems!!! I'm glad that it helped you!

    Regards


    André CR

    Wednesday, March 20, 2013 6:19 PM