Iam getting the same error when using SQL user even

Thanks for the reply, Physical memory is 128Gb out of which 114gb was set as max memory to SQL. We can't remove Distinct or Top1 as the query is inbuilt executing...

any update on the result

please find the query and plan

Iam getting the results with in 38sec upto table n-1,if I include tablen then it takes 3mins for the output.
eventhough the structure of tablen-1 and table n are same

The query is like below

select application1,application2............... from table1 inner join table2, inner join table3,........, inner join table n where psteid='10000004'

How to purge Historical data from a Database

I want to delete old data and please let me know how to partition tables with detailed steps from start to end.

Thanks all,Can you explain with an example to calculate index size and how much space is needed for...

Thank you, could you also let me know how to calculate index size and how much space is needed for index drive?

Thanks,
Can you explain with an example