Win98 memory bottleneck for using XML in MSDE2000

[Andrzej]

Used to read newsgroup for answers, now have to ask for them as well.

I have an application (C#, .NET 1.1) that connects to local db on MSDE
2000 SP3a (using ADO from MDAC 2.71) on one side and to a web service
on the other (not relevant I guess). Some stored procedures consume
xml produced by DataSet objects (values as elements) using OPENXML
statements.
The application should be distributed among users having also Win98.
Established that I require min 128 MB of physical memory to run it.

However on Win98 I need to set 'min server memory' value to 128
otherwise even small OPENXML statement, which is first in the
application lifecycle (so I could not have missing sp_xml_removedocs
statments before) ends up with an error 'server memory low, use
sp_xml_removedocument ...'. It turns out that users having even 256MB
get that error as well, when larger XML is consumed by MSDE. That I
could understand, but I managed to configure application to run on
Win98 with 128MB!!! Only after some time users started to receive that
errors, and even restarts and running the application without any
other applications running at the same time, didn't help. What could
have changed in memory management assuming no other apps were
installed?

Tried setting up 'min memory per query' to even 32MB; tried to set up
'min/max server memory' to 128 (and more); tried to set up 'min/max
server memory' and 'set working set size' to 1 (it should reserve
memory for SQLserver process). I used 'reconfigure' statments and
performed restarts after having set above values as well.

Is there any other way I could force Win98 to give memory to MSDE or
to parsuade xml parser to be more memory efficient?

What I also know:
- MSDE instllation does not have msxml2.dll, so I included it in my
installation package (downloaded msxml2.msi from Microsoft with dll
file ver 8.30.9523.0, looks pretty new to me)
- XML parser in MSDE 2000 (any SQL 2000) uses 1/8 of memory available
for SQLserver, and that value cannot be changed (or can it?)
- MSDE 2000 cannot use XML other than MSXML2, and nothing can change
it, so I guess no help comes from MSXML upgrade
- XML parser uses A LOT of memory, and can consume several times more
memory that XML size
- memory management in Win98 sucks, but I cannot leave Win98 out of my
application distribution list

If you got that far in my post, thanx for that.
Rgds, Andrzej

[Bryant Likes]

"Andrzej" <th***@wp.pl> wrote in message
news:d8**************************@posting.google.c om...
[snip]

However on Win98 I need to set 'min server memory' value to 128
otherwise even small OPENXML statement, which is first in the
application lifecycle (so I could not have missing sp_xml_removedocs
statments before) ends up with an error 'server memory low, use
sp_xml_removedocument ...'.

This isn't a definite answer, but I would assume the problem is with how
much memory Windows 98 is allocating to SQL Server. In the
sp_xml_preparedocument documentation you will find that "[t]he MSXML parser
uses one-eighth the total memory available for SQL Server." This means that
sp_xml_preparedocument has access to only one-eighth of the total memory
assigned to SQL Server. So even if you have all 128 assigned to SQL Server,
you still only get 16 MB available. If Windows 98 gives you less than 128
(which I would assume it is doing) then you will get even less.

--
Bryant

[Michael Rys [MSFT]]

Bryant is correct.

However 16MB should still allow a fairly big XML document be parsed with
OpenXML. How big is the file/are the files and can you release the memory as
early as possible (for example, copy your data into temp tables and release
the handle).

Best regards
Michael

PS: Yes, the missing MSXML2 from all SP3/SP3a versions of SQLServer 2000 is
unfortunate. You can install the MSXML2 version distributed with SP2 or
request a version from your PSS support.

"Bryant Likes" <br****@suespammers.org> wrote in message
news:OL**************@TK2MSFTNGP12.phx.gbl...

"Andrzej" <th***@wp.pl> wrote in message
news:d8**************************@posting.google.c om...
[snip]

However on Win98 I need to set 'min server memory' value to 128
otherwise even small OPENXML statement, which is first in the
application lifecycle (so I could not have missing sp_xml_removedocs
statments before) ends up with an error 'server memory low, use
sp_xml_removedocument ...'.
This isn't a definite answer, but I would assume the problem is with how
much memory Windows 98 is allocating to SQL Server. In the
sp_xml_preparedocument documentation you will find that "[t]he MSXML

parser uses one-eighth the total memory available for SQL Server." This means that sp_xml_preparedocument has access to only one-eighth of the total memory
assigned to SQL Server. So even if you have all 128 assigned to SQL Server, you still only get 16 MB available. If Windows 98 gives you less than 128
(which I would assume it is doing) then you will get even less.

--
Bryant

[Andrzej]

Cheers for the answers,

I release XML handles as soon as I copy stuff to temp tables (I do it
for some time now, since I had 'internal sql server error's when I
performed joins on OPNXML statemens, but that's diferent story), so no
room for improvement here. I have no concurrent users, so XML parser
works only with one doc at a time (at least I suspect I does).

XML data comes from DataSet object and is not even big. Even XML
having 1,17kB can trigger an error, and XML data having 5,61kB will
most certainly do the same. Is it really that much?

The worst thing is lack of stability in application's work. I can set
MSDE2000 server params and it works. After some time user calls me and
says it does not work. I can't really check if he's not installed any
other apps that could change memory settings, but I really do not
suspect that's the case.
Can Win98 really reserve memory for sql server or it will give memory
as soon something else will ask for it? Any clues on Win98 settings
that could help?

Hope you had great New Year's Eve ;)
Regards, Andrzej

"Michael Rys [MSFT]" <no**********@microsoft.com> wrote in message news:<#L**************@TK2MSFTNGP12.phx.gbl>...

Bryant is correct.

However 16MB should still allow a fairly big XML document be parsed with
OpenXML. How big is the file/are the files and can you release the memory as
early as possible (for example, copy your data into temp tables and release
the handle).

Best regards
Michael

PS: Yes, the missing MSXML2 from all SP3/SP3a versions of SQLServer 2000 is
unfortunate. You can install the MSXML2 version distributed with SP2 or
request a version from your PSS support.

"Bryant Likes" <br****@suespammers.org> wrote in message
news:OL**************@TK2MSFTNGP12.phx.gbl...

"Andrzej" <th***@wp.pl> wrote in message
news:d8**************************@posting.google.c om...
[snip]

However on Win98 I need to set 'min server memory' value to 128
otherwise even small OPENXML statement, which is first in the
application lifecycle (so I could not have missing sp_xml_removedocs
statments before) ends up with an error 'server memory low, use
sp_xml_removedocument ...'.

This isn't a definite answer, but I would assume the problem is with how
much memory Windows 98 is allocating to SQL Server. In the
sp_xml_preparedocument documentation you will find that "[t]he MSXML

parser

uses one-eighth the total memory available for SQL Server." This means

that

sp_xml_preparedocument has access to only one-eighth of the total memory
assigned to SQL Server. So even if you have all 128 assigned to SQL

Server,

you still only get 16 MB available. If Windows 98 gives you less than 128
(which I would assume it is doing) then you will get even less.

--
Bryant

[Michael Rys [MSFT]]

Re: Joins with OpenXML generating "internal SQL Server error": This is an
error that should have been fixed in one of the SPs. If not, feel free to
post or send me a repro case...

OpenXML should have no problem with XML documents up to about a 100kB.
Beyond that it becomes a question of load and available memory. And it
should not trigger an error. Can you send me a repro and the version of MSDE
2000 and MSXML that you are using to repro it??

I know that you are using Win98 which is on the way out and may introduce
some other special problem, but I would like to have our test team
investigate this a bit more...

Thanks
Michael

"Andrzej" <th***@wp.pl> wrote in message
news:d8**************************@posting.google.c om...

Cheers for the answers,

I release XML handles as soon as I copy stuff to temp tables (I do it
for some time now, since I had 'internal sql server error's when I
performed joins on OPNXML statemens, but that's diferent story), so no
room for improvement here. I have no concurrent users, so XML parser
works only with one doc at a time (at least I suspect I does).

XML data comes from DataSet object and is not even big. Even XML
having 1,17kB can trigger an error, and XML data having 5,61kB will
most certainly do the same. Is it really that much?

The worst thing is lack of stability in application's work. I can set
MSDE2000 server params and it works. After some time user calls me and
says it does not work. I can't really check if he's not installed any
other apps that could change memory settings, but I really do not
suspect that's the case.
Can Win98 really reserve memory for sql server or it will give memory
as soon something else will ask for it? Any clues on Win98 settings
that could help?

Hope you had great New Year's Eve ;)
Regards, Andrzej

"Michael Rys [MSFT]" <no**********@microsoft.com> wrote in message

news:<#L**************@TK2MSFTNGP12.phx.gbl>...

Bryant is correct.

However 16MB should still allow a fairly big XML document be parsed with
OpenXML. How big is the file/are the files and can you release the memory as early as possible (for example, copy your data into temp tables and release the handle).

Best regards
Michael

PS: Yes, the missing MSXML2 from all SP3/SP3a versions of SQLServer 2000 is unfortunate. You can install the MSXML2 version distributed with SP2 or
request a version from your PSS support.

"Bryant Likes" <br****@suespammers.org> wrote in message
news:OL**************@TK2MSFTNGP12.phx.gbl...

"Andrzej" <th***@wp.pl> wrote in message
news:d8**************************@posting.google.c om...
[snip]
> However on Win98 I need to set 'min server memory' value to 128
> otherwise even small OPENXML statement, which is first in the
> application lifecycle (so I could not have missing sp_xml_removedocs
> statments before) ends up with an error 'server memory low, use
> sp_xml_removedocument ...'.

This isn't a definite answer, but I would assume the problem is with how much memory Windows 98 is allocating to SQL Server. In the
sp_xml_preparedocument documentation you will find that "[t]he MSXML

parser

uses one-eighth the total memory available for SQL Server." This means

that

sp_xml_preparedocument has access to only one-eighth of the total memory assigned to SQL Server. So even if you have all 128 assigned to SQL

Server,

you still only get 16 MB available. If Windows 98 gives you less than 128 (which I would assume it is doing) then you will get even less.

--
Bryant

[Andrzej]

Michael,
Since the time I started using temp tables instead of performing joins
on OPENXML I haven't received any 'internal SQL errors', so I guess
I'm not having a repro case there.

Let's get back to the main issue.
I receive an error on Win98 with less than 128MB mem. When I set 'min
server memory' to 128 I can get it to work

MSDE 2000 ver. 8.00.760
MSXML2 ver. 8.30.9523.0

It's hard to give you an exact repro, but I could send you xml and
stored proc that generates an error, but I cannot send you the whole
application. I can tell that on XP during normal work it consumes
around 60MB (a can't tell how much will that be on Win98). However
I've seen error on 256MB machine with Win98 and no other apps working.

I also suspect, that my .NET application takes up a lot of memory
since garbage collector does not seem to free it as soon as possible,
but 'set working set size' SQL setting should reserve memory for
sqlserver. However I still get the error.

Hope this gives you more info,
rgds, Andrzej

"Michael Rys [MSFT]" <no**********@microsoft.com> wrote in message news:<OW**************@TK2MSFTNGP11.phx.gbl>...

Re: Joins with OpenXML generating "internal SQL Server error": This is an
error that should have been fixed in one of the SPs. If not, feel free to
post or send me a repro case...

OpenXML should have no problem with XML documents up to about a 100kB.
Beyond that it becomes a question of load and available memory. And it
should not trigger an error. Can you send me a repro and the version of MSDE
2000 and MSXML that you are using to repro it??

I know that you are using Win98 which is on the way out and may introduce
some other special problem, but I would like to have our test team
investigate this a bit more...

Thanks
Michael

"Andrzej" <th***@wp.pl> wrote in message
news:d8**************************@posting.google.c om...

Cheers for the answers,

I release XML handles as soon as I copy stuff to temp tables (I do it
for some time now, since I had 'internal sql server error's when I
performed joins on OPNXML statemens, but that's diferent story), so no
room for improvement here. I have no concurrent users, so XML parser
works only with one doc at a time (at least I suspect I does).

XML data comes from DataSet object and is not even big. Even XML
having 1,17kB can trigger an error, and XML data having 5,61kB will
most certainly do the same. Is it really that much?

The worst thing is lack of stability in application's work. I can set
MSDE2000 server params and it works. After some time user calls me and
says it does not work. I can't really check if he's not installed any
other apps that could change memory settings, but I really do not
suspect that's the case.
Can Win98 really reserve memory for sql server or it will give memory
as soon something else will ask for it? Any clues on Win98 settings
that could help?

Hope you had great New Year's Eve ;)
Regards, Andrzej

[Michael Rys [MSFT]]

Thanks. Please send it the data and stored proc to my work email (mrys at
microsoft dot com) and I will follow-up with my people.

The internal sever error may have been an error that was fixed in SP1. Is
your MSDE version SP3 or SP3a?

Thanks
Michael

"Andrzej" <th***@wp.pl> wrote in message
news:d8**************************@posting.google.c om...

Michael,
Since the time I started using temp tables instead of performing joins
on OPENXML I haven't received any 'internal SQL errors', so I guess
I'm not having a repro case there.

Let's get back to the main issue.
I receive an error on Win98 with less than 128MB mem. When I set 'min
server memory' to 128 I can get it to work

MSDE 2000 ver. 8.00.760
MSXML2 ver. 8.30.9523.0

It's hard to give you an exact repro, but I could send you xml and
stored proc that generates an error, but I cannot send you the whole
application. I can tell that on XP during normal work it consumes
around 60MB (a can't tell how much will that be on Win98). However
I've seen error on 256MB machine with Win98 and no other apps working.

I also suspect, that my .NET application takes up a lot of memory
since garbage collector does not seem to free it as soon as possible,
but 'set working set size' SQL setting should reserve memory for
sqlserver. However I still get the error.

Hope this gives you more info,
rgds, Andrzej

"Michael Rys [MSFT]" <no**********@microsoft.com> wrote in message

news:<OW**************@TK2MSFTNGP11.phx.gbl>...

Re: Joins with OpenXML generating "internal SQL Server error": This is an error that should have been fixed in one of the SPs. If not, feel free to post or send me a repro case...

OpenXML should have no problem with XML documents up to about a 100kB.
Beyond that it becomes a question of load and available memory. And it
should not trigger an error. Can you send me a repro and the version of MSDE 2000 and MSXML that you are using to repro it??

I know that you are using Win98 which is on the way out and may introduce some other special problem, but I would like to have our test team
investigate this a bit more...

Thanks
Michael

"Andrzej" <th***@wp.pl> wrote in message
news:d8**************************@posting.google.c om...

Cheers for the answers,

I release XML handles as soon as I copy stuff to temp tables (I do it
for some time now, since I had 'internal sql server error's when I
performed joins on OPNXML statemens, but that's diferent story), so no
room for improvement here. I have no concurrent users, so XML parser
works only with one doc at a time (at least I suspect I does).

XML data comes from DataSet object and is not even big. Even XML
having 1,17kB can trigger an error, and XML data having 5,61kB will
most certainly do the same. Is it really that much?

The worst thing is lack of stability in application's work. I can set
MSDE2000 server params and it works. After some time user calls me and
says it does not work. I can't really check if he's not installed any
other apps that could change memory settings, but I really do not
suspect that's the case.
Can Win98 really reserve memory for sql server or it will give memory
as soon something else will ask for it? Any clues on Win98 settings
that could help?

Hope you had great New Year's Eve ;)
Regards, Andrzej