What is SQL Server commands to clear caches before running a performance comparison?
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? How sql server clear cache? Remove all elements from the plan cache for the entire instance. DBCC FREEPROCCACHE; Use this to clear the plan cache carefully. Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. 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:
Remove all elements from the plan cache for the entire instance. DBCC FREEPROCCACHE; Use this to clear the plan cache carefully. Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. 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 clean (unmodified) pages from the buffer pool
- Precede that with a CHECKPOINT to flush any dirty pages to disk first
- DBCC FLUSHPROCINDB clears execution plans for that database
- Also see (on DBA.SE)
- Solving high page load time using SQL Profiler