June 23, 2008 at 12:56 am
We can remove all plans from cache using DBCC FREEPROCCACHE but the same can be executed by only users from sysadmin roles.
How the same can be executed by other roles?
Also can be force sql server not to cache plan for a particulat proc?
Cheers
SID
June 23, 2008 at 5:21 am
You can force a procedure to drop it's plan for each execution with the RECOMPILE keyword.
This is almost always a bad idea though. It can sometimes be ok for some troubleshooting, but you want procedures to cache their plans. Why are you trying to clear the plan cache regularly?
June 23, 2008 at 7:45 am
We tried with Recomplie option but the same is not removing the plan from cache. Could you please suggest what could be the reason for the same?
Another way could be using sp_recompile system proc but for the same users should be in the role of sysadmin which is not the case.
June 23, 2008 at 7:52 am
RECOMPILE does not remove the plan from the CACHE, it just does not use the cached plan when the procedure runs.
Again I will ask, why would you want to continuously clear the procedure cache? It is usually not a good idea.
June 23, 2008 at 8:10 am
The proc cache is your bestest buddy. Why on earth do you want it cleared out all the time? What problem are you trying to solve?
"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
June 23, 2008 at 8:22 am
I don't know about best buddy.
I would ask the plan cache to help me move a couch, but I don't think I would ask it to take me to the airport - that would be a little weird.
June 23, 2008 at 8:29 am
Yes, that's true. It won't help you bury the bodies. In fact, it usually digs them up, like a dog. Traitors!
"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
June 24, 2008 at 5:13 am
Let me phrase the problem once more. We are facing issues with performance because there are too many things in procedure cache. So to boost up performance, we need to find a way to clear the cache.
To solve the same, the following could be the possible algorithm to achieve the same:-
1: The cache plans are not been used or unnecessarily and new cache plans are being generated. (when cache will get a hit and when it will miss the same)
2: Read the contents of cache and define an algorithm which stuff to be removed from cache.
3: Design and define what should go into cache and what should not.
Please let me know your views on the same.
June 24, 2008 at 5:21 am
I have to believe you have mis-diagnosed your performance problem.
I do not even think it is possible to see any performance degredation because there is too much in the plan cache. If SQL Server wants to cache something and it needs space, it will push out older plans automatically, so there is always room to cache plans when SQL needs it.
Perhaps you could tell us some of the symptoms that you are experiencing that have brought you to this conclusion.
June 24, 2008 at 5:23 am
There's not really fine-grained control that will allow you to manipulate the cache in that manner. I have heard of a number cache problems with the release version and SP1. Are you running SP2?
If you're getting that much processing into the cache, I assume you're using a lot of dynamic SQL? Have you looked at using parameterized queries instead of straight dynamic SQL? That will ensure better performance from your cache.
Other than that, I hope others have more useful suggestions.
"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
June 24, 2008 at 5:27 am
I got your point and I will try to get more info on the same. Thanks for the Input. I am wondering how I could miss that point 🙂
June 24, 2008 at 5:32 am
Michael Earl (6/24/2008)
I do not even think it is possible to see any performance degredation because there is too much in the plan cache.
Pre SP2 on SQL 2005 there was a 'bug' with memory limits that let the proc cache get way, way too big. It only manifested itself on really big servers, 64bit, 20GB+ memory and lots of ad-hoc code. The limits were changed in SP2 and it's no longer a problem.
I'd also like to know what kind of symptons you're seeing.
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
June 24, 2008 at 5:36 am
Yes I agree that DB will manage automatically what to store in cache and what not so there is a very rare chance that performance issues are there because of cache. The only probelm could be if there are lot of dynamic sql being used because in that case cache must be getting full and DB must be replacing stuff from cache to accomodate new ones. I am investigating the same will the team and will get back on the same.
June 25, 2008 at 5:37 am
We have sp2 on the box. When team execute DBCC FreeProcCache the problem get solved. Is there any way using which users who are not in sysadmin roles can execute the same?
June 25, 2008 at 5:53 am
You really shouldn't be flushing the cache regularly.
What are the symptoms of the problem that you are seeing?
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 16 total)
You must be logged in to reply to this topic. Login to reply