Home > Uncategorized > Using DBCC to create fair performance tests on stored procedures

Using DBCC to create fair performance tests on stored procedures

When tweaking stored procedures, to make them run faster, we often compare them against a benchmark,
say SP_A vs SP_B.
 
However, if SP_A has been used actively, as part of a running system (i.e. website). it will be optimized by SQL server and will be placed in the procedural cache. This then results in Biased tests between SP_A and SP_B.
 
Therefore I would recommend using
DBCC FREEPROCCACHE
and
DBCC DROPCLEANBUFFERS
 
before timing the execution of SP_A against SP_B. Although, the affect of clearing the cache will cause them both to run slower, it will highlight performance gains of one against the other fairly.
 
 
 
Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

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: