Inconsistent behaviour of FOR XML AUTO?

I’ve recently had to fix what seemed like a very strange problem with SQL Server, where very occasionally the system would stop working but would eventually fix itself. The system in question runs under a single identity, all access to the database goes via this "gateway" identity. Under particular circumstances the users of the system were getting incorrect results. The underlying stored procedure producing the incorrect results uses FOR XML AUTO to send back an XML result set. However, when I profiled the SQL and re-ran the same query in Management Studio it produced the correct results. I believe the problem was based upon the following facts;
  1. When the system runs it runs under identity A, therefore any stored procedure executed will have a specific plan created for identity A – "plan A". When I run the query as myself I get – "plan Me"
  2. The query uses FOR XML AUTO but does not have an ORDER BY
  3. FOR XML AUTO relies on the order to create the parent/child relationships
  4. Plans change depending upon the exact circumstances of the specific call, i.e. statistics, calling arguments, user id, etc

So what happended? Well I believe the problem was the "plan A" was constructing its query in a way that results in a different ordering of the results, I’ve certainly seen this happen in other queries (especially when attempting to CAST data). "Plan Me" was then using the natural (and more typical) order of how the data was added. This would explain why the XML results were different. This would also go a long way to explaining why after a reset of SQL or a DBCC FreeProcCache the system would start working again. This is because the plans would have to be re-created and I’d see the correct results since normally "plan A" would be exactly the same as "plan me" and therefore the system would work.

 

So the lessons learnt – 1) Always supply an ORDER BY for XML AUTO 2) When you’re testing for inconsistent results, use the same user id.

Advertisements
This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s