Temporary tables in stored procedures

  • xblondel

    Say Hey Kid

    Points: 708

    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

     

  • Jonathan

    SSC-Insane

    Points: 20427

    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

    Say Hey Kid

    Points: 708

    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

    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

    Say Hey Kid

    Points: 708

    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

    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

    Say Hey Kid

    Points: 708

    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

    > 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

    Say Hey Kid

    Points: 708

    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: 141

    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.

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

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