Estimated way too less compared to Actual no of rows
Even after making the stats up to date by running frag and stats job, the attached query takes more than a min to fetch 4K records
What could be the reason here for mismatch of estimnate vs actual? Or is there a way i can improve this query to complete much faster as is expcted to run in 10-15 seconds.
The mismatches between estimates and actuals are expected, given the size and complexity of the plan. Any cardinality estimation errors tend to grow as the plan becomes more complex, because the estimate from one operation is the input to the next.
You want to rethink the use of table-valued functions as well. These get a fixed guess at cardinality, and do not support statistics.
As far as performance is concerned, one issue is again the size of the plan. Humans and computers alike struggle to work with things that big. If you can break the query into smaller, more easily analysed parts, I would encourage you to do so.
As it is, I would look first at the scans (and seeks) with residual predicates (not seek predicates). These are tests that are applied to a full scan, or on the results of a seek operation. In many cases, your query is reading a much larger number of rows from the table or index than is shown as the operator's output.
For example, a Clustered Index Scan on "Object9.Index14" is reading 47 million rows, but passing on only 14,000. That is the effect of the predicate identified as ScalarString87. The solution depends on exactly what that predicate is of course. Perhaps you are missing an index, or are using a predicate test that cannot be used to seek.
There are many other issues in the plan, but that is the first thing that jumped out at me.
You should upgrade your version of Plan Explorer by the way.