Steve McRoberts-357330 (10/19/2009)

---

Please see the warning from Mauve, above, regarding its allocation of 1/8 of the available memory.

me

---

I think this is the thing that causes the confusion: sp_xml_preparedocument

So that's just saying that when you use the XML parser to open a document for use with OPENXML, up to one-eighth of SQL Server memory is potentially available to Msxmlsql.dll - should it need it.

This is the first MSFT answer I found with Google to back that up: MSDN Forums Thread[/url].

My impression (though the method has so far escaped deprecation) is that XML DML methods/XQuery/XPath/FOR XML and the gang are to be preferred to using sp_xml_preparedocument and OPENXML wherever possible. There are many difficulties associated with OPENXML so personally I avoid it.

To be clear, all that applies specifically to prepare/remove document and OPENXML. XML variables, columns and so on allocate memory in the usual fashion. That applies to the XML portion of the query optimizer too. One interesting thing about xml variables (like all the MAX datatypes when used as variables) is that they require tempdb workspace.

Many of the XML features use streaming interfaces to minimize memory consumption - bulk loading XML is a good example of this. See XML Support in Microsoft SQL Server 2005

me again

---

Yes the wording could be better. I refreshed my memory on the detail last night (one of Ken Henderson's excellent books) so I just want to add:

SQL Server uses MSXML and the DOM to process documents you load via sp_xml_preparedocument. It restricts the virtual memory MSXML can use for DOM processing to one-eighth of the physical memory on the machine or 500MB, whichever is less.

It's unlikely that MSXML would be able to get 500MB of virtual memory since SQL Server reserves most of the user mode address space for use by the buffer cache. MSXML allocates memory via the multi-page allocator (the so-called MemToLeave region) which has a default maximum size of 256MB. Since that region is used for all sorts of stuff (by SQL Server as well) it's likely to be much less than that.

MSXML is limited to 500MB of virtual memory regardless of the amount of memory on the machine because SQL Server calls the GlobalMemoryStatus Win32 API function to determine the amount of available physical memory. On machines with more than 4GB of physical memory, GlobalMemoryStatus can return incorrect information, so Windows returns a -1 to indicate an overflow. The Win32 API function GlobalMemoryStatusEx exists to address this shortcoming, but SQLXML does not call it. :laugh:

One last thing that I had forgotten - MSXML had an ordinal-only DLL entry point added specifically for SQL Server to allow it to set the maximum virtual memory to use.