asked
How update stats and rebuild/reorganise indexes work individually?
choosing right number of appropriate indexes and frequent updating of stats both of them lead to better performance, but how they work individually? 1. what leads to the increase in fragmentation level and what happens internally when we use the commands for reorganise or rebuilding the indexes? 2. what are the things that lead to update the stats for better performance? and what happens when we update stats internally?
These sound like interview questions, but I'll go ahead and pass along some info 1) Fragmentation is caused when updates or inserts would lead to too much data being stored on one page in an index. This causes the page to split, with half the data staying in the existing page and half going to a new page. Links to and from the new page are created, but the storage of the data is in a different physical location on the disk. That's fragmentation. Rebuilding an index causes it to be sorted and re-stored on disk, in a more contiguous fashion, reducing fragmentation. Reorgs are just a light-weight version of rebuilds with minimal disk movement. They're not as effective. 2)Stats are updated three ways. During an index rebuild, statistics get updated because the index is rebuilt, so the statistics are as well. After the threshold is reached when auto_update_stats is enabled, a scanned rebuild is done wherein the entire data set is not read, but a sampled sub-set of the data is with new statistics coming out the other side. When a command is issued to rebuild statistics usually from either sp_updatestats (scanned/sampled) or UPDATE STATISTICS (scanned or a complete rebuild, your call).
15 People are following this question.
