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!