December 26, 2012 at 9:34 am
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.
December 26, 2012 at 9:52 am
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/
December 26, 2012 at 12:56 pm
It'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.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 27, 2012 at 2:42 am
GilaMonster (12/26/2012)
The optimiser is not allowed (in most cases) to spend minutes optimising a query.
Not allowed means? what i think is optimizer will take time to generate plan regardless of time boundation.please explain
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 27, 2012 at 3:02 am
ok
December 27, 2012 at 3:04 am
Bhuvnesh (12/27/2012)
what i think is optimizer will take time to generate plan regardless of time boundation.
You would be thinking incorrectly then. Google: optimiser 'reason for early termination'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 27, 2012 at 3:33 am
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.
December 27, 2012 at 3:40 am
dbasql79 (12/27/2012)
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.
That only logs complete clearing of the cache, not if single plans are aged out or invalidated.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 27, 2012 at 5:04 am
GilaMonster (12/27/2012)
Bhuvnesh (12/27/2012)
what i think is optimizer will take time to generate plan regardless of time boundation.You would be thinking incorrectly then. Google: optimiser 'reason for early termination'
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 ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 27, 2012 at 5:13 am
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?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 27, 2012 at 5:21 am
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;-)
December 28, 2012 at 1:35 am
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;-)
December 28, 2012 at 2:17 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 28, 2012 at 3:25 am
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;-)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply