July 22, 2011 at 8:02 am
Can someone shed some light on global temp tables (##temp) and the relationship they have with RAM?
Is it somehow written completely in memory before stored in tempdb?
I can't find anything that gives me a clear understanding of what happens when the ##temp table is created.
thanks in advance
July 22, 2011 at 8:12 am
##temp tables are created in tempdb, just like #temp tables.
I recommend that you avoid ##temp tables whenever you can, and only create them when there is no other way to accomplish what you need to do.
You should always dynamically generate the table name with a random name based on a guid ( NEWID() ), so that you do not conflict with other ##temp tables on the same server.
July 22, 2011 at 8:15 am
Keep in mind that one of the main reason why you want to use Global temp table is because you want it available for different sessions. That means when it is queried the first time it is going to be put in memory. The other sessions will be reading the same data from the memory. So it is going to be using RAM.
Other than that, the global temp table works just like a normal table.
Just my 2 cents
-Roy
July 22, 2011 at 8:28 am
Thanks for the responses.. a little more detail.
I'm not actually using the ##temp tables, I have a client and I asked him why did he want to use them instead of a permenant table that is created and drop (he's using this in dynamic sql so a regular temp table doesn't work).
For some reason he just has a thing against creating real tables for temp work.
But we got over that, but we were seeing that the Memory would shoot up high on the server whenever the ##temp tables were created and I'm just trying to understand more about what the affects are on memory etc.. when using them.
Not to mention this is a very small server with limited resources.
July 22, 2011 at 10:53 am
nawillia (7/22/2011)
...(he's using this in dynamic sql so a regular temp table doesn't work)...
If you can create the temp table outside the dynamic SQL you can reference it inside. Afterwards all the data will still be there. If you're creating it inside the dynamic SQL that you run into a problem because it doesn't exist afterwards.
create table #temp (id int);
declare @sql varchar(8000)
set @sql = 'insert into #temp values(1)'
exec (@sql)
select * from #temp
drop table #temp
July 22, 2011 at 11:58 am
cfradenburg (7/22/2011)
nawillia (7/22/2011)
...(he's using this in dynamic sql so a regular temp table doesn't work)...If you can create the temp table outside the dynamic SQL you can reference it inside. Afterwards all the data will still be there. If you're creating it inside the dynamic SQL that you run into a problem because it doesn't exist afterwards.
create table #temp (id int);declare @sql varchar(8000)
set @sql = 'insert into #temp values(1)'
exec (@sql)
select * from #temp
drop table #temp
Thanks, that was the exact problem. The temp table was being created inside the the dynamic sql.
July 22, 2011 at 12:06 pm
@cfradenburg (thanks for the info above --forgot my manners)
One last note, the client isn't developing using a 'standard' the schema on tables can change on a whim, etc...
so I checked the code and he's even doing a select xxxx into tmp from xxxxx
so i don't think wants to take the time to think about the ddl needed for a temp table, but just wants to do a quick and dirty table creation.
July 22, 2011 at 12:11 pm
The select into could be done outside the dynamic SQL and it should function the same. I hope they know the consequences of not thinking out the table definition. I worked on a product that had big problems due to that and by the time they surfaced we were stuck.
July 22, 2011 at 12:23 pm
Well the reason that it's dynamic is because there are a few tables with the same structure that he's using in the the 'from' clause.
so the idea is that the variable will contain that from table and the SP can be cloned for multiple tables.
Again this thing is a work in progress and i've been away for a few days and come back and whole databases and functions moved or rewritten.
One guy is primarily doing all the work so that what happens.
Back to my original issue, which i might need to take to another forum, but using those ##temp tables was making the memoroy max out.
So I was wondering if the ##temp table had anything to do with RAM.
(a side note we are going over upgrade options, but that would only hide bad code so that's why I wanted to find documentation on the negative effects of using ##temp.)
July 22, 2011 at 12:44 pm
I don't know of any memory difference for local versus global temp tables. I wouldn't be surprised if global ones took more resources because they need to be more widely accessible and cant' disappear until all sessions have stopped referencing them. But I don't know anything authoritative and I haven't gotten familiar enough with the memory DMVs to do a quick test to see if that's the case.
Out of curiosity, is something being done to make sure that two sessions aren't using the same table name when they shouldn't be?
July 22, 2011 at 1:07 pm
Actually it's not really worth it, the server isn't even dedicated to SQL Server, so I keep saying I don't think just running a query would max out the memory (but that's why i'm asking). Normally i've seen the tempdb hit it's limit in size before the memory just maxes out.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply