Index fragmentation overview
What is index fragmentation and why I should focus on it:
Fragmentation exists when the logical ordering in the index (based on the key value in the index) does not match the physical order in the index pages in the index pages in the index in the pages that the index contains.
Whenever an insert, update, or delete operation is performed on the underlying data, the database engine automatically modifies the index. For example, adding rows to a table can cause split rows to store existing pages in the index to make room for inserting new keys. Over time, these modifications can cause information in the index to be scattered in the database (containing fragments). Fragmentation occurs when the logical ordering (based on key-value) in the pages that the index contains does not match the physical order in the data file.
A large number of fragmented indexes can slow down query performance because additional I/O is required to find the data that the index points to. More I/O can cause the application to be slow to respond, especially when scanning operations are involved.
Check for database index fragmentation
Command:
There are two ways to defragment: reorganize and recreate indexes, rebuilding indexes refers to deleting old indexes and rebuilding new indexes in a transaction, which reclaims the hard disk space of the original index and allocates new storage space to create an index structure. Reorganizing indexes refers to not allocating new storage space, reorganizing the leaf nodes of the index structure on the basis of the original space, so that the logical order and physical order of the data pages are consistent, and freeing up the excess space in the index.
Use the function sys.dm_db_index_physical_stats to detect the degree of fragmentation, field avg_fragmentation_in_percent the percentage of logical fragments returned, in general, Microsoft recommends a threshold of 30%:
avg_fragmentation_in_percent >5% and <=30%: ALTER INDEX REORGANIZE;
avg_fragmentation_in_percent >30%: ALTER INDEX REBUILD;
avg_fragmentation_in_percent: Index fragmentation percentage, if the fragmentation is less than 10%~20%, fragmentation is unlikely to be a problem,If the index fragmentation is at 20%~40%, fragmentation can be a problem, but index resolution can be eliminated by index reorganization, and large-scale fragmentation (when fragmentation is greater than 40%) may require index reconstruction.
Query the fragmentation information for all indexes of a given table
table reindexing
Rebuild the index of all tables in the entire database
Resources:The hyperlink login is visible.
|
Posted on 12/27/2020 10:32:27 PM
|
|
|
|
Posted on 9/22/2021 1:11:22 PM
|
Landlord