Ineeficient Memory Usage

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
Have I posted this in the wrong place or is my wording too poor for anyone to
assist?
I had hoped to see some replies by now.
Please feel free to state the obvious as I may have missed something simple.
Cheers
Tom
"TomPearson" wrote:

> 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
>
|||Have I posted this in the wrong place?
I had hoped for some replies by now.
If my wording is poor or unclear I am will hapily clarify as I could really
do with some advice on this issue.
Also don't be afraid to state the obvious as I may have missed something
really simple.
Cheers
Tom
"TomPearson" wrote:

> 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
>
|||Hi Tom
This is the right newsgroup. But since it is a newsgroup, it may take a
while until your posting gets distributed and people answer.
There are lots of reasons why this may happen, including a problem with SQL
Server. Are there any other processes going on in the database that may use
up memory and resources (other queries, allocating temp tables etc)?
Can you send us a repro, so that we can investigate it in our lab to see
whether it repros and if, why?
Thanks
Michael
"TomPearson" <TomPearson@.discussions.microsoft.com> wrote in message
news:7F720240-6B2A-414F-8296-1025383DB57C@.microsoft.com...[vbcol=seagreen]
> Have I posted this in the wrong place?
> I had hoped for some replies by now.
> If my wording is poor or unclear I am will hapily clarify as I could
> really
> do with some advice on this issue.
> Also don't be afraid to state the obvious as I may have missed something
> really simple.
> Cheers
> Tom
> "TomPearson" wrote:
|||I have tried this in isolation without any other db queries, processes etc
going on and the result is the same. No temp tables are used during this task.
I would be eager to have someone look into the problem, but I don't
understand what you mean by a repro I'm afraid.
Cheers
Tom
"Michael Rys [MSFT]" wrote:

> Hi Tom
> This is the right newsgroup. But since it is a newsgroup, it may take a
> while until your posting gets distributed and people answer.
> There are lots of reasons why this may happen, including a problem with SQL
> Server. Are there any other processes going on in the database that may use
> up memory and resources (other queries, allocating temp tables etc)?
> Can you send us a repro, so that we can investigate it in our lab to see
> whether it repros and if, why?
> Thanks
> Michael
> "TomPearson" <TomPearson@.discussions.microsoft.com> wrote in message
> news:7F720240-6B2A-414F-8296-1025383DB57C@.microsoft.com...
>
>
|||A repro is a T-SQL script, data and (if necessary) some code that shows the
behaviour so we can try to run it in our environment to see whether we can
find the reason for the behaviour (as per email exchange).
Best regards
Michael
"TomPearson" <TomPearson@.discussions.microsoft.com> wrote in message
news:567A03AE-AEF4-4147-A39E-6D2F9EADB4E3@.microsoft.com...[vbcol=seagreen]
>I have tried this in isolation without any other db queries, processes etc
> going on and the result is the same. No temp tables are used during this
> task.
> I would be eager to have someone look into the problem, but I don't
> understand what you mean by a repro I'm afraid.
> Cheers
> Tom
>
> "Michael Rys [MSFT]" wrote:
|||Michael,
I have similler issue with the MSXML parser with Visual Studio. Here I have
a sample code. Can you run this and see you find any thing worng with my code.
thanks in advance.
#include "stdafx.h"
#include <msxml2.h>
#include "comutil.h"
#include "psapi.h"
void Init();
void Release();
BOOL LoadXML(LPSTR lpzxml);
BOOL Go_To_Parent(LPCSTR lpszNodeName);
BOOL GetChild(LPCSTR lpszNodeName, LPCSTR lpszNodeValue);
void PrintMemoryInfo( DWORD processID, int Iteration );
IXMLDOMDocument2*pXMLDom;
IXMLDOMNode*pNode;
int _tmain(int argc, _TCHAR* argv[])
{
LPSTR lpzxml =
"<PromptDataRoot><Errors>OK</Errors><NodeId>3</NodeId><Action></Action><SessionDataRoot><SessionId>001050625171432 00002</SessionId><DateTime>6/25/2005
5:14:37
PM</DateTime><CurrentDataItem>TNI</CurrentDataItem><LookupUserInputKey>1</LookupUserInputKey><DataCollected><InitialUserKey> 1</InitialUserKey><LANGUAGECHOICE>1</LANGUAGECHOICE></DataCollected><StepLog><PromptData><NodeId>2</NodeId><UserInputKey>1</UserInp
utKey><LookupKey>1</LookupKey></PromptData><Action></Action></StepLog></SessionDataRoot><PromptControlData><Language>0</Language><MainMessage><MsgId>!102</MsgId><ReplayMsgKey></ReplayMsgKey><CorrectValues></CorrectValues><CharSet>0123456789</CharSet><Leng
th>10</Length><Timeout>10000</Timeout><TimeOutMsgId>!102</TimeOutMsgId><RetryCount>3</RetryCount><RetryExitAction><Transfer></Transfer><End>N</End></RetryExitAction><RetryExitMsgId>!125</RetryExitMsgId></MainMessage><ConfirmMessage><MsgId>!103
^# !104</MsgId><CorrectValues>1,2
</CorrectValues><CharSet></CharSet><Length>1</Length><Accept>1</Accept><Reject>2</Reject><Timeout>10000</Timeout><TimeOutMsgId>!103
^#
!104</TimeOutMsgId><RetryCount>2</RetryCount><RetryExitAction><Transfer><Record>N</Record><Phone>2003207</Phone><BusyAction>End</BusyAction><BusyMsgId>!123</BusyMsgId></Transfer><End>N</End><CompletionStatus>1</CompletionStatus></RetryExitAction><RetryExi
tMsgId>!126</RetryExitMsgId></ConfirmMessage></PromptControlData><SpecialProcessing><MiscMessageI dTags></MiscMessageIdTags></SpecialProcessing></PromptDataRoot>";
char szValue[512];
DWORD x;
for(int i = 0 ;i<1000;i++)
{
Init();
LoadXML(lpzxml);
Go_To_Parent("PromptDataRoot");
GetChild("Errors", szValue);
Release();
x = GetCurrentProcessId();
PrintMemoryInfo(x, i);
Sleep(10000);
}
MessageBox(NULL, "Done", "Info", MB_OK);
return 0;
}
void Init()
{
CoInitialize(NULL);
CoCreateInstance(__uuidof(DOMDocument40),
NULL,
CLSCTX_INPROC_SERVER,
__uuidof(IXMLDOMDocument2),
(void**)&pXMLDom);
pXMLDom->put_async(VARIANT_FALSE);
pXMLDom->put_validateOnParse(VARIANT_FALSE);
pXMLDom->put_resolveExternals(VARIANT_FALSE);
}
void Release()
{
if (pNode)
pNode->Release();
if (pXMLDom)
pXMLDom->Release();
CoUninitialize();
}
BOOL LoadXML(LPSTR lpzxml)
{
VARIANT_BOOL status;
BSTR bstrXML;
try{
bstrXML = _bstr_t(lpzxml);
pXMLDom->loadXML(bstrXML, &status);
return TRUE;
}
catch(...)
{
return FALSE;
}
}
BOOL Go_To_Parent(LPCSTR lpszNodeName)
{
IXMLDOMElement *pElementRoot;
IXMLDOMNodeList *pNodeList;
BSTRbstrValue;
long lNodeLength;
try{
pXMLDom->get_documentElement(&pElementRoot);
pElementRoot->getElementsByTagName(_bstr_t(lpszNodeName), &pNodeList);
pNodeList->get_length(&lNodeLength);
if (lNodeLength > 0)
{
pNodeList->get_item(0, &pNode);
return TRUE;
}
else
{
pXMLDom->get_childNodes(&pNodeList);
pNodeList->get_item(0, &pNode);
pNode->get_xml(&bstrValue);
return TRUE;
}
}
catch(...)
{
return FALSE;
}
}
BOOL GetChild(LPCSTR lpszNodeName, LPCSTR lpszNodeValue)
{
BSTR bstrValue;
long lLength, l;
IXMLDOMNodeList *pNodeList;
IXMLDOMNode *pLocalNode;
try{
pNode->get_childNodes(&pNodeList);
pNodeList->get_length(&lLength);
for (l=0;l <=lLength;l++)
{
pNodeList->get_item(l, &pLocalNode);
pLocalNode->get_xml(&bstrValue);
pLocalNode->get_nodeName(&bstrValue);
if (strcmp((LPSTR)_bstr_t(bstrValue), lpszNodeName) == 0)
{
pLocalNode->get_text(&bstrValue);
sprintf((char *)lpszNodeValue, "%s", (LPCSTR)_bstr_t(bstrValue));
return TRUE;
pNodeList->Release();
pLocalNode->Release();
}
}
return FALSE;
}
catch(...)
{
return FALSE;
}
}
void PrintMemoryInfo( DWORD processID, int Iteration )
{
HANDLE hProcess;
PROCESS_MEMORY_COUNTERS pmc;
TCHAR szProcessName[MAX_PATH] = TEXT("<unknown>");
hProcess = OpenProcess( PROCESS_QUERY_INFORMATION |
PROCESS_VM_READ,
FALSE, processID );
if (NULL == hProcess)
return;
HMODULE hMod;
DWORD cbNeeded;
if ( EnumProcessModules( hProcess, &hMod, sizeof(hMod),
&cbNeeded) )
{
GetModuleBaseName( hProcess, hMod, szProcessName,
sizeof(szProcessName)/sizeof(TCHAR) );
}
if ( GetProcessMemoryInfo( hProcess, &pmc, sizeof(pmc)) )
printf( "Iteration :%d, ProcessName :%s, PID:%u, Memory Usage:%dk
\n",Iteration, szProcessName, processID ,pmc.WorkingSetSize/1024);
CloseHandle( hProcess );
}
"Michael Rys [MSFT]" wrote:

> A repro is a T-SQL script, data and (if necessary) some code that shows the
> behaviour so we can try to run it in our environment to see whether we can
> find the reason for the behaviour (as per email exchange).
> Best regards
> Michael
> "TomPearson" <TomPearson@.discussions.microsoft.com> wrote in message
> news:567A03AE-AEF4-4147-A39E-6D2F9EADB4E3@.microsoft.com...
>
>