WangcChiKaBastar (12/26/2012)
I have a stored procedure and when I run it First time it creates an execution plan and it takes long time to complete which is understandable after the first time when I run it second time, it works perfectly...now here is the issue... If I run the proc lets say today manually then there is no issue rest of the day and application accessing it don't complain about timeout issues...
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...
SP uses Temp table and correlated subquery.
That is the nature of execution plans. They can and will be removed from the cache when sql needs the room for something else. If this data is pretty constant maybe you could use a table to hold the results instead of running your proc repeatedly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/