Simple XML .query SELECT statement times out on 1 SQL SERVER instance

Posted on

Question :

I dropped and recreated a stored procedure on a DB on a SQL Server 2012 instance (SERVER1/INSTANCE1), after which the sproc started hanging on a line. Using profiler I have reduced a recreateable scenario on this SQL Server instance to the following code:

DECLARE @RequestXML xml = '<Foo>
    <Node1>Hello</Node1>
    <Node2>World</Node2>
    <Node3>Today</Node3>
</Foo>'

SELECT
    @RequestXML.query('/Foo/Node1[1]')
    ,@RequestXML.query('/Foo/Node2[1]')
    ,@RequestXML.query('/Foo/Node3[1]') -- comment out any of these lines so only one or two items are returned and the query works fine!

This statement hangs. If you comment out one of the returned columns in the SELECT statement, then the statement runs fine. So one of two returned columns is fine, but anything over is bad. If you take out the first node selector (i.e. the [1]) it also runs fine.

Here’s the rub though:

  • the statement fails on any DB in this SQL Server 2012 instance (SERVER1/INSTANCE1).
  • the statement succeeds on any other SQL Server instance I have tried, including other instances running from the same SQL Server machine as the failing instance (e.g. SERVER1/INSTANCE2, SERVER1/INSTANCE3 etc.) – all of which are 2012 instances.

It seems to me that SERVER1/INSTANCE1 has borked the way it runs this statement. I have heard that SQL Server does things with optimising execution of statements, but my knowledge stops there. I’m sure there must be some way to get it to behave again, but how?

UPDATE

The following adjustment (getting the singleton of the Xpath result-set rather than potentially multiple first elements) solves the issue above for the SERVER1/INSTANCE1 issue. I would put this as the answer, except that I don’t believe this is identifying the underyling problem (please correct me if I’m wrong!). Given that our codebase deployed on other servers implements the above statement (or derivatives of it) I don’t want to update our entire shredding XML approach without good reason (or at least I would have to justify it to my peers).

DECLARE @RequestXML xml = '<Foo>
    <Node1>Hello</Node1>
    <Node2>World</Node2>
    <Node3>Today</Node3>
</Foo>'

SELECT
    @RequestXML.query('(/Foo/Node1)[1]')
    ,@RequestXML.query('(/Foo/Node2)[1]')
    ,@RequestXML.query('(/Foo/Node3)[1]')

Any help gratefully received.

Thanks,

Ali

Answer :

I’m not sure if it answers your question, but I had a similar problem. The resulution was limiting SQL Server to execute the query using one processor only.

If you have the same problem, this should work:

DECLARE @RequestXML xml = '<Foo>
    <Node1>Hello</Node1>
    <Node2>World</Node2>
    <Node3>Today</Node3>
</Foo>'

SELECT
    @RequestXML.query('/Foo/Node1[1]')
    ,@RequestXML.query('/Foo/Node2[1]')
    ,@RequestXML.query('/Foo/Node3[1]') 
OPTION(MAXDOP 1)

The thing is that SQL Server Query Optimizer decides that the query is costly enough to split its execution and merge those results. And sometimes this creates problems, so lowering degree of parallelism (MAXDOP) can solve it.

EDIT

Looking at the execution plan for this query, I find it hard to believe that somewhere in the middle of execution of each branch for @RequestXML.query('...'), estimated number of rows was 180,000. However, when I told it that I promise I will need only the first Foo, he reduced it to 900. So, try this…

DECLARE @RequestXML xml = '<Foo>
    <Node1>Hello</Node1>
    <Node2>World</Node2>
    <Node3>Today</Node3>
    <Node4>Tomorrow</Node4>
</Foo>'

SELECT
    @RequestXML.query('/Foo[1]/Node1[1]')
    ,@RequestXML.query('/Foo[1]/Node2[1]')
    ,@RequestXML.query('/Foo[1]/Node3[1]')
    ,@RequestXML.query('/Foo[1]/Node4[1]')

The problem could be related to available memory.

If you dig into the query plan (I am using SQL 2012, so it may vary for you) you will see this MemoryGrantInfo section for your query:

<MemoryGrantInfo SerialRequiredMemory="1536" SerialDesiredMemory="11253216" RequiredMemory="12192" DesiredMemory="11263872" RequestedMemory="189696" GrantWaitTime="0" GrantedMemory="189696" MaxUsedMemory="2024" />

That is 189696 KB (or about 180 MB) of memory for that tiny query.

The version you have already come up with that uses the () singleton selection has a much nicer memory grant:

<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />

I don’t have access to the inner workings to see why this happens, but it is always best to be as specific as possible when querying XML data – in this case ensuring that you specify a singleton query is the key.

If you have the opportunity to refactor the code, this might be a more efficient way of doing that:

SELECT
     f.c.query('(Node1)[1]')
    ,f.c.query('(Node2)[1]')
    ,f.c.query('(Node3)[1]')
FROM @RequestXML.nodes('Foo') f(c)

I can’t reproduce on my SQL 2012 instance Microsoft SQL Server 2012 (SP1) – 11.0.3128.0 (X64). Can you confirm versions are up-to-date and the same on your servers?

Leave a Reply

Your email address will not be published.