XML-based query extremely slow through ADO.NET, instant through SSMS

Question

I'm in the perennial situation: a query that runs instantly through SSMS with a handful of reads, but slow enough to time out with thousands of reads when run through ADO.NET. Unlike the other questions I could find on StackOverflow, clearing the query cache (or forcing myself to use the one SSMS uses) does not seem to be doing the trick.

Generally, when others have reported this situation on StackOverflow, they've had corrupt query caches. In all of these cases, the fix has been either to run the ADO.NET queries with SET ARITHABORT ON (to match the session settings being used by SSMS) or to run DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE to force the query cache to rebuild. These techniques make no difference in my application, making me believe that there's something more fundamental going on.

The query in question is this (actual verbatim query captured by SQL Profiler, cleaned up only for formatting):

declare @p5 xml
set @p5=convert(xml,N'<r>
<n v="66ebc21b3bcb31e9a5ecbfb4b29fd2a47c37994c"/>
<n v="665919306fb23d9e685638a2d199e1e623745305"/>
<n v="a080c3b4e0c86e37b4d494d5efc09cebe20c6929"/>
<n v="245cb49bdeca9e37ef9bbd55877e21ade14e6282"/>
<n v="297650a6be65be332c1bb2aab426331a156ee342"/>
<n v="6a2668c8ab64fecf3b6925c7be613c61cef4dd7c"/>
<n v="09923f25f8b1de19f693bca1111bfa50d617856e"/>
<n v="0a7836d8e4e34f4ea92b2105eea5a99029949428"/></r>')
exec sp_executesql N'
            SELECT ixChangesetTag, ixRepo, ixChangeset, sTag, fBookmark
            FROM ChangesetTag
              INNER JOIN @p2.nodes(''/r/n'') X(n) ON X.n.value(''xs:hexBinary(@v)'', ''binary(20)'') = ixChangeset
            WHERE ixRepo = @p0 AND ixCustomer = @p1',N'@p0 bigint,@p1 int,@p2 xml',@p0=2,@p1=23363,@p2=@p5

(The XML parameter is in order to allow using a parameterized query where I'd normally have trouble doing so, since the number of objects I want to pass in varies. Table-valued procedures would be the 2008 way to do this, but some of our customers run on 2005.)

Run through SSMS, the actual query plan used looks appropriate (index seeks), and takes about 200 reads over 4ms. Run through the web application, it takes about 4500 reads over a second.

What am I missing here? Could something be reinstating the bad query plan when run through the web application, despite the DBCC calls and ARITHABORT settings?

Answer 1

Easy fix would be to put a multi-column index on (ixCustomer, ixRepo, ixChangeset). Without knowing what the columns actually are, whether they are unique etc its hard to come up with a better answer.

Answer 2

The problem ended up being that SQL Server was generally picking an absolutely horrible execution strategy, basically looping over the XML repeatedly, rather than doing a sane join. The fix was to put the XML into a temporary table and join on that instead, which reliably yielded good execution plans.