How to flush query results from cache/memory

  • Hello:

    Using SQL Server 2005 Std., 32-bit, Win2003

    I have some rather simple join queries that I've been creating. I've noticed that the first time I run them they will take almost a minute to run. Then, after that they run in just a few seconds.

    I'm assuming this is because the data is memory resident? I'm trying to simulate worst case testing (on test server) and want to force the data retrieval to do I/O operations.

    How do I flush the data from memory, or force it to re-retrieve data from pages on hard drive?

    Thanks!

  • 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

  • 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
  • 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);

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

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