SQL Server Database maintains the indexes when the various operations are made such as insertion, deletion, or updation. These modifications can cause the information in index to become fragmented. Fragmentation only exists when the indexes have pages that have logical ordering that is based on key value and unable to match the physical ordering. It can be resolved by rebuilding or reorganizing an index. There are two commands that help to recognize data and index pages in SQL Server as discussed below:
DBBC INDEXDEFRAG
All pages, which have large data objects, are compacted. DBCC INDEXDEFRAG defragments the index so that the physical order of the pages matches the logical order. The leaf level of an index is defragmented that matches the left-right order of the leaf nodes. Reorganizing a definite clustered index compacts of all LOB columns that are in clustered index. While restructuring a non-clustered index compacts all LOB columns, which are not included columns in the index. DBCC INDEXDEFRAG method was used in earlier versions of Microsoft SQL Server. In newer versions of Microsoft SQL Server this has been removed. Instead of this we use ALTER INDEX.
ALTER INDEX
This states an index leaf level to be reorganized. This is similar to the statement DBCC INDEXDEFRAG. However, ALTER INDEX is always executed online which modifies data or view index. Data or index are modified by disabling, rebuilding or reorganizing index. This means that long-term blocking in the table locks is not held. Moreover, queries or updates in the underlying table can carry on throughout the ALTER INDEX transaction. REORGANIZE cannot be stated for inactivated index or an index with ALLOW_PAGE_LOCKS set to OFF.
avg_fragmentation_in_percent
The logical fragmentation for indexes as well as for extent fragmentation heaps in the IN_ROW_DATA allocation unit. Its value is restrained as percentage and takes into account various files.
Fragment_count
It includes the number of fragments that are there in the leaf level of IN_ROW_DATA allocation unit.
avg_fragment_size_in_pages
It represents the average number of pages, which are there in one fragment in leaf level of an IN_ROW_DATA allocation unit.
DBCC DBREINDEX
It rebuilds either an index for a table or all the indexes that are defined for table. After allowing index to rebuilt dynamically, both the primary key or unique constraints can be rebuilt or recreate without dropping the constraints. It clears that an index can be rebuilt without knowing table structure. It may occur after a bulk copy of data into table. It can rebuilt all the indexes for table in only one statement. It is quite simple and easy than coding various Drop and Create Index statements. As the work is only done by using only one statement, i.e. DBCC DBREINDEX where both the drop and create statements are included in a transaction. It also offers more optimization than an individual Drop and Create Index statement. It is mainly an offline operation to be performed easily. Users can utilize ALTER INDEX REBUILD statement for performing a degree control of parallelism at the time of index rebuild.
Recognize Data by Using SQL Server Management Studio
- Expand the database, which contains a table that is needed to be reorganized.
- After this, expand the tables’ folder then, increase table that requires restructuring of indexes.
- Click on the indexes folder >> choose Reorganize All option.
- Now, verify the accurate indexes from the reorganized indexes. Choose index and delete an index from indexes to be recognized grid.
- Choose the data of compact large object column data for specifying that all the pages have LOB data >> click OK.
Rebuild Defragment Index by Using T-SQL
- Connect with the instance of database engine.
- Choose New Query from standard bar.
- Now, copy and paste the mentioned query and execute.
ALTER INDEX Buisness_Test ON TEST REBUILD;
Conclusion
Management of data is quiet important of the work continuity. In the above discussion, we have discussed the same way to reorganize data and index pages SQL Server without losing data.
EmoticonEmoticon