SQL Server – Does Index Rebuild Reduce Physical Reads from Disk?

clustered-indexdisk-structuresindex-tuningola-hallengrensql server

Recently I have rebuild indexes uisng Ola Hallengreen script which were 99% fragmented. After rebuilt i have noticed the physical reads have reduced a lot. Does this has anything to do with Index Rebuilt ?

Best Answer

Immediately after you rebuild an index, its pages are more likely to be cached in memory.

Consider this scenario:

  1. Your server has a wide variety of queries running, and a wide variety of tables are cached in memory
  2. You run a query hitting a specific table - say, COUNT(*) from table - and all of its pages were not cached in memory. Your query does a lot of physical reads to bring that data up into cache.
  3. You rebuild its index.
  4. You run the query again - but this time, its pages are now cached in memory. You see less physical reads on that query.

This is one of the reasons you don't really wanna use physical reads to gauge the success of your performance tuning efforts. Start by using logical reads instead. (Heck, you could have skipped step #3 altogether and you'd still see less physical reads - but that doesn't mean you should run every query twice in order to reduce physical reads.)

Related Question