February 7, 2023 at 1:19 pm
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.
February 7, 2023 at 1:31 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 7, 2023 at 1:44 pm
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
February 7, 2023 at 2:35 pm
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 will give you the shirt off *someone else's* back.
February 7, 2023 at 2:43 pm
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?
February 7, 2023 at 2:54 pm
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 will give you the shirt off *someone else's* back.
February 8, 2023 at 11:51 am
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
February 8, 2023 at 1:19 pm
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply