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')
Is there anything I can do to reduce the grant?
With a quick look at your xpath expression
//wobble 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) 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 (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.
<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>
//wobble you will get
(//wobble) would only give you
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') from @wibble.nodes('//*[wobble]') as T(X) for xml path('');
//*[wobble] gives you all nodes that has a
wobble node and
wobble 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') OPTION (MAX_GRANT_PERCENT = 0);
Documented here:- https://support.microsoft.com/en-us/kb/3107401