When data is inserted, updated, or deleted in a database table, if indexes exist that operate on the table, they must be maintained to reflect the table data changes. The maintenance of these indexes will eventually cause the indexes to become less efficient. This inefficiency is typically due to the index becoming fragmented.
There are two types of fragmentation:
external and internal. External fragmentation occurs when the logical ordering of the index does not match the physical ordering of the index. This causes SQL Server 2005 to perform extra work to return ordered results. For the most part, external fragmentation isn’t too big of a deal for specific searches that return very few records or queries that return result sets that do not need to be ordered.
Internal fragmentation occurs when there is too much free space in the index pages. Typically, some free space is desirable, especially when the index is created or rebuilt. You can specify the Fill Factor setting when the index is created or rebuilt to indicate a percentage of how full the index pages are when created. If the index pages are too fragmented, it will cause queries to take longer (because of the extra reads required to find data) and cause your indexes to grow larger than necessary. If no space is available in the index data pages, data changes (primarily inserts) will cause page splits, which also require additional system resources to perform.
There are two types of fragmentation:
external and internal. External fragmentation occurs when the logical ordering of the index does not match the physical ordering of the index. This causes SQL Server 2005 to perform extra work to return ordered results. For the most part, external fragmentation isn’t too big of a deal for specific searches that return very few records or queries that return result sets that do not need to be ordered.
Internal fragmentation occurs when there is too much free space in the index pages. Typically, some free space is desirable, especially when the index is created or rebuilt. You can specify the Fill Factor setting when the index is created or rebuilt to indicate a percentage of how full the index pages are when created. If the index pages are too fragmented, it will cause queries to take longer (because of the extra reads required to find data) and cause your indexes to grow larger than necessary. If no space is available in the index data pages, data changes (primarily inserts) will cause page splits, which also require additional system resources to perform.
give one example of this
ReplyDelete