Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

How to optimize the openxml queries showing remote scan Expand / Collapse
Posted Monday, June 22, 2009 11:42 PM

SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 13, 2014 12:27 PM
Points: 267, Visits: 386
We have a 5000+ lines SP (i know what you will say).
We have found that one of the batch takes 58% of the resources .
The reason is that the output is 1 row but the estimated rows that the optimizer assumes is 10000 and hence it is doing a remote scan .

this part of the SP captures only the XML file and does not use any table inside the database so that we can check for indexes isssue .So its nto using any table .

We tried hints like top 1 or fast 1 etc .This makes optimizer to estimate only one row by force but the subtree cost is still same .

I have also applied CU8 (Its SQL Server 2005 SP2) but still its behaving the same .

Any help is much appreciated .


Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
Post #739917
Posted Thursday, August 22, 2013 1:00 PM


Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 195, Visits: 823
Hi ,I have similer problem,Could you solve it?
Post #1487464
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse