Performance testing sql script / stored proc in SQL 2000

• David Putman

  Mr or Mrs. 500

  Points: 571

  More actions

  March 15, 2006 at 9:22 am

  #171374

  Someone is sure to say that this is easy but it is beyond me....  I have a stored proc or script that I wish to performance test.  After running it for the first time and making a few improvements I normally down SQL Server and restart it - which is fine since I work on a standalone copy.  How do I get a level playing field if I cannot down SQL Server?    In other words how do I clear any caching that SQL Server is doing and test the script as if it has not been run before?

  Any ideas?

• colin.Leversuch-Roberts

  SSC Guru

  Points: 52643

  More actions

  March 15, 2006 at 9:29 am

  #626819

  there are a couple of dbcc commands that will clear the cache(s) but it does it for the entire server ..

  sp_recompile removes the proc plans from cache.

  dbcc freeproccache clears the cache

  dbcc dropcleanbuffers clears the data cache

  [font="Comic Sans MS"]The GrumpyOldDBA[/font]
  www.grumpyolddba.co.uk
  http://sqlblogcasts.com/blogs/grumpyolddba/

• David Putman

  Mr or Mrs. 500

  Points: 571

  More actions

  March 15, 2006 at 9:40 am

  #626825

  Colin

  Many thanks those two DBCC commands seem to give me reasonably consistent timings now for the same piece of code.

  Thanks for the prompt reply.

  David Putman