To Drop or Not to Drop (the temp table question)

  • So here's a thing that I've been told several times over the past few weeks and I'd like to know if any of you have ever heard of this or have any links to this.

    Apparently if a primary key is added to a temp table, the SQL Engine keeps the table's meta data and the temp table doesn't need to be recreated. Nor does it need to be dropped. Because apparently creating / dropping temp tables in stored procedures take a lot of overhead in SQL Server.

    My thought is if the metadata is kept, what prevents the actual temp table data from being kept? And whatever happened to the idea that the temp table will drop anyway once the session is disconnected?

    If anyone can please point me to anything that would confirm or debunk this, I would appreciate it. I'm being pushed pretty hard to convert our developers to use this option going forward and I'd really like to know what it is I'm insisting they do before something breaks.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • To my knowledge, primary key or not, temp tables are cleaned up at the end of a connection. I'm not aware of circumstances that prevent this. Of course, a global temp table, slightly different rules. The last connection still using it, will cause a cleanup when it disconnects. However, to my mind, that's effectively the same.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • This is the link holding the part that causes your confusion:

    Performance improvements in tempdb for SQL Server

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution ūüėÄ

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I believe that SQL renames temp tables rather than dropping them, esp. for stored procs.¬† That way, when the proc runs again, it doesn't have to fully recreate the table just rename it back.¬† I believe SQL does truncate the table so that the data is gone; just the metadata for the table is kept.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

  • ScottPletcher wrote:

    I believe that SQL renames temp tables rather than dropping them, esp. for stored procs.  That way, when the proc runs again, it doesn't have to fully recreate the table just rename it back.  I believe SQL does truncate the table so that the data is gone; just the metadata for the table is kept.

    Do you have a reference for this renaming behavior? A book title? A website link?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Johan Bijnens wrote:

    This is the link holding the part that causes your confusion:

    Performance improvements in tempdb for SQL Server

    Thanks for the links. I will review.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin wrote:

    ScottPletcher wrote:

    I believe that SQL renames temp tables rather than dropping them, esp. for stored procs.  That way, when the proc runs again, it doesn't have to fully recreate the table just rename it back.  I believe SQL does truncate the table so that the data is gone; just the metadata for the table is kept.

    Do you have a reference for this renaming behavior? A book title? A website link?

    I do now :-).  I didn't initially remember exactly the article, but I figured it would be either Paul White or Itzik Ben-Gan.  Searched for it, it was Paul White.  Be sure to check the article for the restrictions to allow a temp table to be cached.  The italicizing was added by me, is not in the original article:

    https://sqlperformance.com/2017/05/sql-performance/sql-server-temporary-object-caching

    "

    For a cached temporary table, the first time the module is run, the temporary table is created just as for the non-cached case. At the end of the module, instead of being dropped automatically (as the scope in which it was created ends), the temporary table is truncated and then renamed to the hexadecimal representation of the object ID (exactly as seen for the table variable). The next time the module runs, the cached table is renamed from the hexadecimal format to the user-supplied name (plus underscores plus hex object id).

    The extra renaming operations at the start and end of the module involve a small number of system metadata changes. Cached temporary tables can therefore still experience at least some metadata contention under very high rates of reuse. Nevertheless, the metadata impact of a cached temporary table is much lower than for the non-cached case (creating and dropping the table each time).

    "

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

  • We can also run a code that shows that SQL Server caches the temporary table and doesn't drop it.¬† If you'll run the code bellow (pleas run it on a server that you are only using, so you'll see only data about the temporary table that you created), you'll be able to see that ojbect_id and create_date will be the same before and after the table was dropped in the code.¬† You'll also be able to see that the temporary table was created before the procedure ran for the second time

    create or alter proc DemoTempTables
    as
    declare @ProcStartedRunningAt DATETIME = GETDATE();

    create table #t(i int);
    --see the list of temp tables
    select @ProcStartedRunningAt AS ProcStartedRunningAt, object_id, create_date, modify_date, name from tempdb.sys.tables where name like '#%'

    drop table #t

    select @ProcStartedRunningAt AS ProcStartedRunningAt, object_id, create_date, modify_date, name from tempdb.sys.tables where name like '#%'
    go

    --See that object_id and create_date are the same all 4 relvent rows, but
    --modify_date and name are being modified each time
    exec DemoTempTables
    waitfor delay '00:00:01'
    exec DemoTempTables
    go

    --clean up
    DROP PROC DemoTempTables

     

    Adi

  • And finally other documentation has been supplied by the person who mentioned this to me:

    SQL Server Temporary Table Caching (mssqltips.com)

    Thank you everyone for your help. I appreciate it.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply