SQL Server CACHING & Performance Testing

  • Article

As you know SQL Server uses caches to speed up access to data and also query plans.

 

There are two caches one for Data and the other of Query Plans. When you are doing performance testing, with static data and you want to get accurate results of your test, it is recommended that you clean the two caches. The way to do this is by using the following statements in between each performance test run:

  • DBCC DROPCLEANBUFFER will clear only data but not procedure cache.
  • DBCC FREEPROCCACHE will clear the entire procedure cache.
  • Note: If you only want to clear one particular query you could use sp_recompile <storedproc>.

You can use SET STATISTICS IO ON and SET STATISTICS TIME ON to verify.

Happy performance testing :).