June 15, 2005 at 3:27 am
I have an instance of SQL Server 2000 (SP4) running on Win 2K.On it I have a task that runs to decompose xml into tables using sp_prepare/sp_remove xml doc procedures and open xml.When the server is booted or sql server and sql server agent process restarted it sits happily using about 60MB of memory. However once I start my xml decomposition task the memory gradually creeps up until it hits the maximum.Now I know SQL server will effectively grab all the memory it can and keep it.However my issue is not with the amount of memory it has taken, but the way it would appear to use it.This is because when the task first kicks off it runs like grease lightening and will decompose about 1000 1MB XML files a minute. However once the memory has reached maximim this is down to 1 1MB XML file a minute. Quite a significant difference despite the file being indetical in structure (they are messages with different content). I have tried to use profiler to determine where the problem is, but it looks most likely that sql server is not releasing enough unused memory back to itself. The overhead is in the passing of large chunks of data between stored procedures and the use of the xml (sp_prepare and sp_remove) and this is only a significant overhead once SQL Server has taken all the memory for itself. Also once the task is stopped at no point is memory ever released by SQL Serevr even if there is no activity at all for hours/days. Is this a known issue/bug or is it possible that I have done something wrong?Note sp_remove is always called after an sp_prepare so I assume that this should unallocate any memory used by that. Alternatively if a parent procedure runs continually calling the same child procedure, do the variables local to the child procedure get cleared properly between each call if the parent is still active?I'm very perplexed and would greatly appreciate some assistance/ideas/CheersTom
June 15, 2005 at 4:10 am
I have an instance of SQL Server 2000 (SP4) running on Win 2K.
On it I have a task that runs to decompose xml into tables using
sp_prepare/sp_remove xml doc procedures and open xml.
When the server is booted or sql server and sql server agent process
restarted it sits happily using about 60MB of memory. However once I start my
xml decomposition task the memory gradually creeps up until it hits the
maximum.
Now I know SQL server will effectively grab all the memory it can and keep it.
However my issue is not with the amount of memory it has taken, but the way
it would appear to use it.
This is because when the task first kicks off it runs like grease lightening
and will decompose about 1000 1MB XML files a minute. However once the memory
has reached maximim this is down to 1 1MB XML file a minute. Quite a
significant difference despite the file being indetical in structure (they
are messages with different content).
I have tried to use profiler to determine where the problem is, but it looks
most likely that sql server is not releasing enough unused memory back to
itself. The overhead is in the passing of large chunks of data between stored
procedures and the use of the xml (sp_prepare and sp_remove) and this is only
a significant overhead once SQL Server has taken all the memory for itself.
Also once the task is stopped at no point is memory ever released by SQL
Serevr even if there is no activity at all for hours/days.
Is this a known issue/bug or is it possible that I have done something wrong?
Note sp_remove is always called after an sp_prepare so I assume that this
should unallocate any memory used by that.
Alternatively if a parent procedure runs continually calling the same child
procedure, do the variables local to the child procedure get cleared properly
between each call if the parent is still active?
I'm very perplexed and would greatly appreciate some assistance/ideas
Cheers
Tom
REPOST To try and inprove the strange formatting
June 16, 2005 at 2:21 am
Bump - help.
Anyine else experenced this?
June 16, 2005 at 7:11 am
I am not sure SP4 has bugs or not in the xml related SPs. In SP3, if your SP decompposing xml is getting slower dramatically, it is probably caused by memory leak.
Check the "OPENXML and memory leak" in the following link: http://spaces.msn.com/members/p-sql/?partqs=ayear%3D2005%26amonth%3D5&_c11_blogpart_blogpart=blogview&_c=blogpart
As for the second question, AFAIK, the local variables of the callee are cleaned between each call of the caller if the parent is still alive.
June 16, 2005 at 7:52 am
Thanks for your response.
I have actually seen this article and believe I have covered this by my programming, but I will find another a collegue to have a good look in case I have missed a possible exit route where the dosument isn't removed.
What really perplexes is me though is that it ceratinly does look like a memory leak, but the machine will never run out of memory. It reaches a plateau when the physical memory runs out and never increases after that. I have never even had a memory warning come up. It's performance simply begins to grind. It can be left in this low performance state indefiniately with the same task running, just very slowly.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply