openxml performance

We do lot of xml parsing (OpenXML) and uses SQL Server 2k for our application. Recently we are getting the following error.
SEVERE: Exception in DB call com.jnetdirect.jsql.m: XML document could not
be created because server memory is low. Use sp_xml_removedocument to
release XML documents.
We have little more load on our server in the past few weeks. We found few SPs are missing "sp_xml_removedocument" and we added. In general are there any tips on using ? do any of the following helps?
making the xml size small by keeping the tags small etc., or
splitting larger xml documents into multiple documents and parsing
any others please let me know.
is there anyway to monitor the memory used by sql server for openxml?
Thanks.
Making the XML smaller might help a little but not a lot. Splitting the
file into smaller files might actually make things worse - there's some
overhead for each DOM created so having the same amount of data spread
around to more DOM's would use more memory. This may help if the smaller
size means the documents get processed enough faster so the total amount of
memory used at a time is reduced. The best thing to do is to reduce the
amount of time the DOM is in memory by rearranging the logic. There are
some cases where increasing memory to leave helps but I don't know if it
will help in your case.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"siddu" <siddu@.discussions.microsoft.com> wrote in message
news:637129A8-A7C5-4C1D-9D27-A9E75640D025@.microsoft.com...
> We do lot of xml parsing (OpenXML) and uses SQL Server 2k for our
> application. Recently we are getting the following error.
> SEVERE: Exception in DB call com.jnetdirect.jsql.m: XML document could not
> be created because server memory is low. Use sp_xml_removedocument to
> release XML documents.
> We have little more load on our server in the past few weeks. We found few
> SPs are missing "sp_xml_removedocument" and we added. In general are there
> any tips on using ? do any of the following helps?
> making the xml size small by keeping the tags small etc., or
> splitting larger xml documents into multiple documents and parsing
> any others please let me know.
> is there anyway to monitor the memory used by sql server for openxml?
> Thanks.
|||In addition to Roger's response:
Making sure that sp_xml_removedocument is always called is the most
important way to keep memory usage low.
The next step is to avoid having the DOM loaded. One way to achieve this is
to do all OpenXML calls as soon as possible (if needed loading data into a
temp table/table variable) and call sp_xml_removedocument early.
Best regards
Michael
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OyYRlTyWEHA.556@.tk2msftngp13.phx.gbl...
> Making the XML smaller might help a little but not a lot. Splitting the
> file into smaller files might actually make things worse - there's some
> overhead for each DOM created so having the same amount of data spread
> around to more DOM's would use more memory. This may help if the smaller
> size means the documents get processed enough faster so the total amount
> of memory used at a time is reduced. The best thing to do is to reduce
> the amount of time the DOM is in memory by rearranging the logic. There
> are some cases where increasing memory to leave helps but I don't know if
> it will help in your case.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "siddu" <siddu@.discussions.microsoft.com> wrote in message
> news:637129A8-A7C5-4C1D-9D27-A9E75640D025@.microsoft.com...
>