Rebuild/Reorganize Index & Update Statistics in SQL Server

Rebuild, reorganize and update statistics are maintenance operations in SQL Server that help improve the performance and accuracy of queries.

Rebuild Index: Rebuilding an index completely drops and recreates the index, which can help resolve fragmentation and other performance issues. Microsoft recommends that you rebuild indexes when fragmentation is above 30% and you need to reclaim disk space.

To rebuild an index on a table named Orders with an index named IX_Orders_OrderID, you can use the following T-SQL script.

                ALTER INDEX IX_Orders_OrderID ON Orders REBUILD;

Reorganize Index: Reorganizing an index only reorganizes the physical order of the index pages to reduce fragmentation and improve performance but does not completely rebuild the index. Microsoft recommends that you reorganize indexes when fragmentation is between 5% and 30%.

To reorganize an index on a table named Orders with an index named IX_Orders_OrderID, you can use the following T-SQL script.

                ALTER INDEX IX_Orders_OrderID ON Orders REORGANIZE;

Update Statistics: Updating statistics updates information about the distribution of data in a database, which is used by the query optimizer to determine the most efficient query plan. This operation helps ensure that the query optimizer makes accurate decisions about how to execute queries. Microsoft recommends that you update statistics regularly to ensure that the query optimizer has accurate information about the data.

To update statistics on a table named Orders, you can use the following T-SQL script.

                UPDATE STATISTICS Orders;

Note - It's important to note that the recommendations from Microsoft may vary depending on the specific requirements and workload of your database. It is recommended to regularly monitor the performance of your database and make adjustments to your maintenance strategy as needed.