Recently we developed a stored procedure whose execution time approximately triples, from 1.1secs to 3.3secs, after running around a dozen times or so. Essentially the procedure creates two temp tables; one having around 100k records the other 200k records. On investigation we found that Rebuilding the indexes on one particular table (NOT one of the temp tables) brought the execution time back down to 1.1 secs.
After a few runs the execution time went back up to 4secs. However on inspection of the same table, using SSMS Rebuild All, the indexes showed zero fragmentation, but we ran Rebuild All anyways just to find out if it made any difference– and guess what; the execution time went back down to 1.1 secs!
Can anyone explain why running Rebuild All made such a difference when no fragmentation was reported?
Ps. we are using SQL Server 2008 R2.