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 degrade too fast

(Win2003, SQL Server 2000 SP4)

I have a database of about 5Gb of size. Some queries where taking more than 1 minute to complete execution (all of them are stored procedures). Because of that lack of performance, I call the command DBREINDEX for each table, executed the sp_updatestats system stored procedure and finally I executed the sp_recompile system stored procedure for each sp in my database.

After all this task, queries completed in a matter of a few seconds instead of minutes. Strange enough is that some hours later (about 6 hrs), after normal use (this database belong to a Client/Server information system), the problem appeared again: Queries started to take too long to complete.

I am assuming that indexes are degrading too fast so that they required another ReIndex, but I am not sure.

Any thoughts? How can I prevent this behaviour?

Thank a lot in advanced.Have a look at the execution plan before and after the re-indexing routine. This will give you the "good" plan, and the "bad" plan. If the plans are identical, then the reindexing helped (doubtful but possible on a 5Gb system). More likely, the sp_recompile bumped the plan back from the "bad" plan to the "good" plan. By now, you are wondering if I just like putting "good" and "bad" in quotes. Well, no, I am not particularly fond of it, but it does remind you that not all plans are created equal. Can these procedures take in parameters that can determine a range? If so, then the "bad" plan may be good for some ranges, and the "good" plan may be bad for the same range.|||Thanks. What I really find confusing is why the performance of the database degrade in a matter of just 6 hours or so.

Of all the customers that I have distributed the same DB design, this one is the only one that presents this problem; and the size of the database is not the issue: I have other customer that has a 15 Gb database with the same DB design, then same DBMS version but with a not-as-good hardware. However, this customer does not have the same problem.|||What does DBCC SHOWCONTIG show you on the tables in question once the slow down restarts? Do you see high fragmentation numbers and low density numbers? Then yes your indices have degraded. have you traced the 2 systems. Does the one that is experiencing the issue have more insert and updates goinng on? Is it growing at a faster rate?|||Thanks. I will check that out. Just in case: Which DBCC command allow me to defrag all the tables of a database? I am planning to run that command and later run a DBCC REINDEX command for each index of every table. I suppose that this would produce a clean database, am I right?

Thanks in advance.|||There is no command to rebuild all of the tables/indexes at once. You can create or google for scripts that will do that for you, however.