InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 21:28:49 GMT20http://www.sqlservercentral.com/Forums/Topic1400285-392-1.aspx[quote][b]GilaMonster (12/28/2012)[/b][hr]I already said that the optimiser will not return the previous cached plan (if one exists) if it terminates early.[/quote] So does it mean that here this option only depicts the state of currently generated plan whether its case of "timed-out", “Memory Limit Exceeded.” OR “Good Enough Plan Found.” ?Fri, 28 Dec 2012 03:25:57 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1400285-392-1.aspx[quote][b]Bhuvnesh (12/28/2012)[/b][hr]So here the chosen plan is the stored plan (stored at the time of previous runs) ?[/quote]Huh? I already said that the optimiser will not return the previous cached plan (if one exists) if it terminates early.Fri, 28 Dec 2012 02:17:17 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1400285-392-1.aspxAnother question,When select shows the [b]“Reason For Early Termination” is “Time Out.”[/b] then optimizer simply stopped trying and takes the plan that is currently the best, which might be a good plan… or it might not be, and applies that plan to the query. So here the chosen plan is the stored plan (stored at the time of previous runs) ?Fri, 28 Dec 2012 01:35:34 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1400285-392-1.aspx[quote][b]GilaMonster (12/27/2012)[/b][hr][quote][b]Bhuvnesh (12/27/2012)[/b][hr]will this option also return value when first time exec plan get created ? [/quote]Yes[quote]is this option gives the value of 'reason for early termination' when query tried to use same plan for last time ? like if we get "timed out" , does that mean that query coudnt able to generate new plan and used plder one ?[/quote]No. Did you do the research?[/quote] yes i got few links from "Grant Fritchey" like [url]http://www.scarydba.com/2010/11/18/reason-for-early-termination-of-statement/[/url] but still i have these two questions.Thu, 27 Dec 2012 05:21:05 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1400285-392-1.aspx[quote][b]Bhuvnesh (12/27/2012)[/b][hr]will this option also return value when first time exec plan get created ? [/quote]Yes[quote]is this option gives the value of 'reason for early termination' when query tried to use same plan for last time ? like if we get "timed out" , does that mean that query coudnt able to generate new plan and used plder one ?[/quote]No. Did you do the research?Thu, 27 Dec 2012 05:13:57 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1400285-392-1.aspx[quote][b]GilaMonster (12/27/2012)[/b][hr][quote][b]Bhuvnesh (12/27/2012)[/b][hr]what i think is optimizer will take time to generate plan regardless of time boundation.[/quote]You would be thinking incorrectly then. Google: optimiser 'reason for early termination'[/quote] will this option also return value when first time exec plan get created ? is this option gives the value of 'reason for early termination' when query tried to use same plan for last time ? like if we get "timed out" , does that mean that query coudnt able to generate new plan and used plder one ?Thu, 27 Dec 2012 05:04:48 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1400285-392-1.aspx[quote][b]dbasql79 (12/27/2012)[/b][hr]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.[/quote]That only logs complete clearing of the cache, not if single plans are aged out or invalidated.Thu, 27 Dec 2012 03:40:32 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1400285-392-1.aspx[quote][b]WangcChiKaBastar (12/26/2012)[/b][hr]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....[/quote]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.Thu, 27 Dec 2012 03:33:25 GMTSQL Showhttp://www.sqlservercentral.com/Forums/Topic1400285-392-1.aspx[quote][b]Bhuvnesh (12/27/2012)[/b][hr]what i think is optimizer will take time to generate plan regardless of time boundation.[/quote]You would be thinking incorrectly then. Google: optimiser 'reason for early termination'Thu, 27 Dec 2012 03:04:00 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1400285-392-1.aspxokThu, 27 Dec 2012 03:02:00 GMTKum@R marlapatihttp://www.sqlservercentral.com/Forums/Topic1400285-392-1.aspx[quote][b]GilaMonster (12/26/2012)[/b][hr]The optimiser is not allowed (in most cases) to spend minutes optimising a query.[/quote] Not allowed means? what i think is optimizer will take time to generate plan regardless of time boundation.please explainThu, 27 Dec 2012 02:42:39 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1400285-392-1.aspxIt's probably got nothing whatsoever to do with the cached execution plan. The optimiser is not allowed (in most cases) to spend minutes optimising a query.What's probably causing the slow-down is having to re-load the data required for the procedure into the data cache. I'd guess that something like index rebuilds, checkDB or large data loads are done at night, hence displacing the data cache.The solution here is to tune the query, it's probably requiring more data than it should due to inefficient queries or poor indexing and you mainly see the effects of that when the data has to be pulled from disk.Wed, 26 Dec 2012 12:56:50 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1400285-392-1.aspx[quote][b]WangcChiKaBastar (12/26/2012)[/b][hr]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.[/quote]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.Wed, 26 Dec 2012 09:52:36 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1400285-392-1.aspxI 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.Wed, 26 Dec 2012 09:34:54 GMTWangcChiKaBastar