Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Aug 6 '13 at 13:43

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 3 down vote accepted

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]')
share|improve this answer
    
Hey thanks for that - I have run it with initial success, but on repeated calls it eventually hits a point where it hangs again. –  alergy Aug 6 '13 at 10:27
    
The problem I have is this is our long standing way of shredding XML, run in multiple SQL server instances, and we've never had this as an issue. –  alergy Aug 6 '13 at 10:28
    
Has your hardware changed maybe? I guess that someone could have decided to throw in extra cores to improve the performance. I'm just guessing now, but it's probable that SQL Server first decides to execute query on one processor core and next time on the other. First check the CPU usage. –  Ozren Tkalčec Krznarić Aug 6 '13 at 10:46
1  
See updated answer –  Ozren Tkalčec Krznarić Aug 6 '13 at 10:57
2  
Execution plan of this query is even better: SELECT @RequestXML.query('(/Foo/Node1)[1]') –  GriGrim Aug 6 '13 at 11:21

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.

share|improve this answer
    
Thanks for the info - that is not good memory usage, and probably helps explain the issue at hand. The refactor does seem to be working, and any improvement through refactoring is good. –  alergy Aug 8 '13 at 9:03

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?

share|improve this answer
    
The instances are not the latest version - they are 11.0.2100. (x64) - I'll ask our IT team to update the dev servers to SP1. In the interim I have refactored the query as has been suggested (and detailed in my OP edit). Thanks! –  alergy Aug 8 '13 at 9:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.