We have a 5000+ lines SP (i know what you will say:-D).
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 .
Regards
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)