WangcChiKaBastar (12/26/2012)
when I run the proc tomorrow it again takes forever to complete...and the applications time out on the front end...to resolve the time out I have to manually go to SSMS run the stored proc..and then applications have no issue for the rest of the day.
my question is why is the Query execution plan getting flushed/lost next day ?
the data doesnt change much and not at all some times....
Check Sql Error log to find what cleared your plan cache.There you can see some error messages like below.
QL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to "" Operation.