November 7, 2013 at 5:08 am
Hi,
We have a procedure that takes a year as parameter and calculates the revenue for the company from all the existing documents (sales, purchases, ....).
That procedure uses a "dynamic" temporary table, since more than one window can be opened and execute the same operation:
DECLARE @tbl VARCHAR(100) = '#tbl' + REPLACE(CAST(NEWID() AS VARCHAR(40)), '-','_');
...
That temp table is used inside a dynamic SQL and the data is obtained using "EXEC (@SQL)"..
We've noticed that when using the same year SQL Server memory grow by 100k each time the procedure was executed...
Can this be because of the "dynamic" temp table? Since the table hasn't the same name SQL keeps putting the same data (not really the same cause the table has a different name) in memory?!
I've already "killed" the temp table, meanwhile, but I'd like to know if the use of "dynamic" temp tables has this behavior on SQL memory...
Thanks,
Pedro
November 7, 2013 at 7:31 am
I all those windows use a separate database connection, you don't need to use a different temporary table name. SQL Server will handle that for you.
As far as the "memory leak" is concerned, SQL Server must deal with the temporary object and could end up needing some memory for the task. As soon as the connection that uses the temp table is closed, the table gets deallocated.
Hope this helps
Gianluca
-- Gianluca Sartori
November 7, 2013 at 7:36 am
spaghettidba (11/7/2013)
I all those windows use a separate database connection, you don't need to use a different temporary table name. SQL Server will handle that for you.As far as the "memory leak" is concerned, SQL Server must deal with the temporary object and could end up needing some memory for the task. As soon as the connection that uses the temp table is closed, the table gets deallocated.
Hope this helps
Gianluca
It's the same connection, there's global variable that's the connection to the database and it's used all over the code, it a win form application that uses just one connection and keeps it ative always until the app is closed... Probably it should use a "web approach", make a new connection on every operation and closed it in the end...
Since the connection is the same, it isn't closed and so the memory keeps growing and growing...
In some servers with low memory, 3G, executing this operation 10 times made the server very slow since all the memory was used...
Pedro
November 7, 2013 at 9:10 am
WHat about dropping the temp tables explicitly then?
-- Gianluca Sartori
November 7, 2013 at 9:16 am
spaghettidba (11/7/2013)
WHat about dropping the temp tables explicitly then?
Already done that in this case but there are others more "complicated"...
It's a very old SW (VB6 code) and some complex operations still just use SQL as a "repository"...
Some operations create temporary tables, get some data from SQL, process in VB6, send back data processed and insert in the temp table created.
Then do some more stuff and finally get the data from the temp table and insert in the final table....
The operations made in VB6 are so complex that developers are afraid to create a procedure that does the same as the code (I already suggested that if they are more comfortable with programming languages then make a CLR :-)..)
Pedro
November 7, 2013 at 9:33 am
I feel your pain.
Been there, done that.
It still hurts 🙂
Talk to the DEVs and make them do the right thing. It may look too complicated in the beginning, but it's worth the effort in the end.
Good luck!
-- Gianluca Sartori
November 7, 2013 at 9:41 am
Thanks 🙂
It's complicated being a DBA on a company with a software with a big legacy and people that are comfortable with "if it works why should we change it?!"...
Pedro
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply