Sorry for the previous reply.The plan were indeed from different databases. I have corrected it. One plan is taken after some time when the indexing on DB was complete, while...

Attached the execution plan for both the cases. in one plan ,the query uses nested loop and in other plan it uses hash join for the same query.

Buffer hits were 99% when i saw it from performance monitor. But how to see the stats on plan reuse.

Moreover i am getting 'Appdomain unloaded due to memory pressure' as...

The DB Server is having 8 GB RAM, SQL Server is allowed to take as much ram it can take. As the load increases on Server, it takes upto 7.81...

The query is using hardcoded ID as the only parameter.(where ID=123). There are inner join and left join in it. Select statement involve some case statement as well.

Its returning the...