Monday, December 13, 2010

Sql Server: Clean Query Plans and Buffer Pool before Query Performance Comparison

If you want to optimize your queries and  execute different quires to compare their performance. Performance comparison is only possible when
        I.            Already NO query plan exists for this query and
      II.            NO query related data pages are in cache.
Always use following database consistency checker statements before your query execution on testing server (NEVER USE IT ON PRODUCTION SERVER) .
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE will remove all elements from the plan cache. And DBCC DROPCLEANBUFFERS removes all clean buffers from the buffer pool.

No comments:

Post a Comment

All suggestions are welcome