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...
>
>

Indexes on datasets created froma Union All task

Hi

I have a package that takes data from 3 different sources, unions them all together and then performs a lookup against a tabel on a different server. The 3 sources of data creates a dataset with quite a large volume of rows (approx 100,000) and the look up takes an age to complete.

Is there anyway of adding an index of sorts to the dataset created by the union to speed the process up? I guess i could output the union all to a temp table and look up against this but wondered if there was another way

thanks

scott

100,000 rows is not much in my experience and fully-cached lookups are very fast once the reference data is loaded. I assume you're not running the lookup in cache mode?

If you are running in non-cached mode, I would think you would be focusing your indexing efforts on the reference table, not the rows in the pipeline. And is that really necessary?

I don't follow you on the temp table idea. It sounds like the data from the union all is the reference data. I can't visualize the scenario.

If the lookup is cached, are you sure the slowdown is in the Lookup and not downstream? A component cannot output rows if the next component is not ready to receive them. Try replacing the everything below the Lookup with a Union All and no destination. It should scream.
|||

Hi Jay

thanks for the reply

I made a mistake it is only 10,000 rows so even less!

I have 3 sources going into a union all which takes about a 2 seconds to 'go green'. next the Union and the look up go yellow and it takes about 90 seconds to complete. After this I simply have 2 destinations - success from look up goes to one tabel and error output goes to another table. Once the look up is complete the population of the 2 destination tables takes the blink of an eye.

I am not sure what you mean by caching though?

cheers

scott

|||

On the Advanced tab of the Lookup component, make sure that "Enable Memory Restriction" is not checked. Also, make sure that you are not using the table selection method in the lookup - you should specify a SQL statement that returns the smallest number of columns possible. So ideally you would return two columns, the value to match on, and the value to return.

How many rows in your lookup?

|||

Racsco wrote:

I have 3 sources going into a union all which takes about a 2 seconds to 'go green'. next the Union and the look up go yellow and it takes about 90 seconds to complete. After this I simply have 2 destinations - success from look up goes to one tabel and error output goes to another table. Once the look up is complete the population of the 2 destination tables takes the blink of an eye.

90 seconds for 10,000 rows is 111 rows/second. That could be indicative of not caching. In this situation, the Lookup would issue 10,000 select statements to your reference table. When caching is enabled, the lookup should be almost as fast as the union.

I want to make sure you understand my point about the downstream components. If one of those destinations is slow (i.e. not using fast load), then the lookup and the union will appear slow as they wait for the destination to accept new rows.
|||

Hi John

I checked the advanced tab and enable memory restriction was checked. I unchecked it and ran it again and it was rapid to say the least. However out of the 10000 rows only 770 matched the lookup condition and the rest errored. I tested it again and checked the enable memory restriction box, ran it and all passed the look up condition

any ideas as to why?

If i run the look up as a join in query analyser all 10000 rows pass the look up condition

|||

Hi Jay

both destinations are set to table or view fast load and both are tables. Please see my previous reply above your most recent post as i got it going much faster but now have a problem with the look up

cheers

scott

|||

Racsco wrote:

Hi John

I checked the advanced tab and enable memory restriction was checked. I unchecked it and ran it again and it was rapid to say the least. However out of the 10000 rows only 770 matched the lookup condition and the rest errored. I tested it again and checked the enable memory restriction box, ran it and all passed the look up condition

any ideas as to why?

If i run the look up as a join in query analyser all 10000 rows pass the look up condition

I bet it's the collation. SQL Server performs case-insensitive comparisons by default. The lookup is case-sensitive. The would enable SQL Server to make matches that the lookup can't. You can try converting everything to uppercase.