How to clear SQL Server Cache ?

CONCLUSION :
1. Normally, use following two when you do not want query compilation also to
come into picture. 
CHECKPOINT
DBCC DROPCLEANBUFFERS
2. When you want query compilation also to come into picture,  free the proccache also :

CHECKPOINT
DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

3. The most comprehensive cleaning strategy would be :

DBCC FREESYSTEMCACHE(All)
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC FLUSHPROCINDB( db_id )
CHECKPOINT
DBCC DROPCLEANBUFFERS

=============================================================



From : http://sqlserverpedia.com/wiki/SELECT_DISTINCT

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

NOTE: Do not run the aforementioned DBCC commands on a production server

MSDN : DBCC DROPCLEANBUFFERS (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms187762.aspx

Removes ALL CLEAN BUFFERS from the buffer pool.

MSDN recommends to use CHECKPOINT before DROPCLEANBUFFERS.  AS you can see, DROPCLEANBUFFERS will drop only clean buffers, what about dirty buffers ?

clean buffers  - memory and disk data is matching

dirty buffers  - updated data present in memory, not written to disk

CHECKPOINT forces data of dirty pages to be written to disk, and makes it clean.

REMEMBER that CHECKPOINT only works on CURRENT DATABASE.

So next question : What is the difference between DROPCLEANBUFFERS and

FREEPROCCACHE ?

See http://mssqlonline.blogspot.com/2007/03/stored-procedure-clean-cache-and-clean.html for answer.

It says "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."

FREEPROCCACHE clears cached execution plans of SPs.

MSDN says http://msdn.microsoft.com/en-us/library/ms174283.aspx

Use DBCC FREEPROCCACHE to clear the plan cache carefully. Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache.

Now another question : What are clean , dirty and cold buffer caches ?

CLEAN and DIRTY are well explained above. Not much clear about cold,

refer here for some explanation :

http://blogs.msdn.com/b/psssql/archive/2009/03/17/sql-server-what-is-a-cold-dirty-or-clean-buffer.aspx

It says :

The cold buffer cache has a couple of variants within SQL Server.    Typically it refers to the buffer pool right after restart.   The data cache is not loaded (cold) and requires physical reads to populate the cache.   This is often termed the RAMPUP phase.     Internally it can also refer to the free list activity.   SQL Server understands the time of last access to buffers and when appropriate takes this into account when handling the free buffer lists.   Buffers on the free list that have some age to them may be termed cold because the age increases the chance that they accessible with a near memory transfer from a location such as CPU cache.

As per Pinal Dave http://blog.sqlauthority.com/2008/07/22/sql-server-clear-sql-server-memory-caches/

The following commands clean only the plan cache:
DBCC FREESYSTEMCACHE(All | pool_name)
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC FLUSHPROCINDB( db_id )
To clear the data buffer use the following command:
DBCC DROPCLEANBUFFERS
For more information visit to : http://blog.sqlauthority.com/2007/03/23/sql-server-stored-procedure-clean-cache-and-clean-buffer/