title: SQL Query FOR XML runs fine in 2000, slow in 2008 R2
body: <p>I'm converting a client's SSIS packages from DTS to SSIS. In one of their packages they have an execute SQL task that has a query similar to this:</p>
  
  <pre><code>SELECT * FROM [SOME_TABLE] AS ReturnValues 
  ORDER BY IDNumber
  FOR XML AUTO, ELEMENTS
  </code></pre>
  
  <p>This query seems to return in a decent amount of time on the old system but on the new box it takes up to 18 minutes to run the query in SSMS. Sometimes if I run it it will generate an XML link and if I click on it to view the XML its throwing a 'System.OutOfMemoryException' and suggests increasing the number of characters retrieved from the server for XML data. I increased the option to unlimited and still getting error.</p>
  
  <p>The table itself contains 220,500 rows but the query rows returned is showing 129,810 before query stops. Is this simply a matter of not having enough memory available to the system? This box has 48 GB (Win 2008 R2 EE x64), instance capped to 18GB because its shared dev environment. Any help/insight would be greatly appreciated as I don't really know XML!</p>