Cache - is there an off switch?

• rich sperrin

  SSC Enthusiast

  Points: 102

  More actions

  July 12, 2005 at 3:08 am

  #68157

  Background

  I'm working on a suite of reports that have been lifted from Access and also need optimising. Typically each report uses 10-12 sql statements to prepare the data in temp tables and process the results. To identify the slow bits I have added datediff calcs to report the speed of each statement so i can concentrate my efforts in the right place. When complete the suite of reports will be run once a day.

  The Problem

  When I run the sproc first time I get a good indication of which statement is slowing the proc down. But, any subsequent runs are affected by the server cache and it improves performance, significantly, in some cases. This skews any results when testing statements I have adjusted ( with the aid of the query plan ) and can provide misleading results.

  The Solution...

  ...would be, how do I switch off cacheing? OR

  am I looking in the wrong place for an answer?

• Gabor Nyul

  SSCrazy Eights

  Points: 9459

  More actions

  July 13, 2005 at 4:21 am

  #573808

  You cannot switch off the cache.

  But what you can do is you can clean up the cache between eauch consequent SP run by using:

  DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE

  
  
  Bye
  Gabor