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 ?
asked Jul 9, 2018 at 19:09
Add a comment
|
3 Answers
Immediately after you rebuild an index, its pages are more likely to be cached in memory.
Consider this scenario:
- Your server has a wide variety of queries running, and a wide variety of tables are cached in memory
- 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.
- You rebuild its index.
- 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.)
answered Jul 10, 2018 at 12:00
The basic question boils down to "What, exactly, does an index rebuild do to an index." The simple answer is that it puts the index in sequential order. The more complex answer is the answer to your question, and comes in two parts:
- Nonclustered Index: In a nonclustered index, the rebuild has the effect of putting pages physically one after another in the order specified by the index. This can have the effect of consolidating fragmented pages into a smaller number of pages, allowing you to look at fewer pages for each seek. It also helps on range lookups, since an extent should have more pages of the same index in it, allowing a larger selection of rows to be retrieved from each index read. This will have a smaller effect than:
- Clustered Index: In a clustered index, the index is actually attached to the data row. If the clustered index is fragmented, not only are the index pages non-sequential on disk, but the data records are also non-sequential. Again, this will both tend to reduce the total number of pages in the index, but also will reorder both the index and the data rows into sequence, allowing for faster retrieval, especially on range selections.
-
a slight niggle; on SQL Server the clustered index is the table.– Hannah Vernon ♦Commented Jul 9, 2018 at 19:51
-
@MaxVernon - Although I've heard this before, I consider it imprecise (although my phrasing was also bad). Since the clustered index include non-leaf nodes, as well as leaf nodes with the data records, I think it is more precise to say that the clustered index contains the table. Commented Jul 9, 2018 at 22:15
-
I agree, technically the data is stored at the leaf level of the index. It probably is more accurate to think of the table being part-of the clustered index.– Hannah Vernon ♦Commented Jul 9, 2018 at 22:41
I'd also add another possibility (in additional to @Laughing Vergil's answer) why you see your I/O workload reduced.
After index rebuilt, the statistics on those indexes are also rebuilt, i.e. more accurate statistics are built, and as such, some queries that previously (i.e. before index rebuilt) use table scan to do the work, now may use index seek to get the result and so greatly reduced your I/O workload.
