A significant portion of your time as a database programmer or administrator will be to improve the performance of queries. While there are several benchmarks you can compare two or more queries, the actual comparision of the two queries can lean you do make false assumptions. When you run a query, data and execution plans can be cached. This cache will actually work against you in trying to determine which option is best.
So how do you ensure a consistent test environment?
You mush clear out that cache. Simliar to keeping your browser free and clear of useless data, you must also clear out this cache so the second query doesn’t have an advanage over the first.
WARNING: Do not run this in your production environment. It will make your users irritable!
-- The following removes all plans from the cache DBCC FREEPROCCACHE -- The following Clears the data cache DBCC DROPCLEANBUFFERS
Run this separate from, but between the queries. What I mean is, Run the above code, and when it finishes run your first query, take notes on the performance of that query. Then, run the above code again. Then run the second query, again take notes of the performance. Now, you have a more equitable comparison between your queries.
Why do I say more equitable, rather than a truly equal comparison?
Well, there are many other variables at work. If your test environment is shared by you and other developers, someone else could be using the same database objects, at the same time as one of your tests. This could create its own information in cache. It could also cause locks, or other performance drains on your test. Either way, your results could be skewed. Try to obtain an exclusive lock on your database objects during testing. Secondly, if you are testing on a machine that does more than run SQL, such as testing on localhost, you may get interference from other processes on that machine running, like Burning a CD or running a disk defrag.
Using these two commands in SQL will help minimize one of the most common mistakes in comparing two queries and getting a better result the second time. As always, if you have any questions or comments, please send them in!