Temporary tables in stored procedures

Question

Temporary tables in stored procedures

xblondel

SSC Eights!

Points: 818
More actions
January 15, 2004 at 11:32 am

#358920

Hi all!
We have an application running on IIS 5, connecting to a SQL Server SP2 back-end (W2K everywhere.)
We have a bunch of stored procedures using a lot of small local temporary tables. To save connection time, we use a (growing) pool of connections to SQL Server.
I thought that the life scope of the temporary table was the connection (at least, that's what all of my readings say!), but I noticed the temporary table's name disappears from tempdb..sysobjects as soon as the stored procedure exits, even though the connection is still there. So, is there a narrower scope of the temporary tables, at the stored procedure level? Or do I need to drop the temporary tables manually as the stored procedure finishes?
Actually, my main concern is that we have an impressive amount of memory leaks: the sqlserver process keeps on demanding more memory, even though the activity decreases (but the pool of connections is not shrinked immediately...) So I'd rather be sure that the memory space used by the temporary tables is recycled as soon as the stored procedures end.
Thanks in advance,
Xavier

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

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

From BOL:

"A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table."

As for your memory "leaks," please read this:

http://support.microsoft.com/?id=321363

--Jonathan

xblondel SSC Eights! Points: 818 More actions · Answer 2

Hi!

Jonathan: Thanks!

Thanks also for the link - but my problem is not to have the memory decrease: it's to make it stop increase!

That is: SQL Server memory increases relative to the activity - fine by me. But I would expect it to stabilize, when the activity is getting low and memory should be recycled and reused - it's not that *big* an application we are running!!! - hence the "leaks".

I don't put the blame on SQL Server (I've bumped into far more complicated apps running smoothly on SQL Server.) I'm sure it is something badly written somewhere in the application - some ADOblah going into the wild, probably...

Bye,

Xavier

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 3

xblondel,

I think that SQL will continue to grab more memory until it reaches the max you specify. I have seen this with all MS products from Office 95 forward. This is to allow the Server to (in theory) run quicker and more efficiently. Unfortunately other things (O/S etc...) suffer.

You can use different switches to tell SQL to use only x memory but the only way to get the mem grabbed (that I have seen work 100%) is to cycle the box.

As far as the #tables go... If you want the #table to be available for as long as it is needed research ##table. They stay around across ALL connections until the last connection is closed against it and then it disappears. Conversely, you could research the creation of small tables that contain data for the particular user/session and then when they logged off or closed the application the table would flush itself or drop.

Good Hunting!

AJ Ahrens

webmaster@kritter.net

xblondel SSC Eights! Points: 818 More actions · Answer 4

Hi!

Thanks for the info.

But my problem is not to limit SQL Server memory usage; it's alone on its box and can take whatever suits it fine.

No, what I want to understand is why it takes so much memory, as if the buffer pool was constantly full of used pages (with nothing for the lazywriter to return to the free list).

It's not a huge application, as regards the size of the databases (compared to the server RAM) and the number of connections, and that's what makes me think there is something wrong somewhere, hence my questions about retained space in stored procs.

I keep on investigating!

And to fully reply, #tables are what we need.

Bye,

Xavier

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 5

Xavier,

As fas as I know (and have been told) MS adds frequently accessed tables to memory, Master sp's and xp's to mem on start and database sp's etc... on access. This is where the memory increase is coming from. I think that once the server grabs the memory for the object that it holds it forever (until boot) regardless of any active connections. This is how the server can speed up access.

Memory read is quicker than the physical logical reads and this is where the server likes to grab the sp's from.

I apologize for not understanding the question about the #tables.

And if my answer isn't close enough I'm sure I'll be reading about it here 🙂

Good Hunting!

AJ Ahrens

webmaster@kritter.net

xblondel SSC Eights! Points: 818 More actions · Answer 6

Hi!

Forget about the #tables, I already got the answer!

I re-read thoroughly the memory management sections of Inside SQL Server this week-end (as if I had nothing else to do! 😉

It seems that data pages and procedures are both handled by the lazywriter, which seems to imply that seldomly used pages are kicked out of the cache after a while.

But you gave me an idea: I will have a look at syscacheobjects to see how much stored procedures are in the cache. Since we use a lot of them, it may be a track to follow.

Thanks,

Xavier

Jonathan SSC-Insane Points: 20427 More actions · Answer 7

> It seems that data pages and procedures are both handled by the lazywriter, which seems to imply that seldomly used pages are kicked out of the cache after a while.

No, that's not correct. Reread the KB article I linked to above. Cached pages are not removed from the buffer pool unless and until the buffer pool is very close to its maximum allocated size.

--Jonathan

xblondel SSC Eights! Points: 818 More actions · Answer 8

Hi!

Sorry for my lag, it's been a hard week! 😉

Thanks for the info Jonathan.

I finally found where all my memory goes:

select count(*) from syscacheobjects

returns about 60000 thousands objects, accounting for a good 700 MB!

For those who are interested, the pagesused columns helps you find the memory used by the objects.

Most of the objects are prepared statements, and the application uses a lot of sp_executesql calls. So I used the DBA plan 1.a.1: "Change your application or gimme more RAM, Boss." 😉

Bye,

Xavier

Aris Routis SSC Enthusiast Points: 142 More actions · Answer 9

SQL server will tie up as much ram as it can get on the machine it resides, and will manage it as it sees fit. I see nothing strange in this situation.