Same procedures, different execution times

• b.ozga

  SSCommitted

  Points: 1891

  More actions

  June 26, 2008 at 2:39 am

  #129677

  Hi,

  I got procedure to optimize. So I had copied body, changed name and created new test proc in the same test database.

  I did some optimization with no effect, so I copied once again oiginal body and pasted to my test procedure, so both of them look exactly the same. To my amazement original procedure executed much faster than my test one.

  There is no recompile option in procedures, I did test several times, in the same environment. Before test I had defragment indexes, updated statistics on tables which are involved in testing. Should I change some create/alter procedure options ?

  Thanks in advance,

  Regards

  Bartek

• cy-dba

  SSCarpal Tunnel

  Points: 4179

  More actions

  July 9, 2008 at 7:35 am

  #840127

  Try running CHECKPOINT and DBCC DROPCLEANBUFFERS before each performance test. This cleans out the data cache. See for details.