Flushing the data cache

[Souvik]

Hi,
Regarding the dbcc dropcleanbuffers method, I see that inspite of running it, my physical reads are 0 & all the data is still read logically. I wanted to know if I can clean the all the pages & read from the db file on the disk & then wanted to measure the statistics for that.

Thanks again,

 

[tlbroadbent]

Issue a checkpoint command before executing the DBCC commands. This will cause unwritten buffers to be written to disk and then the DBCC can clear them.

When you run the query and analyze the result, make sure you also consider the read-ahead reads as well as the physical reads. The number of logical reads will not change but the number of physical reads and read-ahead reads will change when the cache is cleared. Read-ahead reads are "Number of pages placed into the cache for the query" and will provide a more accurate picture of the I/O required to run the query.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm