January 13, 2012 at 1:59 am
I have a report that runs twice per day, its expensive, when cached its much quicker, however the plan regularly dissapears from the cache. The server is not restarted or rebooted, and there is plenty of memory available. Perhaps 5% of the underlying data rows may be changed over the course of a week, but the plan is rarely used more than twice (suggesting some daily job may clear it?)
What kind of things cause a plan to be dropped?
And is there any way to set a cached plan's expiry time, or hint to keep it longer
January 13, 2012 at 2:11 am
Are we talking about a Reporting Services report ?
January 13, 2012 at 2:14 am
No, its not reporting services. Its a complex stored procedure.
January 13, 2012 at 2:27 am
The plan probably gets aged out of cache because it doesn't get used often. There's no way to force it to stay in cache.
Why is the plan being removed an issue?
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
January 13, 2012 at 2:51 am
They want it to run in a 5-minute slot, during a break when production activity is very low, and complete before production resumes. It usually finishes within 1 minute, or 30 seconds if cached. But sometimes things overrun. I'm looking at other ways to improve performance - keeping the plan cached is just one possible way I've identified.
Coming from the asp.net world, I can set cache expiry times for individual items. I guess this is not configurable in sql server. 🙁
January 13, 2012 at 3:14 am
SQL uses a variety of the LRU algorithm for the plan cache, so less-used plans will be aged out and removed.
I suppose you could schedule a job that ran the proc every 10 min or so. Probably not the best solution though.
Before you go too far down that though, check how long the compile really takes. The optimiser's not allowed to take that log to optimise queries (about 20 sec is the longest I've heard of and that was view upon view upon view upon view
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
January 13, 2012 at 3:10 pm
Would an hint like USE PLAN or KEEPFIXEDPLAN help ?
January 13, 2012 at 3:27 pm
No. UsePlan does not disable the optimiser and KeepFixedPlan is either for schema invalidation or statisics invalidation (forget which and not checking now)
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
January 13, 2012 at 3:43 pm
GilaMonster (1/13/2012)
No. UsePlan does not disable the optimiser and KeepFixedPlan is either for schema invalidation or statisics invalidation (forget which and not checking now)
I think USE PLAN might do it.
From the BOL (http://msdn.microsoft.com/en-us/library/ms181714.aspx) :
USE PLAN N'xml_plan'
Forces the query optimizer to use an existing query plan for a query that is specified by 'xml_plan'. For more information, see Specifying Query Plans with Plan Forcing. USE PLAN cannot be specified with INSERT, UPDATE, MERGE, or DELETE statements.
KEEPFIXED PLAN
Forces the query optimizer not to recompile a query due to changes in statistics. Specifying KEEPFIXED PLAN makes sure that a query will be recompiled only if the schema of the underlying tables is changed or if sp_recompile is executed against those tables.
January 13, 2012 at 3:48 pm
Yes, use plan forces the optimiser to use a specific plan form (it does allow a little leeway), but that is not Tom's problem, he want to stop the optimiser from compiling on each run (or stop the plan from aging out of cache), not to make the procedure use a specific plan form (which is what useplan is for).
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
January 13, 2012 at 4:05 pm
GilaMonster (1/13/2012)
he want to stop the optimiser from compiling on each run (or stop the plan from aging out of cache)
He wants that because it's much quicker when the plan doesn't have to be recompiled.
And :
- as you said, SQL uses a variety of the LRU algorithm for the plan cache, so less-used plans will be aged out and removed. So there is not much to be done here.
- if the report has to be run in a 5-minute slot, during a break when production activity is very low, it's not possible to run more frequently the stored procedure so that the plan won't get out of the cache
- if the plan is stable and using 'USE PLAN' has the same effect as stopping the optimiser from compiling on each run
it still looks like a good solution to me 🙂
January 13, 2012 at 4:06 pm
GilaMonster (1/13/2012)
The optimiser's not allowed to take that log to optimise queries (about 20 sec is the longest I've heard of and that was view upon view upon view upon view
I've had 20 minutes once. Yes, twenty minutes.
It was a huge query on a EAV database (37 LEFT JOINs IIRC). Something else must have been wrong with the instance anyway, because that absurdly long compile time was not the only weird symptom. I cycled the instance and the query started behaving surprisingly fast for such a dumb DB model (it "only" took 1 minute to execute).
Once refactored the view to a PIVOT, the execution time dropped to 30 ms (or was it 300 ms?? I don't remember) and I was never able to make the 20 minutes compile time happen again, even using the original ugly code.
However, this edge case apart, I must have a 700 Kb .sql file lying around somewhere that consistently took over 1 minute to parse.
Would be interesting to know if there's a parse timeout that can be set or a maximum timeout that the optimizer can't go over.
-- Gianluca Sartori
January 13, 2012 at 4:12 pm
azdzn (1/13/2012)
- if the plan is stable and using 'USE PLAN' has the same effect as stopping the optimiser from compiling on each runit still looks like a good solution to me 🙂
No, use plan does not have the same effect as stopping the compiler from compiling on each run. In fact, it may even increase compile time. The plan that comes out will be consistent every time, but since plan inconsistency is not the problem here and compile time is, this is unlikely to have the desired effect.
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
January 13, 2012 at 4:16 pm
Gianluca Sartori (1/13/2012)
Once refactored the view to a PIVOT, the execution time dropped to 30 ms (or was it 300 ms?? I don't remember) and I was never able to make the 20 minutes compile time happen again, even using the original ugly code.
I wonder how much of that was parse (syntax check) and bind (locate, check and link objects) and how much of it was compile. Still hellishly impressive.
There are no max settings that I know of, and the time the optimiser has is a function of how complex the query is, so, there probably isn't a max limit other than a practical one.
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
January 14, 2012 at 7:25 am
giving more memory to sql server procedure cache in order to avoiding Memory page out of cache.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply