September 7, 2011 at 6:43 am
Hi All,
I know that there are two types of temp table local and Global ( singe and double # ) , and I know that local temp table is session based and the other one is session based but available across the server.
What I would like to know is , if I create a temp table and inserts some data, and if I log out from the session WITHOUT dropping the temp table, will the space used by the temp table get released? Or do I need to explicitly drop that table to get the space released ( I mean that space in temp database is released? )
Also is how can we check the space used by a temp table in temp db?
Thanks & Regards,
MC
September 7, 2011 at 6:54 am
only4mithunc (9/7/2011)
Hi All,I know that there are two types of temp table local and Global ( singe and double # ) , and I know that local temp table is session based and the other one is session based but available across the server.
What I would like to know is , if I create a temp table and inserts some data, and if I log out from the session WITHOUT dropping the temp table, will the space used by the temp table get released? Or do I need to explicitly drop that table to get the space released ( I mean that space in temp database is released? )
The table will be dropped if you close the session.
Also is how can we check the space used by a temp table in temp db?
Create Table #Table1 (Col1 int)
Insert into #Table1 Select 1
Go 1000
Use tempdb
sp_spaceused '#Table1_____________________________________________________________________________________________________________00000000011B'
September 8, 2011 at 6:58 am
Hi Thanks for the reply.
I agree that the table will be dropped when I close the session, but then the space used also will be released?. And if that is the case what are the other things which uses the space in Temp DB? Is it the SORT,and Group BY? Why I'm asking this question is sometimes I get the Temp DB Full Error.
So what I would like to know is if the space used by the temp table is getting released when I close the session, then in that case what else is using the space in temp DB and which is causing the error?
Thanks & Regards,
MC
September 8, 2011 at 8:26 am
It's possible there is a delay in the space getting released from inside the table. Is this table the size of your tempdb database?
Tempdb needs what it needs. Any large query (including SELECTs) can use tempdb space for worktables, sorting, etc. Tempdb is in use by much more than global or local temp tables. If you get the full message, you haven't allocated enough tempdb space for your workload.
September 9, 2011 at 7:59 am
Thanks for the reply.
So both of the above reply together answers my question thanks friends.
I would like to clarify one more thing, as the temp table is getting dropped once the session is closed , then there is no point in explicitly writing DROP TABLE #table at the start or end of a stored procedure right? Is that right? Or writing this drop statement has some advantage?
Thanks & Regards,
MC
September 9, 2011 at 8:03 am
In theory there's no difference in writing it or not.
In pratice, in my own little world, it makes the code look cleaner.
Not diff perf wise.
September 10, 2011 at 9:20 am
Ok... Thank you all, for your reply.
Thanks & Regards,
MC
September 10, 2011 at 3:05 pm
Hi
Call me the devils advocate but the temp table will not be dropped when the session becomes closed. It becomes dropped when the connection becomes closed or the session becomes reset.
I don't know what environment you are talking about, but this can be a big difference. If you talk about SSMS then both become closed when the current query window becomes closed, though, things are different if you are talking about client applications that use a data provider like ADO.NET that works with connection pooling. In ADO.NET the connection will stay open, even if the session becomes closed and it will be reset when the next pooled connection becomes created.
I wrote a little blog post about this a few weeks ago:
ADO.NET Connection Pooling, Internals and Issues
As Ninja's_RGR'us said, best solution is always drop temp tables explicitly.
Greets
Flo
September 10, 2011 at 4:47 pm
only4mithunc (9/7/2011)
Also is how can we check the space used by a temp table in temp db?
You can check the space used by the session that created the tempdb by querying sys.dm_db_session_space_usage for that session id.
September 11, 2011 at 6:12 am
Thanks friends.
The scenario is I have stored procedures which are used by SSRS reports and inside the SPs we have so many temp tables.
So when the report runs it calls the SP and inside the SP the temp tables are created by CREATE TABLE #table. So in this scenario will the DROP TABLE statement make any difference?
As the per all the above replies now my understanding is that the tables will be created while running the report and once that window is closed the tables will be dropped by its own and the space used will be released.
Friends is that correct?
Thanks & Regards,
MC
September 11, 2011 at 6:50 am
Dropping the temp table explicitly at the end of a batch is considered good programming practice. On the other hand, I have seen it mentioned that NOT dropping it explicitly and letting SQL server do the cleanup instead, takes advantage of the temp-table caching feature: SQL caches the metadata structure to re-use it next time the temp table gets created by a different session. This provides a performance boost, especially when lots of temp tables get created and dropped.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 11, 2011 at 10:59 am
Hi
only4mithunc (9/11/2011)
The scenario is I have stored procedures ...
In this scenario it is a bit different. I thought you create the temp tables directly from SSMS or a client application. Temp tables exist in the scope which creates them and die when the scope is left. A temp table that is created inside of a procedure will be dropped when the scope of the procedure ends, but will be available for all internally called sub-procedures. A temp table that is created in your session, by a SQL script, will be alive and accessible for all procedures called in this session until the session ends.
Here's a little example:
USE tempdb;
GO
-- ==============================================
-- proc that creates a temp table #ProcTemp
IF (OBJECT_ID('CreateTempTable') IS NOT NULL)
DROP PROCEDURE CreateTempTable;
GO
CREATE PROCEDURE CreateTempTable
AS
CREATE TABLE #ProcTemp (Id INT);
PRINT 'CreateTempTable: #ProcTemp ' + ISNULL(CAST(OBJECT_ID('tempdb..#ProcTemp') AS VARCHAR(10)), 'NULL');
GO
-- ==============================================
-- proc that creates a temp table #ChildTemp. Called by ParentProc
IF (OBJECT_ID('ChildProc') IS NOT NULL)
DROP PROCEDURE ChildProc;
GO
CREATE PROCEDURE ChildProc
AS
PRINT 'ParentProc: #ParentTemp ' + ISNULL(CAST(OBJECT_ID('tempdb..#ParentTemp') AS VARCHAR(10)), 'NULL');
CREATE TABLE #ChildTemp (Id INT);
PRINT 'ChildProc: #ChildTemp ' + ISNULL(CAST(OBJECT_ID('tempdb..#ChildTemp') AS VARCHAR(10)), 'NULL');
GO
-- ==============================================
-- proc that creates a temp table #ParentTemp. Calls ChildProc
IF (OBJECT_ID('ParentProc') IS NOT NULL)
DROP PROCEDURE ParentProc;
GO
CREATE PROCEDURE ParentProc
AS
CREATE TABLE #ParentTemp (Id INT);
PRINT 'ParentProc: #ParentTemp ' + ISNULL(CAST(OBJECT_ID('tempdb..#ParentTemp') AS VARCHAR(10)), 'NULL');
-- call a nested proc
PRINT 'ParentProc: #ChildTemp before child proc call ' + ISNULL(CAST(OBJECT_ID('tempdb..#ChildTemp') AS VARCHAR(10)), 'NULL');
EXECUTE ChildProc;
PRINT 'ParentProc: #ChildTemp after child proc call ' + ISNULL(CAST(OBJECT_ID('tempdb..#ChildTemp') AS VARCHAR(10)), 'NULL');
GO
-- ==============================================
-- test
PRINT '-- exec proc that creates a temp table'
PRINT 'Session: before CreateTempTable ' + ISNULL(CAST(OBJECT_ID('tempdb..#ProcTemp') AS VARCHAR(10)), 'NULL');
EXECUTE CreateTempTable;
PRINT 'Session: after CreateTempTable ' + ISNULL(CAST(OBJECT_ID('tempdb..#ProcTemp') AS VARCHAR(10)), 'NULL');
PRINT ''
PRINT '-- exec proc that creates a temp table and works with sub procs'
PRINT 'Session: #ParetTemp before ParentProc ' + ISNULL(CAST(OBJECT_ID('tempdb..#ParentTemp') AS VARCHAR(10)), 'NULL');
PRINT 'Session: #ChildTemp before ParentProc ' + ISNULL(CAST(OBJECT_ID('tempdb..#ChildTemp') AS VARCHAR(10)), 'NULL');
EXECUTE ParentProc;
PRINT 'Session: #ParetTemp after ParentProc ' + ISNULL(CAST(OBJECT_ID('tempdb..#ParentTemp') AS VARCHAR(10)), 'NULL');
PRINT 'Session: #ChildTemp after ParentProc ' + ISNULL(CAST(OBJECT_ID('tempdb..#ChildTemp') AS VARCHAR(10)), 'NULL');
only4mithunc (9/11/2011)
... inside the SP the temp tables are created by CREATE TABLE #table. So in this scenario will the DROP TABLE statement make any difference?
Nope, the temp tables will be (marked as) dropped as soon as your procedure call runs out of scope.
only4mithunc (9/11/2011)
As the per all the above replies now my understanding is that the tables will be created while running the report and once that window is closed the tables will be dropped by its own and the space used will be released.
Depends on how SSRS works with connections and what you mean with "window is closed". If you execute the report by utilizing the SSRS and SSRS works with connection pooling - what I don't know, your temp tables will be available until ADO.NET closes its internal connection or reuses the connection for another task.
Marios Philippopoulos (9/11/2011)
Dropping the temp table explicitly at the end of a batch is considered good programming practice. On the other hand, I have seen it mentioned that NOT dropping it explicitly and letting SQL server do the cleanup instead, takes advantage of the temp-table caching feature: SQL caches the metadata structure to re-use it next time the temp table gets created by a different session. This provides a performance boost, especially when lots of temp tables get created and dropped.
Thanks Marios, very good point!
Yes, when using temp tables inside of procedures it is mentioned not to drop them explicitly for SQL Server 2005 and later versions. Here is the related Tech Net article: Working with tempdb in SQL Server 2005.
And here is the most important paragraph related to dropping temp tables:
SQL Server 2005 caches temporary objects. When table-valued functions, table variables, or local temporary tables are used in a stored procedure, function, or trigger, the frequent drop and create of these temporary objects can be time consuming. This can cause contentions on tempdb system catalog tables and allocation pages. In SQL Server 2005, these are cached. That means that dropping and creating temporary objects is very fast. When SQL Server drops a temporary object, it does not remove the catalog entry for the object. If a temporary object is smaller than 8 MB, then one data page and one IAM page are also cached so that there is no need to allocate them when re-creating the objects. If a temporary object is larger than 8 MB, defer drop is used. When tempdb is low on space, SQL Server frees up the cached temporary objects. You can drop the associated stored procedure(s) or free the procedure cache to get rid of these temporary tables.
Greets
Flo
September 12, 2011 at 5:32 am
Hi Flo,
Thanks a lot for the explanation. 🙂 Now it is clear for me.
Thanks & Regards,
MC
August 22, 2012 at 11:34 pm
Marios Philippopoulos (9/11/2011)
Dropping the temp table explicitly at the end of a batch is considered good programming practice.
Certainly best practice for ad-hoc batches, but I would say it is merely personal preference within a procedure, since there is no advantage to writing the explicit DROP TABLE statement as the very last statement of a procedure. It may, however, be beneficial to drop temporary tables within a procedure at an earlier time.
On the other hand, I have seen it mentioned that NOT dropping it explicitly and letting SQL server do the cleanup instead, takes advantage of the temp-table caching feature: SQL caches the metadata structure to re-use it next time the temp table gets created by a different session. This provides a performance boost, especially when lots of temp tables get created and dropped.
That is not true, DROP TABLE does not affect temporary table caching in a procedure, see http://bit.ly/TempCaching.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 23, 2012 at 5:57 pm
SQL Kiwi (8/22/2012)
Marios Philippopoulos (9/11/2011)
Dropping the temp table explicitly at the end of a batch is considered good programming practice.Certainly best practice for ad-hoc batches, but I would say it is merely personal preference within a procedure, since there is no advantage to writing the explicit DROP TABLE statement as the very last statement of a procedure. It may, however, be beneficial to drop temporary tables within a procedure at an earlier time.
On the other hand, I have seen it mentioned that NOT dropping it explicitly and letting SQL server do the cleanup instead, takes advantage of the temp-table caching feature: SQL caches the metadata structure to re-use it next time the temp table gets created by a different session. This provides a performance boost, especially when lots of temp tables get created and dropped.
That is not true, DROP TABLE does not affect temporary table caching in a procedure, see http://bit.ly/TempCaching.
Thank you for the correction and the link, I will certainly have a look.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply