SQL Server / XML.query() causes massive memory grant

The following (on my systems, tested on SQL Server 2016 and 2019 std) produces a massive memory grant request (3327 MB)

declare @wibble xml = '<wibble><wobble>1</wobble></wibble>'

select @wibble.query('//wobble[1]')

Is there anything I can do to reduce the grant?

https://www.brentozar.com/pastetheplan/?id=HkezxS8-K

With a quick look at your xpath expression //wobble[1] and the test data you have you are looking for the first wobble you can find.

One could even think that your expression is equal to this (//wobble)[1] which with your test data gives the same result. Using the parenthesis like that you will get the first wobble from the XML document and there will be no memory grant for the query.

So, as you might know using //wobble[1] (without the parenthesis) is not the same at all. This query will look at all elements in the document and if there are a wobble element in there it will return the first one and concatenate the result with all elements.

For example.

<wibble>
  <wobble>1</wobble>
  <wobble>2</wobble>
  <wobble>3</wobble>
  <wibble>
    <nowobblehere>X</nowobblehere>
    <wobble>A</wobble>
    <wobble>B</wobble>
    <wobble>C</wobble>
  </wibble>
</wibble>

Using //wobble[1] you will get

<wobble>1</wobble>
<wobble>A</wobble>

Where using (//wobble)[1] would only give you <wobble>1</wobble>.

If you really need the concatenated result you could shred your nodes and then rebuild them using for xml path to get a smaller memory grant. For me it requests 1MB instead of 280MB. Still with a warning of excessive memory grant.

select T.X.query('wobble[1]')
from @wibble.nodes('//*[wobble]') as T(X)
for xml path('');

//*[wobble] gives you all nodes that has a wobble node and wobble[1] gives you the first wobble in that node.

Consider hinting the query with MAX_GRANT_PERCENT:-

DECLARE @wibble XML = '<wibble><wobble>1</wobble></wibble>';

SELECT @wibble.query('//wobble[1]')
OPTION (MAX_GRANT_PERCENT = 0);

Documented here:- https://support.microsoft.com/en-us/kb/3107401