April 1, 2010 at 2:25 pm
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!
April 1, 2010 at 2:29 pm
Check out this blog on the topic.
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
April 2, 2010 at 3:21 am
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
April 2, 2010 at 7:46 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply