Reports running slow
What can i do in this part of query to improve overall peformance of the reports.
@SQLkiwi, attached as mentioned in comment.
@Aaron attached is the actual execution plan as well, thank you.
In particular, the 14,466,991 rows coming from the Table2.Index 4 Seek are filtered down (as Aaron mentions in his comment to the question) to just 421 rows by a residual predicate on the Nested Loops Join immediately above it:
There's no way to know from an anonymized plan what that ScalarString28 predicate is, but you should look into it. It may be that you could adjust the indexing on Table2 to allow/encourage the optimizer to push that predicate further down.
It is also possible that the entire subtree shown above is built on a faulty assumption. It is relatively common for subtrees below a Top operator (as shown above) to cause problems when the distribution of data is unfavourable. The optimizer makes special adjustments below operations like Top, to optimize for finding a certain number of rows quickly.
You might like to try executing the query with the documented and supported hint OPTION (QUERYTRACEON 4138) to disable these 'row goal' adjustments to see if it results in a better-performing plan. See https://support.microsoft.com/en-us/kb/2667211 for more information. Note this trace flag is really intended to help diagnose the reasons for poor plan selection rather than providing a 'fix'.
At which point, I notice you are using SQL Server 2005, which means that hint and trace flag will not be effective for you, sorry. I'll leave the advice there for other readers. There's no simple way I can think of right now to turn off the 'row goal' behaviour for you, so concentrate on the first issue I raised instead.
Update based on actual plan
The second uploaded plan has a different shape from the first one, meaning a recompilation has occurred:
The estimate on Table1 makes me think that table might be a table variable. If so, consider using a #temporary table instead. These typically provide better cardinality information to the optimizer than table variables, and can also support automatically-created statistics. Once you have done that, it is quite likely the rest of the plan shape will change again, based on the better information provided to the optimizer.
You should still review your indexing on Table 5 to include Column14 so a Key Lookup is avoided.