Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Stored Procedure looses execution plan. Expand / Collapse
Author
Message
Posted Thursday, December 27, 2012 5:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
GilaMonster (12/27/2012)
Bhuvnesh (12/27/2012)
will this option also return value when first time exec plan get created ?


Yes

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 ?


No. Did you do the research?

yes i got few links from "Grant Fritchey" like http://www.scarydba.com/2010/11/18/reason-for-early-termination-of-statement/ but still i have these two questions.



-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1400573
Posted Friday, December 28, 2012 1:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
Another question,

When select shows the “Reason For Early Termination” is “Time Out.” 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) ?



-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1400825
Posted Friday, December 28, 2012 2:17 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 42,844, Visits: 35,972
Bhuvnesh (12/28/2012)
So here the chosen plan is the stored plan (stored at the time of previous runs) ?


Huh? I already said that the optimiser will not return the previous cached plan (if one exists) if it terminates early.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1400836
Posted Friday, December 28, 2012 3:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
GilaMonster (12/28/2012)
I already said that the optimiser will not return the previous cached plan (if one exists) if it terminates early.
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.” ?


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1400852
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse