May 22, 2011 at 6:57 pm
Is it possible to force a query plan to stay in memory so that it is not flushed? I have seen query hints for forcing a plan but i want to make sure that the plan is always there in memory? Can i just point to an xml saved on the server?
May 22, 2011 at 11:36 pm
No you can't. If the query is frequently run, it won't be aged out, but there are things that will invalidate plans, there are things that will clear the entire proc cache, etc.
Why are you considering forcing a plan at all? It's pretty much a last resort for fixing query problems.
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
May 23, 2011 at 1:08 am
For what reason are you looing for this option? performance?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 23, 2011 at 9:24 am
Me too, I was finding that many query plans are being compiled ad hoc.
(57% of batch requests result in compilation)
I would like if the server reused more my plans.
Any suggestions?
May 23, 2011 at 9:26 am
ehdium-drivers (5/23/2011)
Me too, I was finding that many query plans are being compiled ad hoc.(57% of batch requests result in compilation)
I would like if the server reused more my plans.
Any suggestions?
Are you using dynamic sql? Parametirezed plans get reused but dynamic sql pretty much wipes out that option.
May 23, 2011 at 9:40 am
Dynamic SQL - that is application's fault.
Is there anything that can be done by the DBA in the meanwhile?
May 23, 2011 at 9:50 am
Besides asking for rewrite or maybe more hardware (if under realllllllllllllllll y high memory pressure) I don't see what you can do.
May 23, 2011 at 10:02 am
ehdium-drivers (5/23/2011)
Me too, I was finding that many query plans are being compiled ad hoc.(57% of batch requests result in compilation)
Can you maybe start a new thread rather than hijacking this one? There may be things that can be done, but it depends on the version of SQL and will be a fair bit of discussion.
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
May 23, 2011 at 11:31 am
Thanks everyone. The issue which i am trying to trouble shoot is not for an application but just for a long-running job. I do now where the issue is.But just to keep the show running , i was wondering if i can capture execution plan,store itsomewhere and next time when the job runs if i can pinup this captured plan to that sql so that it doesn't create a new one.
May 23, 2011 at 11:41 am
sqldba_icon (5/23/2011)
Thanks everyone. The issue which i am trying to trouble shoot is not for an application but just for a long-running job. I do now where the issue is.But just to keep the show running , i was wondering if i can capture execution plan,store itsomewhere and next time when the job runs if i can pinup this captured plan to that sql so that it doesn't create a new one.
If the distribution of your data has changed and statistics updated, you may very well get a better plan when the new one is generated. Since it's a long running job, I'm assuming that it isn't run back to back rapid fire so I don't see that compile time would be an issue.
What problem exactly are you trying to solve?
Todd Fifield
May 23, 2011 at 11:50 am
This job runs once everyday. I have nailed it down to a select statement which has the issue. So if i run this select first time, it will take 45 mins and then i run in few mins it will take only few secs reason is because the plan is cache. Now for time being i want this plan to stay in memory so that when the job runs next day it doesn't have to recreate the plan. Makes sense?
May 23, 2011 at 11:59 am
sqldba_icon (5/23/2011)
So if i run this select first time, it will take 45 mins and then i run in few mins it will take only few secs reason is because the plan is cache.
Are you absolutely sure it's because of the plan being cached and not the data being cached? 45 minutes is far beyond a reasonable compile time, I've never seen the optimiser take more than 5 minutes and that was for a horrendous mess of nested views.
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
May 23, 2011 at 12:09 pm
GilaMonster (5/23/2011)
sqldba_icon (5/23/2011)
So if i run this select first time, it will take 45 mins and then i run in few mins it will take only few secs reason is because the plan is cache.Are you absolutely sure it's because of the plan being cached and not the data being cached? 45 minutes is far beyond a reasonable compile time, I've never seen the optimiser take more than 5 minutes and that was for a horrendous mess of nested views.
5 minutes? :blink::blink:
The worse I've seen is 0.5 sec and that was for a 3000 dynamic series of statements... 15 queries and 90 tables.
I can't imagine what would take 5 minutes to compile :w00t:
May 23, 2011 at 12:14 pm
GilaMonster (5/23/2011)
sqldba_icon (5/23/2011)
So if i run this select first time, it will take 45 mins and then i run in few mins it will take only few secs reason is because the plan is cache.Are you absolutely sure it's because of the plan being cached and not the data being cached? 45 minutes is far beyond a reasonable compile time, I've never seen the optimiser take more than 5 minutes and that was for a horrendous mess of nested views.
Well if if run dbcc dropcleanbuffers and the run the select it takes 45 mins. May be i should run dbcc freeproccache and then try to run the select.
May 23, 2011 at 12:16 pm
sqldba_icon (5/23/2011)
GilaMonster (5/23/2011)
sqldba_icon (5/23/2011)
So if i run this select first time, it will take 45 mins and then i run in few mins it will take only few secs reason is because the plan is cache.Are you absolutely sure it's because of the plan being cached and not the data being cached? 45 minutes is far beyond a reasonable compile time, I've never seen the optimiser take more than 5 minutes and that was for a horrendous mess of nested views.
Well if if run dbcc dropcleanbuffers and the run the select it takes 45 mins.
DropCleanBuffers clears the data cache, not the plan cache.
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
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply