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.
Categories: Uncategorized
Comments (0)
Trackbacks (0)
Leave a comment
Trackback
Network Programming in .NET 