How to flush query results from cache/memory

Question

How to flush query results from cache/memory

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281344 More actions · Answer 1

Check out this blog on the topic.

http://blog.sqlauthority.com/2007/05/03/sql-server-dbcc-commands-to-free-several-sql-server-memory-caches/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

There are two things that can cause the first run to be slower:

- Fetching of the data from disk rather than memory. Counter with CHECKPOINT and DBCC DROPCLEANBUFFERS

- Generation of the execution plan. Counter with DBCC FREEPROCCACHE

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150468 More actions · Answer 3

I usually use:

CHECKPOINT;

DBCC DROPCLEANBUFFERS;

DBCC FREESYSTEMCACHE('ALL');

Since you seem to be looking to measure physical reads, it can be useful to perform the tests with and without read-ahead enabled. You can turn off read-ahead with trace flag 652:

DBCC TRACEON (652);

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi