May 15, 2015 at 2:16 am
Hello,
I have a question, more for general knowledge.
Is there a way the sql cache may be manipulated?
Thank you so much for any reply!
May 15, 2015 at 2:31 am
Which cache? There are several of them
All caches can be explicitly cleared, other than that what kind of manipulation are you interested in?
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 15, 2015 at 2:35 am
Thank you for the reply!
I was thinking more in terms of queries or stored procedures, to be able to store a certain plan of execution in the cache and force that plan to be used.
May 15, 2015 at 2:57 am
Kinda, but not really. You can force a plan, the USE PLAN hint, but it doesn't mean that it'll use a specific plan in cache, rather that the optimiser will optimise until it finds that specified plan
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 15, 2015 at 3:05 am
Thank you so much for the reply! I will search a bit for the HINTs and test on some of my queries.
May 15, 2015 at 3:14 am
Don't.
Hints are for rare cases. They're for when you absolutely are sure you know better than the query optimiser on the best plan for a query and you know that the plan you force will ALWAYS be the best plan. The optimiser has no ability to override hints, if your hint results in a very poor plan, you'll get a very slow query. If the hint isn't usable, your query will throw an error.
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 15, 2015 at 6:06 am
There are a couple of small ways you can manipulate plan cache though. You can drop an individual plan from cache by passing the plan handle. You can also avoid having plan be cached, so that you always get a compile through using a hint, WITH RECOMPILE. Turning on 'Optimize For Ad Hoc Workload' will cause plan stubs to be stored on an initial execution and full plans on subsequent executions. But, those are all very tiny and minor manipulations.
Note: I agree with Gail, hints are an absolute last resort. The question is, what problem are you attempting to resolve?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 15, 2015 at 6:14 am
Thank you for both your replies!
I am not attempting to solve a actual situation at this point. It was more of a curiosity, maybe to be implemented, to know if its possible to optimise the performance of an application by analysing the best execution plan, forcing it, and avoiding or eliminating the optimizer's job, and so gaining some time there. But, at this point I am thinking as the environment changes in time, the best execution plan may also differ in time, so maybe not that good to force a plan, as Gail said.
May 15, 2015 at 6:21 am
You can't avoid the optimiser. Even the USE PLAN hint requires that the optimiser runs the optimisation process until it finds the plan specified. Hence it can actually increase the time needed to optimise the query.
As for eliminating the optimisation time, you may be looking in the wrong place. Fixing indexing and/or queries can easily improve performance by multiple orders of magnitude. Removing the optimisation process might save maybe a second or two on the first execution of the query (the one that generates the plan which is then cached for reuse)
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 15, 2015 at 6:45 am
What Gail says ( pretty much what I always say).
The way I usually put it, build your structures and write your queries with the fact that there is an optimization process. You can't avoid it. Plus, it's actually pretty awesome at it's job. If you then attempt to write your code such that you're taking the optimizers behaviors into account, you'll better be able to have it work for you than against you. It will also make it much easier to identify those rare occasions where you do need to take some control away with a hint.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 15, 2015 at 9:19 pm
GilaMonster (5/15/2015)
You can't avoid the optimiser. Even the USE PLAN hint requires that the optimiser runs the optimisation process until it finds the plan specified. Hence it can actually increase the time needed to optimise the query.As for eliminating the optimisation time, you may be looking in the wrong place. Fixing indexing and/or queries can easily improve performance by multiple orders of magnitude. Removing the optimisation process might save maybe a second or two on the first execution of the query (the one that generates the plan which is then cached for reuse)
+1000.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 22, 2015 at 2:07 am
I have actually set up a maintenance plan, for indexes and statistics, that works pretty well. And I don't really have performance issues at this point. But as I was working on that maintenance plan, I was thinking about performance and the question about the way query plan, popped into my mind.
But thank you so much for all your replies and for clarifying things for me, in that regard.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply