Thanks for your valuable suggestions...I didn't create a covered index as the number of columns in include statement are more( & table has around 40 million records), didn't want to spend too much time/resources in maintaining index's. I forced the query to use an already existing index(using with index statement) which prevents table scans. Is it advisable to force the query to use an index. I guess sql server should already choose best execution plan based on available indexes right??

I also see Hash Match's in my execution plan..What are these??

So analysing the elapsed time is the only option to decide whether an index is helpful??? (not sure how accurate it is as the SP can be cached)

P.S: elapsed time decreased considerably(almost decreased to 1/4th) after making these changes, but higher than what it used to be in the past(3-4 months ago when the query was running faster)...Not sure what changes happened but it forced me to analyse execution plan

These queries are used for our reporting purposes(to insert data into Reporting tables)