d_uvarajan (11/9/2013)
Attached the execution plan. Please refer "Query 54" in the plan.
Query 54 is 0% of the cost of the batch. Why do you think it's the problem?
Can you go through that procedure (as described in the article Jeff referenced), identify exactly which queries are the problems and upload the plans of just those queries. It will involve running them one by one.
Also, we need an actual plan (as the article explains), not an estimated plan. You attached an estimated plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability