- 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"
- The query uses FOR XML AUTO but does not have an ORDER BY
- FOR XML AUTO relies on the order to create the parent/child relationships
- 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.
IT Bytes 