OPENXML Remote Scan Performance

Question

I'm looking for some advice on OPENXML. Specifically, the performance. I am seeing very slow performance on a very small piece of XML.

Something about this is causing a Remote Scan. Any ideas on how to go about tuning it?

 DECLARE @idoc int
 EXEC sp_xml_preparedocument @idoc OUTPUT, @ResourceXML

 DECLARE @tmpRes TABLE (ResourceID int, Quantity int, FormID int)

 INSERT INTO @tmpRes (ResourceID, Quantity, FormID)
 SELECT  TMP.ID, TMP.Q, RESC.FormID 
 FROM  OPENXML(@idoc, '/Resources/R') WITH (ID int, Q int) TMP 
 INNER JOIN dbo.tblResources RESC ON TMP.ID = RESC.ResourceID
 WHERE  RESC.OrgID = @OrgID

Answer 1

Remote Scan is what the optimizer uses to access the XML data as OPENXML is actually external to the database engine - so that bit is normal.

However there does seem to be an issue with the statistics that the optimizer uses, this article here discusses that, offers some pointers, but I don't know whether this helps your particular situation.