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') ,@RequestXML.query('/Foo/Node2') ,@RequestXML.query('/Foo/Node3') -- 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
) 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?
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)') ,@RequestXML.query('(/Foo/Node2)') ,@RequestXML.query('(/Foo/Node3)')
Any help gratefully received.