What are the methods of index defragmentation?

What are the methods of index defragmentation?

Rating:

The fragmentation of indexes can cause slow processing of queries. In order to resolve the issue, indexes need to be defragmented. The methods to defragment the indexes are as follows:

• Drop and recreate indexes: Indexes are dropped and then recreated in order to resolve the issues caused by fragmentation. It removes fragmentation, reclaims disk space, and reorders the indexes in contiguous pages. Indexes are dropped and recreated by using the Object Explorer in the SQL Server Management Studio.


• CREATE INDEX...WITH DROP EXISTING: The DROP EXISTING argument of the CREATE INDEX statement is used to drop and rebuild an index. Its default value is off. When it is set to on, it drops and rebuilds the index that is specified in the index_name parameter of the CREATE INDEX statement.


• DBCC DBREINDEX: The DBCC DBREINDEX statement is used to drop and rebuild one or more indexes for a specified table in a database. It enforces the rebuilding of the PRIMARY key or the UNIQUE key constraint.


• DBCC INDEXDEFRAG: The DBCC INDEXDEFRAG statement defragments the indexes of a specified table or view. It defragments the leaf level of an index to match the physical order of the pages from left to right. This improves index-scanning performance.

Rating: