Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Table variable is not automatically dropped in TempDB Expand / Collapse
Author
Message
Posted Saturday, April 27, 2013 1:50 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 3:32 AM
Points: 66, Visits: 216
hi,
I have created procedure like .

USE [master]
GO
/****** Object: StoredProcedure [dbo].[b] Script Date: 04/28/2013 01:09:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[b]
as
begin
declare @i table (id int)
select * From tempdb.sys.tables
end

and after i ran the executed my proce

exec b

The result is :
name objectid
#023D5A04 37575172 and so on...

i closed the above connection and after i opened new connection and ran the query like

select * from tempdb.sys.tables

it is showing the below result and table exists in tempdb with the name:

The result is :
name objectid
#023D5A04 37575172 and so on...

Question is:
Why did not it automatically dropped when i ran procedure and even i closed the connection please help on this..

Thanks,
Dastagiri

Post #1447290
Posted Sunday, April 28, 2013 6:56 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
It is effectively dropped, you can't in any way access the table variable after it goes out of scope. As an optimisation, SQL caches the definitions for temp tables & table variables so there's less work involved in recreating them.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1447321
Posted Monday, April 29, 2013 8:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 3:32 AM
Points: 66, Visits: 216
The object still exist in under tempdb
Post #1447550
Posted Monday, April 29, 2013 8:12 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
Yes, it's an optimisation, SQL caches the definition and a single data page to reduce the work required to recreate it. The table variable's effectively gone, you cannot access it in any way, there's just a shell cached for future use.

Why is it a concern?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1447553
Posted Monday, April 29, 2013 7:44 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:36 AM
Points: 4,576, Visits: 8,348
I made up a quick test to verify Gila's suggestion.
Very interesting outcome.

Whoever is curios try to run this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[b]
as
begin
declare @i table (id int, NAME nvarchar(150))
select 'during exec', * From tempdb.sys.tables ORDER BY create_date DESC, name
end

GO

select 'after create', * From tempdb.sys.tables ORDER BY create_date DESC, name
GO
exec b
GO
select 'after exec', * From tempdb.sys.tables ORDER BY create_date DESC, name
go
exec b
GO
select 'after exec again', * From tempdb.sys.tables ORDER BY create_date DESC, name
go
DROP proc [dbo].[b]
GO
select 'after drop', * From tempdb.sys.tables ORDER BY create_date DESC, name
GO
WAITFOR DELAY '00:01:00'

select 'after 1 minute wait', * From tempdb.sys.tables ORDER BY create_date DESC, name
GO

On my desktop (being effectively idle at the time) the table variable disappeared only in the "after 1 minute" resultset.

Very different from the behaviour on SQL2000: object is gone immediately after SP is completed.

Post #1447842
Posted Tuesday, April 30, 2013 3:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
Temp table caching was added in SQL 2005 to alleviate the contention on the TempDB allocation pages caused by frequent creation of temp tables. You can probably find a few articles on it, not something that's been widely written about though.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1447929
Posted Tuesday, April 30, 2013 3:31 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:36 AM
Points: 4,576, Visits: 8,348
GilaMonster (4/30/2013)
Temp table caching was added in SQL 2005 to alleviate the contention on the TempDB allocation pages caused by frequent creation of temp tables.


I wish that would be a bottleneck in our systems...

Post #1447930
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse