SQL Server 2000 Performance Guidelines - SQL XML
From Guidance Share
- J.D. Meier, Srinath Vasireddy, Ashish Babbar, Sharon Bjeletich and Alex Mackman
Avoid OPENXML over Large XML Documents
Be aware that there are limitations to the amount of memory that is available to the OPENXML construct over an XML document operation. This operation builds a Document Object Model (DOM) in the SQL buffer space that can be much larger than the original document size. Also, this operation is limited to one eighth of the buffer space, and large XML documents may consume this memory fairly quickly and cause an out-of-memory error on the server. Do not create large systems based on this functionality without conducting significant load testing. You might also want to use the XML bulk load option if possible.
Avoid Large Numbers of Concurrent OPENXML Statements over XML Documents
You also have to consider the issue with OPENXML when you use OPENXML to batch inserts. This is a fairly common operation because it is an effective way to issue a group of inserts with one statement. Issuing a group of inserts reduces the overhead of multiple insert statements and multiple round trips. However, be aware that this approach may not be very scalable because of the aforementioned memory limitations.
For more information about OPENXML, see "Using OPENXML" on MSDN at http://msdn.microsoft.com/library/en-us/xmlsql/ac_openxml_1cx8.asp.