What is index fragmentation?

Are you preparing for IT certification? With practice questions, study notes, interactive quizzes, tips and technical articles, uCertify PrepKits ensure that you get a solid grasp of core technical concepts to ace your certification exam in first attempt.

What is index fragmentation?

Rating:

Index fragmentation is a process in which index pages split due to heavy insert, update, and delete operations on a table in a database. It results in the inefficient use of pages and degrades the performance of queries. Fragmentation is of two types, which are as follows:

  • External fragmentation: It is caused when the leaf of index pages is not in the logical order. New index keys are inserted with the insertion of data that can result in the creation of new index pages. The insertion of new pages can disturb the original order of the keys. This results in extra processing of the queries, which cause them to run slow.

  • Internal fragmentation: Internal fragmentation occurs when index pages are not used to their maximum limit. It can lead to the increase in the size of the index pages, which causes additional read to be performed. It deteriorates the performance of queries.
In order to resolve the issue, the indexes need to be defragmented. The methods of defragmenting the indexes are as follows:
  • Drop and recreate the index

  • CREATE INDEX...WITH DROP_EXISTING

  • DBCC DBREINDEX

  • DBCC INDEXDEFRAG


Rating:



Other articles

Click here to Article home

 
uCertify.com | Our Company | Articles | Privacy | Security | Contact Us | News and Press Release | uCertify India
MCSE: MCSA, MCTS, MCITP    JAVA Certification: SCJP, SCWCD Cisco Certification: CCNA, CCENT, A+, Network+, Security+
Oracle Certification: OCP 9i, OCP 10g, OCA 9i, OCA 10g CIW foundation    EC-212-32    CISSP    Photoshop ACE    Adobe Flash ACE
© 2008 uCertify.com. All rights reserved. All trademarks are the property of their respective owners.