Performance Tuning – Level Playing Field

When you’re performance tuning T-SQL one thing you want to make sure is that you are have a consistent environment to test your changes/modifications.  Two commands that will help ensure that your environment stays close to the same for each iteration of your script are DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE.

Now for all those people testing in production (first hang your head in shame ) you should use ALOT of caution with these two DBCC commands (or better yet just avoid them altogether in production).

DBCC DROPCLEANBUFFERS gives you a cold buffer cache while avoiding a full service shutdown/restart.  Good to do a CHECKPOINT before running this command so all the dirty pages for your database that you’re working with will be written to disk.  Using this command will help give you a level playing field and an accurate estimation of your codes execution time.  Not using this command might end up with a cached query and your changes (while great I’m sure) might seem to make a big difference without really doing so.

DBCC FREEPROCCACHE gives you the ability to clear all (or a specific) elements from the plan cache or remove all workload groups from a specified resource pool.  Once again be very careful where you use this as clearing your plan cache will cause stored procs to be recompiled which will give you a performance degredation. 

These two DBCC commands can help out alot when trying to troubleshoot/optimize T-SQL code.  Run them both before each execution of the code you are working with to help make sure that the positive effect that you’re seeing is due to your code and not cached information.