January 17, 2002 at 2:35 pm
This little goodie clears the procedure cache correct?
January 17, 2002 at 2:39 pm
DBCC FREEPROCCACHE clears out the procedure cache, that is correct. If you do a before and after on the syscacheobjects table in the master database you'll see the effect.
DBCC DROPCLEANBUFFERS clears out the buffer cache in much the same fashion.
I'm working on an article which discusses these a little with respect to stored procedures and caching. But it's going slow!
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 18, 2002 at 10:11 am
Thanks that was this what I was looking for! Now to figure out if sp_recompile is a better option in my case then DBCC FREEPROCCACHE since the DBCC here "...will clear all plans out of cache and could have an adverse impact on performance."
January 18, 2002 at 11:33 am
You also have the option of executing using the WITH COMPILE if you're executing the stored procedure manually. Here's the excerpt from Books Online:
quote:
You can force the stored procedure to be recompiled by specifying the WITH RECOMPILE option when you execute the stored procedure. Use this option only if the parameter you are supplying is atypical or if the data has significantly changed since the stored procedure was created.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply