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.
Advertisements

Posted on December 13, 2010, in Performance Tuning. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: