Hi Atif,

Thanks for the kind words! 🙂

Regarding your question. On the real system, I would expect the most frequently used data pages (mainly root and intermediate level index pages, and maybe the data of some smaller and more frequently used tables) to get into cache and remain there most of the time. Most data pages of the larger table will get into cache when used, but will usually be swapped out before their next use.

To simulate that as closely as possible, the test machine must also have a cache size that is smaller than the database size. If it's larger, the whole database will be in cache, and the amount of I/O will have much less impact on execution times.

Running DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE will force the cache empty once, but after that it will fill up again. And since the question involved a procedure with several steps, you should expect all but the first step to draw all data from cache only. And adding these DBCC commands in between all the steps in the procedure (that I did not add as an answer option, though I did consider it) would also not be a good simulation, since that would completely clean out the cache, whereas the real thing would keep the most frequently used (and the last accessed) pages in cache in between the queries.

I hope this removes your confusion, but please do tell me if you need more information!