Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When comparing the execution time of two different queries, it's important to clear the cache to make sure that the execution of the first query does not alter the performance of the second.

In a Google Search, I could find these commands:

DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE

In fact, my queries are taking a more realistic time to complete after several executions than before. However, I'm not sure this is the recommended technique.

What's the best practice?

share|improve this question

migrated from stackoverflow.com Jan 17 '12 at 17:49

This question came from our site for professional and enthusiast programmers.

add comment

2 Answers

up vote 17 down vote accepted

Personally, for a common query the 2nd and subsequent executions matter more.
Are you testing disk IO or query performance?

Assuming your query runs often and is critical, then you want to measure that under real life conditions. And you don't want to clear prod server caches each time...

If you want, you can

  • DBCC DROPCLEANBUFFERS clears buffer pool
  • DBCC FLUSHPROCINDB clears execution plans for that database

Also see (on DBA.SE)

share|improve this answer
    
+1 for combination of those 2 DBCC commands, and for relevance of "hot cache". –  AdaTheDev Nov 28 '11 at 12:46
1  
+1 Add a CHECKPOINT before the DBCC DROPCLEANBUFFERS to ensure any dirty pages are written out too. Drop clean buffers only drops...well you know. –  Paul White Dec 13 '12 at 20:43
add comment

I was always told to use:

dbcc dropcleanbuffers;

From MSDN:

Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.

To drop clean buffers from the buffer pool, first use CHECKPOINT to produce a cold buffer cache. This forces all dirty pages for the current database to be written to disk and cleans the buffers. After you do this, you can issue DBCC DROPCLEANBUFFERS command to remove all buffers from the buffer pool.

share|improve this answer
    
Plus: DBCC FREEPROCCACHE to clear any cached execution plans... –  marc_s Nov 28 '11 at 12:40
    
Only if you want to test IO, surely... –  gbn Nov 28 '11 at 12:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.