We help IT Professionals succeed at work.

Perplexed by Rebuild All

antonms asked
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?
Many thanks.
Ps. we are using SQL Server 2008 R2.
Watch Question

Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
That is interesting.  The only thing I can think of off the top of my head is that the stats got fully updated by the index build.

You may to make sure you do a stats update on the table with a FULL SCAN (that's likely not the exact syntax, but the point is, don't do just a sampling, make it update stats based on ALL rows in the table).