sql server: clean database pages from buffer

Question

Chirag Sachdeva 301

How to clean all database pages from the buffer.

I ran below commands
checkpoint
dbcc dropcleanbuffers

and still when I am reading some data from the database it is showing logical reads means pages read are from memory not from disk. Please help.

Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator

2021-02-06T10:35:58.187+00:00

Please include the upper part of the query window, so that we have an idea of what statements you ran.
Dan Guzman 9,411 Reputation points

2021-02-06T13:19:52.17+00:00

showing logical reads means pages read are from memory not from disk.

Pages are always read from memory and reflected in logical reads. The physical read counters are independent of logical reads, counting pages read from storage because they were not already in buffer cache memory.
AmeliaGu-MSFT 14,011 Reputation points Microsoft External Staff

2021-02-08T09:20:10.85+00:00

Hi ChiragSachdeva-6150,

Could you please share us the query you ran?
And quoting from this similar thread which might help:
logical reads = accesses to data in cache

Clearing the cache forces a physical read so you can have logical reads. You can not have zero logical reads (for the pedants, except where a logical read means rows but that is out of scope here)

In addition, you can verify that there are no pages in memory for the database in question using the code from this blog.

Best Regards,
Amelia

Your answer

Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator

2021-02-06T10:35:58.187+00:00

Please include the upper part of the query window, so that we have an idea of what statements you ran.
Dan Guzman 9,411 Reputation points

2021-02-06T13:19:52.17+00:00

showing logical reads means pages read are from memory not from disk.

Pages are always read from memory and reflected in logical reads. The physical read counters are independent of logical reads, counting pages read from storage because they were not already in buffer cache memory.
AmeliaGu-MSFT 14,011 Reputation points Microsoft External Staff

2021-02-08T09:20:10.85+00:00

Hi ChiragSachdeva-6150,

Could you please share us the query you ran?
And quoting from this similar thread which might help:
logical reads = accesses to data in cache

Clearing the cache forces a physical read so you can have logical reads. You can not have zero logical reads (for the pedants, except where a logical read means rows but that is out of scope here)

In addition, you can verify that there are no pages in memory for the database in question using the code from this blog.

Best Regards,
Amelia