Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache. If observing through SQL Profiler, one can watch the Cache Remove events occur as DBCC FREEPROCCACHE goes to work. DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run.
Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. DBCC DROPCLEANBUFFERS serves to empty the data cache. Any data loaded into the buffer cache due to the prior execution of a query is removed.
DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERS
Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL , BOL
pinaldave
extremely helpful in understanding and solving the problems. Thanks for your utmost skill in this field.
Good info! I wonder though if it is possible to specify for an index/table to not cache? or alternatively remove from cache a certain object?
We have a log table which does not get queried at all, but it appears that primary key index of this table takes 56% of Buffer Cache.
Thanks.
andre.
if you have tables which dont get queried, will it not be better if you do not have any index on those tables?
How do we use the command? in the SQL or query? in the php page itself? im confused!
I have a stored procedure that calls another stored procedure in turn. The inner procedure returns an integer value using a SELECT SCOPE_IDENTITY().
The outer procedure also returns an integer value using SELECT SCOPE_IDENTITY().
Obviously the client is going to receive multiple result sets. Is there a way to “clear” the earlier resultsets and send only the last result set to the client ?
You are Great!!!!!!!!!!!Bhaji.I am your Great Great Fan…
Would you not be required to call the CHECKPOINT command before calling DROPCLEANBUFFERS?