Paul White (8/5/2009)

---

Jeff Moden (8/5/2009)

---

As a side bar... There is a rumor (and I STRESS the word RUMOR!) I heard and I can't confirm it or deny it. The rumor says that when you use any of the XML extensions in SQL Server, that it automatically allocates at least 1/8th of the available memory to the task. Does anyone know of a reference that can confirm or deny that rumor?

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

Paul