Monday, July 17, 2023

SQL Server - How to reorganize indexes in SQL Server Database

 Reorganizing the indexes consists of two processes

1) Detect the fragmentation: By using the system function sys.dm_db_index_physical_stats, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases. For partitioned indexes, sys.dm_db_index_physical_stats also provides fragmentation information for each partition.

2) Remove Framentation: once you have a complete report on your database fragmentation, you can reorganize the indexes by following steps

1) In Object Explorer, Expand the database that contains the table on which you want to reorganize an index.
2) Expand the Tables folder.
3) Expand the table on which you want to reorganize an index.
4) Expand the Indexes folder.
5) Right-click the index you want to reorganize and select Reorganize.
6) In the Reorganize Indexes dialog box, verify that the correct index is in the Indexes to be reorganized grid and click OK.
7) Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted.

No comments:

Post a Comment